如果没有在存储过程中创建，则创建DB链接

Question

我试图在Oracle中编写存储过程。我在这方面没有很多经验，我遇到了一个问题。该过程的最终目标是从一个DB中获取数据，并将其以不同的形式放入另一个DB中。我有大部分的程序工作，但我有一些问题，似乎应该是简单的。在代码的开头，我想检查以确保创建了DB链接。如果没有，那么我想创建db链接。

这是我在我的过程中写的:

IF (select count(1) from ALL_DB_LINKS where db_link = 'DB_LINK.NAME.COM') = 0 THEN
      CREATE DATABASE LINK LINK_NAME
      CONNECT TO username IDENTIFIED BY password
      USING 'SID';
END IF;

我知道这个链接是有效的，因为我已经在外面做了，并对它进行了查找。当我尝试编译时，我得到的错误是:

遇到符号"CREATE"时，期望下列之一:

我已经做了所有我能做的谷歌，我不知道我做错了什么。为了解决我的另一个问题，我还试着这样做:

声明test_count number;

select count(1) into test_count from ALL_DB_LINKS where db_link = 'DB_LINK.NAME.COM';
 BEGIN
  IF test_count = 0 THEN
      CREATE DATABASE LINK LINK_NAME
      CONNECT TO username IDENTIFIED BY password
      USING 'SID';
  END IF;
END;

但是我得到了相同的错误。我也不确定是否有一个开始里面开始将工作。任何帮助将是伟大的…嗯,有帮助。

Answer 1

Oracle编译包，为了做到这一点，代码中引用的所有对象都需要已经存在。这包括通过数据库链接引用的对象。如果引用的对象不存在，则会标记为无效。通常，DBA会使用PL/SQL脚本而不是存储过程来创建和维护这样的环境。

如果你真的想:

EXECUTE IMMEDIATE q'[ CREATE DATABASE LINK LINK_NAME
      CONNECT TO username IDENTIFIED BY password
      USING 'SID']';

Answer 2

如果您确实需要这样做，您可以使用Ed Gibbs建议和Brian演示的动态SQL创建链接，只要在创建过程时链接已经存在:

create database link test_link
connect to scott identified by oracle
using 'ORCL';
Database link created.
create or replace procedure p42 as
    l_count number;
    l_dummy dual.dummy%type;
begin
    select count(*) into l_count
    from all_db_links
    where db_link = 'TEST_LINK';
    if l_count = 0 then
        execute immediate q'[
            create database link test_link
            connect to scott identified by oracle
            using 'ORCL'
        ]';
    end if;
    select dummy into l_dummy from dual@test_link;
    dbms_output.put_line('Value from remote database: ' || l_dummy);
end;
/
Procedure created.
exec p42;
Value from remote database: X
PL/SQL procedure successfully completed.

但是如果链接不存在，程序将无法编译:

drop database link test_link;
create or replace procedure p42 as
...
end;
/
Warning: Procedure created with compilation errors.
show errors
Errors for PROCEDURE P42:
LINE/COL ERROR
-------- -----------------------------------------------------------------
16/5     PL/SQL: SQL Statement ignored
16/5     PL/SQL: ORA-00942: table or view does not exist

奇怪的是，它不反对在链接存在的情况下创建过程，然后删除链接;过程保持有效，动态SQL重新创建链接，并且过程成功运行:

drop database link test_link;
Database link dropped.
select object_type, object_name, status from all_objects
where object_type = 'PROCEDURE' and object_name = 'P42';
OBJECT_TYPE     OBJECT_NAME     STATUS
--------------- --------------- ---------------------
PROCEDURE       P42             VALID
exec p42;
Value from remote database: X
PL/SQL procedure successfully completed.

我希望删除链接使过程无效，但显然不是，尽管它出现在all_dependencies中。您不能重新编译这个过程:

drop database link test_link;
Database link dropped.
alter procedure p42 compile;
Warning: Procedure altered with compilation errors.
show errors
Errors for PROCEDURE P42:
LINE/COL ERROR
-------- -----------------------------------------------------------------
16/5     PL/SQL: SQL Statement ignored
16/5     PL/SQL: ORA-00942: table or view does not exist

为了让您在链接不存在时编译(或重新编译)，您还需要使用动态SQL引用链接:

drop database link test_link;
drop database link test_link
                   *
ERROR at line 1:
ORA-02024: database link not found
create or replace procedure p42 as
    ....
    execute immediate 'select dummy from dual@test_link' into l_dummy;
    dbms_output.put_line('Value from remote database: ' || l_dummy);
end;
/
Procedure created.
exec p42;
Value from remote database: X
PL/SQL procedure successfully completed.
drop database link test_link;
Database link dropped.
exec p42;
Value from remote database: X
PL/SQL procedure successfully completed.
drop database link test_link;
Database link dropped.
alter procedure p42 compile;
Procedure altered.
exec p42;
Value from remote database: X
PL/SQL procedure successfully completed.