Oracle PL/SQL。 DBMS_UTILITY.EXEC_DDL_STATEMENT 和 DBMS_ADVISOR.create_task

Question

我在远程数据库上创建 addm 任务时遇到问题。

BEGIN
DBMS_UTILITY.EXEC_DDL_STATEMENT@dblink(
  '
  begin
        DBMS_ADVISOR.create_task (
            advisor_name      => ''ADDM'',
            TASK_NAME         => ''15991_16109_AWR_SNAPSHOT_T1'',
            TASK_DESC         => ''Advisor for snapshots 15991 to 16109.'');
  end;
  '
  );
END;

在目标数据库上本地执行也不会产生结果。

BEGIN
DBMS_UTILITY.EXEC_DDL_STATEMENT(
  '
  begin
        DBMS_ADVISOR.create_task (
            advisor_name      => ''ADDM'',
            TASK_NAME         => ''15991_16109_AWR_SNAPSHOT_T1'',
            TASK_DESC         => ''Advisor for snapshots 15991 to 16109.'');
  end;
  '
  );
END;

但是在没有 DBMS_UTILITY.EXEC_DDL_STATEMENT 的情况下在目标数据库上本地执行并更正引号是有效的:

begin
        DBMS_ADVISOR.create_task (
            advisor_name      => 'ADDM',
            TASK_NAME         => '15991_16109_AWR_SNAPSHOT_T1',
            TASK_DESC         => 'Advisor for snapshots 15991 to 16109.');
end;

连接、数据库链接、用户授权等没有问题...DBMS_UTILITY.EXEC_DDL_STATEMENT 的问题。引号似乎是正确的，我使用 DBMS_OUTPUT.PUT_LINE 检查过。

有什么想法吗？谢谢。

Answer 1

DBMS_UTILITY.EXEC_DDL_STATEMENT 不执行匿名 block 。

下面的语句应该会引发错误，但不会:

begin
    dbms_utility.exec_ddl_statement@myself('
        declare 
            v_number number;
        begin
            v_number := 1/0;
        end;
    ');
end;
/

以下是通过数据库链接调用过程的正确方法:

begin
        DBMS_ADVISOR.create_task@myself(
            advisor_name      => 'ADDM',
            TASK_NAME         => '15991_16109_AWR_SNAPSHOT_T1',
            TASK_DESC         => 'Advisor for snapshots 15991 to 16109.');
end;
/

如果您需要运行多个步骤，并且需要诸如匿名 block 之类的内容，则需要创建一个临时过程，调用它，然后删除它。为了帮助您在进行大量嵌套时保持理智，请使用替代引用机制而不是双引号。

begin
    --You may want to use a sequence in the name to ensure uniqueness.
    dbms_utility.exec_ddl_statement@myself(q'<
        create or replace procedure temp_procedure is
        begin
            dbms_advisor.create_task(
                advisor_name      => 'ADDM',
                TASK_NAME         => '15991_16109_AWR_SNAPSHOT_T2',
                TASK_DESC         => 'Advisor for snapshots 15991 to 16109.');
        end;
    >');

    --Don't call this again or you may receive:
    --"ORA-04062: timestamp of procedure ... has been changed"
    execute immediate 'begin temp_procedure@myself; end;';

    dbms_utility.exec_ddl_statement@myself('drop procedure temp_procedure');
end;
/