APEX

Hello,

I apologize that this is going to be quite long even if I did my best to simplify things as much as possible. However I recommend to keep on reading as I'm going to reveal here absolutely mysterious things (APEX and DB version is probably not relevant, the behaviour is consistent in all versions).

I tried to synchronize two tables using DBMS_COMPARISON https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_comparison.htm#CHDHEFFJ  package (source code below). For simplicity I used the well known DEPT table and its replica with a few different rows.

Union all from both tables under the same scheme (TESTING) looks like this:

Now I ran the following code from SQL Developer (source code of the CMP_DEPT package below):

begin
  cmp_dept.prepare_dept_replica; --just creates a copy of the DEPT table named DEPT_REPLICA with the above data
  cmp_dept.g_logmode := cmp_dept.k_logmode_dbms_output; --dbms_output used for debug messages (in APEX is apex_debug.message used instead)
  cmp_dept.sync_tables( --run the table synchronization procedure (in APEX is in P1_CURRENT_SCHEMA filled by expression sys_context ( 'userenv', 'current_schema' )
    p_master_table     => 'DEPT',
    p_master_schema    =>:p1_current_schema,
    p_replica_table    => 'DEPT_REPLICA',
    p_replica_schema   =>:p1_current_schema
  );
end;

Expected output is as follows:

Comparing tables: Master TESTING.DEPT, Replica TESTING.DEPT_REPLICA
Created comparison CMP_20180122_145905
Comparison result:
Scan ID: 202
Differences found: Yes
Converge tables
After converge
Local rows deleted: 0
Remote rows deleted: 1
Local rows merged: 0
Remote rows merged: 2
Recheck tables
Differences found: No

Comparison created, program found differences and synchronized master data to the target table - 1 row deleted, 2 rows merged and finally invoked the RECHECK function just to make sure that data in both tables are identical.

Now let's invoke exactly the same block in SQL Workshop and the mystery begins. Same PL/SQL block but completely different output.

Comparing tables: Master TESTING.DEPT, Replica TESTING.DEPT_REPLICA
Created comparison CMP_20180122_090944
Comparison result:
Scan ID: 196
Differences found: Yes
Converge tables
After converge
Local rows deleted: 0
Remote rows deleted: 0
Local rows merged: 0
Remote rows merged: 0
Recheck tables
Differences found: No

Now some black magic must have happened under the hood, because the original scan found differences, then dbms_comparison.converge did nothing (deleted 0 remote rows and merged 0 remote rows), however the following rescan is quite surprisingly saying that both tables are identical (no differences found) while data in the DEPT_REPLICA remained untouched. Shocking!

Now let's try the same in APEX application (here is used apex_debug.message instead of dbms_output):

begin
  cmp_dept.prepare_dept_replica;
  cmp_dept.g_logmode := cmp_dept.k_logmode_apex_debug;
  cmp_dept.sync_tables(
    p_master_table     => 'DEPT',
    p_master_schema    =>:p1_current_schema,
    p_replica_table    => 'DEPT_REPLICA',
    p_replica_schema   =>:p1_current_schema
  );
end;

Output is as follows:

Comparing tables: Master TESTING.DEPT, Replica TESTING.DEPT_REPLICA
Created comparison CMP_20180122_145632
Comparison result:
Scan ID: 199
Differences found: Yes
Converge tables
After converge
Local rows deleted: 0
Remote rows deleted: 0
Local rows merged: 0
Remote rows merged: 0
Recheck tables
Differences found: No

Exactly the same output and result as from SQL Workshop.

Since I cannot attach here the application export, I created a showcase on apex.oracle.com but I cannot make it working because the CMP_DEPT package requires execute privileges on DBMS_COMPARISON.

It would be highly appreciated if anybody could shed some light into this problem, I've already pulled tons of my hair with no result.

Regards,

Pavel

--------------------------------------------------------

Sample APEX application on https://apex.oracle.com/pls/apex (must be exported with supporting objects)

ws: pp_testing

user: test

pwd: test

package code:

CREATE OR REPLACE package         cmp_dept 
as
------------------------------------------------------------------------------------------------------------------------------------------------------------------
--constants
k_logmode_dbms_output constant pls_integer := 1;
k_logmode_apex_debug constant pls_integer := 2;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
--global variables
g_logmode pls_integer range 1 .. 2 := k_logmode_dbms_output;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
--procedures
procedure prepare_dept_replica;
procedure sync_tables(p_master_table   varchar2,
                    p_master_schema  varchar2,
                    p_replica_table  varchar2,
                    p_replica_schema varchar2);
procedure log_message(p_msg varchar2);
procedure drop_user_comparisons;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
end cmp_dept;
/

CREATE OR REPLACE package body         cmp_dept 
as
------------------------------------------------------------------------------------------------------------------------------------------------------------------
function bool_to_yesno(p_bool boolean)
return varchar2
is
begin
  case p_bool
    when true then
        return 'Yes';
    when false then
        return 'No';
  end case;
end bool_to_yesno;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
procedure prepare_dept_replica
is
  table_exists exception;
  pragma exception_init (table_exists, -00955);
begin
  --create identical dept table replica
  begin
    execute immediate 'create table dept_replica as select * from dept';
    --primary key needed for comparison
    execute immediate 'alter table dept_replica modify (deptno not null )';
    execute immediate 'alter table dept_replica add constraint dept_replica_pk primary key ( deptno ) enable';
  exception
    when table_exists then --if the table exists...
        --dbms_output.put_line(sqlerrm());
        execute immediate 'truncate table dept_replica';
        execute immediate 'insert into dept_replica select * from dept';
  end;
  --we have identical tables now and we'll make few changes
  execute immediate 'delete from dept_replica where deptno = (select max(deptno) from dept)';--row should be later inserted
  execute immediate q'{update dept_replica set dname = dname || 'xx' where deptno = (select min(deptno) from dept)}';--this one should be merged
  execute immediate q'{insert into dept_replica(deptno,dname) values(99,'NEW_DEPT')}'; --this should be deleted
end prepare_dept_replica;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
procedure sync_tables(p_master_table   varchar2,
                    p_master_schema  varchar2,
                    p_replica_table  varchar2,
                    p_replica_schema varchar2)
as
  l_cmp_result               boolean;
  l_scan_info                dbms_comparison.comparison_type;
  k_comparison_name constant varchar2(20) := 'CMP_' || to_char(sysdate, 'yyyymmdd_hh24miss');
begin
  log_message('Comparing tables: Master ' || p_master_schema || '.' || p_master_table || ', Replica ' || p_replica_schema || '.' || p_replica_table);
  --create comparison
  dbms_comparison.create_comparison(comparison_name => k_comparison_name,
  schema_name => p_master_schema,
  object_name => p_master_table,
  dblink_name => null,
  remote_schema_name => p_replica_schema,
  remote_object_name => p_replica_table);
  log_message('Created comparison ' || k_comparison_name);
  --run comparison
  l_cmp_result := dbms_comparison.compare(comparison_name => k_comparison_name,
  scan_info => l_scan_info,
  perform_row_dif => true);
  log_message('Comparison result:');
  log_message('Scan ID: ' || l_scan_info.scan_id);
  log_message('Differences found: ' || bool_to_yesno(not l_cmp_result));
  if
    l_cmp_result = false --differences found
  then
    --converge
    log_message('Converge tables');
    dbms_comparison.converge(comparison_name => k_comparison_name,
    scan_id => l_scan_info.scan_id,
    scan_info => l_scan_info,
    converge_options => dbms_comparison.cmp_converge_local_wins, -- Default
    perform_commit => true);
    log_message('After converge');
    log_message('Local rows deleted: ' || l_scan_info.loc_rows_deleted);
    log_message('Remote rows deleted: ' || l_scan_info.rmt_rows_deleted);
    log_message('Local rows merged: ' || l_scan_info.loc_rows_merged);
    log_message('Remote rows merged: ' || l_scan_info.rmt_rows_merged);
    --now make sure tables are identical
    log_message('Recheck tables');
    l_cmp_result := dbms_comparison.recheck(
    comparison_name => k_comparison_name,
    scan_id => l_scan_info.scan_id,
    perform_row_dif => true);
    log_message('Differences found: ' || bool_to_yesno(not l_cmp_result));
  end if;
end sync_tables;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
procedure log_message(p_msg varchar2)
as
begin
  case g_logmode
    when k_logmode_dbms_output then
        dbms_output.put_line(p_msg);
    when k_logmode_apex_debug then
        apex_debug.message('DEBUGMSG: %s', p_msg); --add prefix to easily filter debug messages in apex debug trace
  end case;
end log_message;
------------------------------------------------------------------------------------------------------------------------------------------------------------------
procedure drop_user_comparisons
as
begin
  for r in (select comparison_name
      from user_comparison) loop
    --purge it first (probably unneeded)
    dbms_comparison.purge_comparison(r.comparison_name);
    dbms_comparison.drop_comparison(r.comparison_name);
  end loop;
end drop_user_comparisons;
end cmp_dept;