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

Question

I have a problem creating an addm task in 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 in the target database does not produce a 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 it is executed locally in the target database without DBMS_UTILITY.EXEC_DDL_STATEMENT and quotes correction:

 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 are no problems with connection, dblinks, user grants, etc ... The problem with DBMS_UTILITY.EXEC_DDL_STATEMENT. The quotes seem correct, I checked with DBMS_OUTPUT.PUT_LINE.

Any ideas? Thanks.

Answer 1

DBMS_UTILITY.EXEC_DDL_STATEMENT does not execute anonymous blocks.

An error should appear in the description below, but not:

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

Here is the correct way to call a procedure using 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 follow several steps and need something like an anonymous block, you need to create a temporary procedure, call it and delete it. To help you keep your sanity when so much nesting happens, use an alternative citation mechanism instead of double 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; /