Sorry for spamming this question. I made the original question below in Apex-forum Problem with dbms_comparison.converge which might be wrong place for it
Application Express 5.0.1.00.06
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
"CORE 12.1.0.1.0 Production"
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
Apex applications parsing schema is same as the schema-user for SqlDevloper.
I try to make Apex gui to manage dbms_comparison activities and keep failing on getting dbms_comparison.converge to work.
If the following
- is executed in SqlDeveloper worksheet or packetized to stored procedure then all steps including converge work
- is executed on Apex application page process as it is or as call to stored procedure, then only converge does nothing.
All potentially necessary before / after activities can be done with Apex guil by calling stored procedures which have the DDL statements in execute immediates when necessary.
- trigger disable/enables
- disables/enables (non-destructive) constraints
- resetting of sequences to match the remote master
For dbms_comparison.converge I can't figure out the proper syntax for execute immediate and I have my doubts if could be done properly??
But how to fix the script so that Apex App gui could execute also the dbms_comparison.converge properly?
Code to compare and converge if different, please don't mind about the dbms_outputs :
D
Creation of the compare:
DECLARE
v_scan_info dbms_comparison.comparison_type;
v_scan_out dbms_comparison.comparison_type;
v_compare_result boolean;
v_comparison_name varchar2(1000);
v_out varchar2(4000);
BEGIN
v_comparison_name := 'COMP_TEST_COMPARE_3';
v_compare_result := dbms_comparison.compare(
comparison_name => v_comparison_name
,scan_info => v_scan_info
,perform_row_dif => TRUE);
if v_compare_result = TRUE then
v_out := v_comparison_name||': Tables are synchronized!';
dbms_output.put_line(a => v_out);
else
v_out := v_comparison_name||': Warning! Data divergence found!'||chr(10)||'Scan id differences: '||v_scan_info.scan_id;
dbms_output.put_line(a => v_out);
dbms_comparison.converge(
comparison_name => v_comparison_name
,scan_id => v_scan_info.scan_id
,scan_info => v_scan_out
-- ,converge_options => dbms_comparison.cmp_converge_local_wins
,converge_options => dbms_comparison.cmp_converge_remote_wins
,perform_commit => true
);
v_out := '<------------->'||'converge scand ID is:'||v_scan_out.scan_id; dbms_output.put_line(a => v_out);
v_out := '<------------->'||v_comparison_name||': local rows updated:'||v_scan_out.loc_rows_merged; dbms_output.put_line(a => v_out);
v_out := '<------------->'||v_comparison_name||': local rows deleted:'||v_scan_out.loc_rows_deleted; dbms_output.put_line(a => v_out);
v_out := '<------------->'||v_comparison_name||': remote rows updated:'||v_scan_out.rmt_rows_merged; dbms_output.put_line(a => v_out);
v_out := '<------------->'||v_comparison_name||': remote rows deleted:'||v_scan_out.rmt_rows_deleted; dbms_output.put_line(a => v_out);
end if;
END;
FYI: I have logged the dbms_outputs to table and I can see that when using the Apex to launch the script the compares work but the converge doesn't because the converge doesn't change anything:
v_scan_out.loc_rows_merged == 0
"MYCOMPAREX: Warning! Data divergence found! Scan id differences: 11079"
<-------------> CONVERGING STARTS <----------------> MYCOMPAREX
<------------->converge scand ID is:11079
<------------->MYCOMPAREX: local rows updated:0
<------------->MYCOMPAREX: local rows deleted:0
<------------->MYCOMPAREX: remote rows updated:0
<------------->MYCOMPAREX: remote rows deleted:0
<-------------> CONVERGING ENDS <---------------->
MYCOMPAREX: COMPARISON RECHECK AFTER CONVERGING ---> OK FOR SCAN_ID : 11079