Oracle PL / SQL. DBMS_UTILITY.EXEC_DDL_STATEMENT和DBMS_ADVIS

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_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;

连接,dblinks,用户授权等没有问题……
DBMS_UTILITY.EXEC_DDL_STATEMENT的问题.行情似乎是正确的,我使用DBMS_OUTPUT.PUT_LINE检查.

有任何想法吗？谢谢.

解决方法

下面的陈述应该引发错误,但不会：

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_DESC         => 'Advisor for snapshots 15991 to 16109.');
end;
/

如果您需要运行多个步骤,并且需要类似匿名块的操作,则需要创建一个临时过程,调用它,然后将其删除.为了帮助您在存在如此多的嵌套时保持理智,请使用替代引用机制而不是加倍引号.

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;
/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!