Oracle PL/SQL. DBMS_UTILITY.EXEC_DDL_STATEMENT and DBMS_ADVISOR.create_task

Question

I have a problem creating addm task on a remote database.

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;

Also executing locally on target database does not lead to the result.

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;

But executing locally on target database without DBMS_UTILITY.EXEC_DDL_STATEMENT and correcting quotes works:

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

There is no issues with connection, dblinks, user grants etc... The problem with DBMS_UTILITY.EXEC_DDL_STATEMENT. Quotes seems to be correct, i checked using DBMS_OUTPUT.PUT_LINE.

Any ideas? Thanks.

Answer 1

DBMS_UTILITY.EXEC_DDL_STATEMENT does not execute anonymous blocks.

The statement below should raise an error, but does not:

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

Here is the proper way to call a procedure over a datbase link:

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

If you need to run multiple steps, and need something like an anonymous block, you'll need to create a temporary procedure, call it, and then delete it. To help you keep your sanity when there is so much nesting going on, use the alternative quoting mechanism instead of doubling quotation marks.

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