Run a script as a sheduled job using sql developer scheduler GUI

Question

I have a script

Alter Trigger  TEST.LIVE_DELETE Disable;
Alter Trigger  TEST.LIVE_UPDATE Disable;

----------Running the comparison script----------------

var scanid number;
SET SERVEROUTPUT ON
DECLARE
consistent   BOOLEAN;
scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
consistent := DBMS_COMPARISON.COMPARE(
           comparison_name => 'COMPARE_TEST',
           scan_info       => scan_info,
           perform_row_dif => TRUE);
:scanid:=scan_info.scan_id;
DBMS_OUTPUT.PUT_LINE('Scan ID: '||scan_info.scan_id);
IF consistent=TRUE THEN
  DBMS_OUTPUT.PUT_LINE('No differences were found.');
ELSE
  DBMS_OUTPUT.PUT_LINE('Differences were found.');
END IF;
END;
/
COLUMN OWNER HEADING 'Comparison Owner' FORMAT A16
COLUMN COMPARISON_NAME HEADING 'Comparison Name' FORMAT A20
COLUMN SCHEMA_NAME HEADING 'Schema Name' FORMAT A11
COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A11
COLUMN CURRENT_DIF_COUNT HEADING 'Differences' FORMAT 9999999
SELECT c.OWNER,
c.COMPARISON_NAME,
c.SCHEMA_NAME,
c.OBJECT_NAME,
s.CURRENT_DIF_COUNT
FROM DBA_COMPARISON c, DBA_COMPARISON_SCAN_SUMMARY s
WHERE c.COMPARISON_NAME = s.COMPARISON_NAME AND
c.OWNER           = s.OWNER AND
s.SCAN_ID         = :scanid;
SET SERVEROUTPUT ON
DECLARE
scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
DBMS_COMPARISON.CONVERGE(
  comparison_name  => 'COMPARE_TEST',
  scan_id          => :scanid,
  scan_info        => scan_info,
  converge_options => DBMS_COMPARISON.CMP_CONVERGE_REMOTE_WINS);
DBMS_OUTPUT.PUT_LINE('Local Rows Merged: '||scan_info.loc_rows_merged);
DBMS_OUTPUT.PUT_LINE('Remote Rows Merged: '||scan_info.rmt_rows_merged);
DBMS_OUTPUT.PUT_LINE('Local Rows Deleted: '||scan_info.loc_rows_deleted);
DBMS_OUTPUT.PUT_LINE('Remote Rows Deleted: '||scan_info.rmt_rows_deleted);
END;
/

--------------End Of Comparison Script-----------------

----------------Enable the Triggers--------------------

Alter Trigger  TEST.LIVE_DELETE enable;
Alter Trigger  TEST.LIVE_UPDATE enable;

----------------END OF SCRIPT--------------------------

I am trying to set this script as a repeated job. Using SQL Developer>Scheduler>New Job (Wizard)

I get the below error, "ORA-06550: line 1, column 754: PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:

( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge " Is there a better way to do this or fix this!

Answer 1

1. You can not issue DDL commands (including ALTER) directly in PL/SQL. Use execute immediate instead.

execute immediate 'Alter Trigger TEST.LIVE_DELETE Disable';

2. Replace DBMS_OUTPUT.PUT_LINE calls with logging to a table, as the output of the former will not appear anywhere in case of a scheduled job.