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!
Best Answer
ALTER
) directly in PL/SQL. Useexecute immediate
instead.execute immediate 'Alter Trigger TEST.LIVE_DELETE Disable';
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.