dbms_comparison package isn’t deployed when you provision a pluggable databases (PDBs) in Oracle 12c. It appears to be a simple omission. At least, it let me manually compiled the
dbms_comparison package with this syntax:
However, when I ran the code against the PDB it failed. The same code worked against a container database (CDB). It struck me as odd. The error stack wasn’t too useful, as you can see below:
1 2 3 4 5 6 7 8 9 10
BEGIN * ERROR at line 1: ORA-06564: object "SYS"."COMPARE_NAME" does NOT exist ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 569 ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 602 ORA-06512: at "SYS.DBMS_CMP_INT", line 394 ORA-01403: no DATA found ORA-06512: at "SYS.DBMS_COMPARISON", line 764 ORA-06512: at line 2
My test was using two copies of a table with differences between column values. Both were deployed in the same CDB or PDB. That meant it was either a missing table or a problem with my database link. Here’s the statement that caused the failure:
7 8 9 10 11 12
dbms_comparison.create_comparison(comparison_name => 'COMPARE_NAME' , schema_name => 'video' , object_name => 'MEMBER#1' , dblink_name => 'loopbackpdb' , remote_schema_name => 'video' , remote_object_name => 'MEMBER#2');
Unfortunately, there wasn’t any notable difference between the two database links. Playing around with it, I discovered the problem. While you don’t have to enclose your case sensitive password in double quotes for a CDB database link, you do need to enclose the password with double quotes in a PDB database link.
This database link fixed the problem:
1 2 3
CREATE DATABASE LINK loopbackpdb CONNECT TO video IDENTIFIED BY "Video1" USING 'video';
The delimiting double quotes on line 2 fixed the problem. Hopefully, this helps somebody who runs into it too. Any way, according to this June 2013 Oracle White Paper it would appear as a bug because it’s an inconsistent behavior between a CDB and PDB.