MacLochlainns Weblog

After writing nine books, it’s always great when the author copies arrive. That’s when I know the process is complete. Friday, my twelve copies of the Oracle Database 12c PL/SQL Programming book arrived in two boxes of six each. The book is also available online at Amazon.com.

The book qualifies all the Oracle 12c new SQL and PL/SQL features. I added review sections and mastery questions to each chapter, and expanded examples and techniques. To conserve space and avoid reprinting duplicate code blocks, I adopted line numbers for the code segments so I could provide the technique variations by line numbers for alternate solutions.

You have complete examples on how to white list functions, procedures, packages, and object types with the new ACCESSIBLE BY clause. Likewise, you’ll learn how to use your PL/SQL variables inside embedded queries.

The improved collection coverage shows you how to write PL/SQL functions that let you use unnested UPDATE statements to add, change, and remove elements from Attribute Data Types (ADTs), and the expanded SQL Primer shows you how to update nested User-Defined Types (UDTs) without having to write PL/SQL. The book also shows you how to export object tables or columns into relational tables for ETL processes.

The Oracle Database Primer grew to include more database administration, multiversion concurrency control, SQL tuning, and SQL tracing. The SQL Primer now contains expanded coverage on data types, Data Definition Language (DDL), Data Manipulation Language (DML), Transaction Control Language (TCL), SQL queries, joins, and unnesting queries. The SQL Built-in Functions appendix was expanded to enable me to remove side discussions about SQL elements from the PL/SQL chapters. John Harper wrote some wonderful examples of DBMS_COMPARISON, DBMS_CRYPTO, and DBMS_FGA to supplement the PL/SQL Built-in Packages and Types appendix. The Regular Expression Primer was rewritten to make it easier to read and use.

I’ve created an Errata for the Oracle Database 12c PL/SQL Programming. If you buy a copy and find an error, please post a comment in the errata.

My students wanted a simple way to cleanup a development schema. So I wrote the following anonymous block PL/SQL program, which also manages the fact that Oracle Database 12c doesn’t completely drop system-generated sequences in active sessions.

The new identity columns in Oracle Database 12c create system-generated sequences, which you must purge from the recycle bin. If you don’t a generic script, like the following raises:

ORA-32794: cannot DROP a system-generated SEQUENCE

Here’s the script that cleans up an Oracle schema:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            ORDER BY object_type DESC) LOOP
 
    /* Drop types in descending order. */
    IF i.object_type = 'TYPE' THEN
 
      /* Drop type and force operation because dependencies may exist. Oracle 12c
         also fails to remove object types with dependents in pluggable databases
         (at least in release 12.1). Type evolution works in container database
         schemas. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' FORCE';
 
    /* Drop table tables in descending order. */
    ELSIF i.object_type = 'TABLE' THEN
 
      /* Drop table with cascading constraints to ensure foreign key constraints
         don't prevent the action. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS';
 
      /* Oracle 12c ONLY: Purge the recyclebin to dispose of system-generated
         sequence values because dropping the table doesn't automatically 
         remove them from the active session.
         CRITICAL: Remark out the following when working in Oracle Database 11g. */
      EXECUTE IMMEDIATE 'PURGE RECYCLEBIN';
 
    ELSIF i.object_type = 'LOB' OR i.object_type = 'INDEX' THEN
 
      /* A system generated LOB column or INDEX will cause a failure in a
         generic drop of a table because it is listed in the cursor but removed
         by the drop of its table. This NULL block ensures there is no attempt
         to drop an implicit LOB data type or index because the dropping the
         table takes care of it. */
      NULL;
 
    ELSE
 
      /* Drop any other objects, like sequences, functions, procedures, and packages. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
 
    END IF;
  END LOOP;
END;
/

As noted by Marat, you can simplify the drop of the tables by simply appending a PURGE clause to the DROP TABLE statement.

16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

    /* Drop table tables in descending order. */
    ELSIF i.object_type = 'TABLE' THEN
 
      /* Drop table with cascading constraints to ensure foreign key constraints
         but you need to purge system-generated constraints. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS PURGE';
 
    ELSE
 
      /* Drop any other objects, like sequences, functions, procedures, and packages. */
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
 
    END IF;
  END LOOP;
END;
/

Don’t run this version if you’ve provisioned an APEX Workspace in the Oracle Schema. I’ve got an updated version of the script for APEX 4.0. As always, I hope this helps a few people.

I’ve worked with every release of Microsoft Excel, and I know it takes effort to keep up to date with certain releases. Clearly, the Data Analysis eXpression (DAX) Language introduced in Excel 2010 went unnoticed by many, which was sad. DAX is truly a powerful extension to the analytical and modeling approaches in Microsoft Excel.

I’d like to recommend Microsoft Excel 2013 Building Data Models with PowerPivot to those who haven’t learned how to use DAX in Excel 2010, 2011, or 2013. DAX works with tables but if you don’t use tables, I guess you can skip DAX because you must have infinite time to produce marginal analytical outcomes (tongue in cheek humor). However, if you’re like most folks, you want a book to get you up-to-speed quickly, and that’s what this book will do for you.

Just one caveat if you’re using an Oracle or MySQL database, use the prepackaged analytic functions before you download the data set. You should always pre-select data before applying analytics in Excel. Remember the more refined the data model you start with the easier it is to structure analytical tools to leverage the data model. While DAX is powerful, it doesn’t replace the speed and query optimized behaviors of effective Oracle or MySQL queries.

Raja asked a question but unfortunately, I was buried in the final aspects of the write of the new Oracle Database 12c PL/SQL Programming book. He wanted to know how to pass an object type as an OUT-only mode parameter from a procedure.

That’s a great question, and it’s actually simple once you understand the difference between Oracle object types and other data types. Oracle object types must always be initiated before you use them, which means you must initialize any OUT-only mode parameters at the top of your execution section, like this:

1
2
3
4
5
6
7
8
9
10
11
12

CREATE OR REPLACE PROCEDURE reset_troll
( pv_troll OUT TROLL_OBJECT ) IS
  /* Troll default name. */
  lv_troll_name  VARCHAR2(20) := 'Bert';
BEGIN
  /* Initialize the incoming parameter by allocating memory to it. */
  pv_troll := troll_object();
 
  /* Set the name to something other than the 'Tom' default value. */
  pv_troll.set_troll(lv_troll_name);
END reset_troll;
/

Line 7 shows you the trick, initialize the incoming parameter because there isn’t an incoming parameter for an OUT-only mode parameter. The calling parameter to an OUT-only mode parameter is only a reference where PL/SQL will copy the internal object reference. While the calling parameter has been initialized, the reference to the call parameter’s object is where the internal object will be copied. The local program must first ensure a new memory location for a new instance of the object type before it can act on or return an object instance to the external reference. More or less, the internal object is copied to the calling object instance’s memory location when the procedure completes its execution.

Here’s the source code for the troll_object object type and body:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

CREATE OR REPLACE TYPE troll_object IS OBJECT
( troll VARCHAR2(20)
, CONSTRUCTOR FUNCTION troll_object
  RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION troll_object
  ( troll VARCHAR2 )
  RETURN SELF AS RESULT
, MEMBER FUNCTION get_troll RETURN VARCHAR2
, MEMBER PROCEDURE set_troll (troll VARCHAR2)
, MEMBER FUNCTION to_string RETURN VARCHAR2)
INSTANTIABLE NOT FINAL;
/
 
CREATE OR REPLACE TYPE BODY troll_object IS
  /* Default no-argument constructor. */
  CONSTRUCTOR FUNCTION troll_object RETURN SELF AS RESULT IS
    troll TROLL_OBJECT := troll_object('Tom');
  BEGIN
    SELF := troll;
    RETURN;
  END troll_object;
  /* Single argument constructor. */
  CONSTRUCTOR FUNCTION troll_object (troll VARCHAR2) RETURN SELF AS RESULT IS
  BEGIN
    SELF.troll := troll;
    RETURN;
  END troll_object;
  /* A getter function. */
  MEMBER FUNCTION get_troll RETURN VARCHAR2 IS
  BEGIN
    RETURN SELF.troll;
  END get_troll;
  /* A setter procedure. */
  MEMBER PROCEDURE set_troll (troll VARCHAR2) IS
  BEGIN
    SELF.troll := troll;
  END set_troll;
  /* A function that returns the formatted object type's contents. */
  MEMBER FUNCTION to_string RETURN VARCHAR2 IS
  BEGIN
    RETURN 'Hello '||SELF.troll;
  END to_string;
END;
/

You can test the reset_troll procedure with the following anonymous block:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

/* Enable printing from a PL/SQL block. */
SET SERVEROUTPUT ON SIZE UNLIMITED
/* Anonymous testing block. */
DECLARE
  lv_troll  TROLL_OBJECT := troll_object('Bill');
BEGIN
  dbms_output.put_line('--------------------');
  /* Prints 'Hello William' */
  dbms_output.put_line(lv_troll.to_string());
  dbms_output.put_line('--------------------');
  reset_troll(lv_troll);
  /* Prints 'Hello Bert' */
  dbms_output.put_line(lv_troll.to_string());
  dbms_output.put_line('--------------------');
END;
/

If you remark out line 7 from the reset_troll procedure, you’d raise the following exception by the call on line 10 because the local object hasn’t been instantiated (given life). It means there’s no memory location allocated for the instantiated (instance of an object type).

--------------------
Hello Bill
--------------------
DECLARE
*
ERROR at line 1:
ORA-30625: method dispatch ON NULL SELF argument IS disallowed
ORA-06512: at "VIDEO.RESET_TROLL", line 10
ORA-06512: at line 8

Hope this helps those trying to solve the same problem.

A neat feature of Oracle Database 12c is the ability to put PL/SQL functions inside SQL WITH statements. It’s covered in Chapter 2 on new SQL and PL/SQL features of the Oracle Database 12c PL/SQL Programming. There’s a trick though, you must disable the SQLTERMINATOR before creating the statement or accessing it, like:

SET SQLTERMINATOR OFF

Then, you can write a WITH statement like this:

WITH
FUNCTION glue
( pv_first_name VARCHAR2
, pv_last_name VARCHAR2) RETURN VARCHAR2 IS
  lv_full_name VARCHAR2(100);
BEGIN
  lv_full_name := pv_first_name || ' ' || pv_last_name;
  RETURN lv_full_name;
END;
SELECT glue(a.first_name,a.last_name) AS person
FROM actor a
/

Unfortunately, you need to include it in a view to make the WITH statement useful, like:

CREATE OR REPLACE VIEW actor_v AS
WITH
FUNCTION glue
( pv_first_name VARCHAR2
, pv_last_name VARCHAR2) RETURN VARCHAR2 IS
  lv_full_name VARCHAR2(100);
BEGIN
  lv_full_name := pv_first_name || ' ' || pv_last_name;
  RETURN lv_full_name;
END;
SELECT glue(a.first_name,a.last_name) AS person
FROM actor a
/

Hope this helps those trying to use the feature.

Somebody asked how to create a SYNONYM in MySQL, which is interesting because MySQL doesn’t support synonyms. I thought the prior entry explained how to do it, but here’s a new post. However, you can create a view in one database that relies on a table in another database.

The following SQL statements create two databases and grant appropriate privileges to the student as the root superuser:

/* Create two databases. */
CREATE DATABASE seussdb;
CREATE DATABASE appdb;
 
/* Grant privileges to a student user. */
GRANT ALL ON seussdb.* TO student;
GRANT ALL ON appdb.* TO student;

Log out from the root superuser and reconnect as the student user. Then, the following code connects to the seuss database and create a hat table; and inserts two rows into the hat table:

/* Use the appdb database. */
USE seussdb;
 
/* Create a hat table. */
CREATE TABLE hat
( hat_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, hat_text  VARCHAR(20));
 
/* Insert two rows into the hat table. */
INSERT INTO hat (hat_text) VALUES ('Thing 1');
INSERT INTO hat (hat_text) VALUES ('Thing 2');

The following code connects to the application database and creates a hat view; and then the code inserts one additional row into the hat table:

/* Connect to the application database. */
USE appdb;
 
/* Create a hat view. */
CREATE VIEW hat AS
  SELECT * FROM seussdb.hat;
 
/* Query the contents of the view, or seuss.hat table. */
SELECT * FROM hat;
 
/* Insert a new row into the hat table. */
INSERT INTO hat (hat_text) VALUES ('Thing 3');
 
/* Query the contents of the view, after insert to the view. */
SELECT * FROM hat;

The results will be the following:

+--------+----------+
| hat_id | hat_text |
+--------+----------+
|      1 | Thing 1  |
|      2 | Thing 2  |
|      3 | Thing 3  |
+--------+----------+

I hope this answers the question on how to mimic the Oracle database’s synonyms. The appdb.hat view acts as a synonym to the seuss.hat table.

Oracle OpenWorld 2013 and JavaOne 2013 are over and the America’s Cup a done deal. It was a full week. I didn’t have a moment to blog because I stay out in Livermore, which adds an hour plus to the beginning and ending of my day. It’s funny but I didn’t get a sense from some that they were excited about Oracle 12c. That’s probably because they don’t appear to know too much about it yet.

The Container Database (CDB) versus Pluggable Database (PDB) has enormous impact on how we can leverage Oracle in the private or public cloud. Admittedly, the ideas and concepts take some time to understand, implement, and appreciate.

Talking to folks, I got some great ideas for future blog posts. If you have any that I should add, let me know with a comment. I think the first one will show everyone how to migrate traditional surrogate key primary key columns to Oracle 12c‘s new Identity Columns.

Having seen some presentations referencing zetta-bytes, I wondered to myself (and now blog readers): “When will the Exadata server be replaced by a Zettadata server?”

Up there on my list for new blog posts are Oracle 12c‘s new Invisible Columns and new DBMS_REDACT package. For those interested, I’ll also present at UTOUG’s Fall Symposium on October 29th, 2013 on SQL and PL/SQL New Features in the Oracle Database 12c.

It was great to see familiar faces and sad to note those missing. Somehow the energy in Dell’s demo ground didn’t quite replace the old Quest Software demo ground’s pizazz.

Once home, I had to pivot back to task. I’m copy editing the Oracle Database 12c Programming PL/SQL Programming book, which will release in February 2014.

If you’re wondering about the photo of the Golden Gate Bridge, my son Joseph took it with his new iPhone 5s. I’m looking forward to my own iPhone upgrade in November. 😉

Somebody wants help provisioning a pluggable database (PDB). While it’s a new concept in the Oracle 12c database, I can sympathize with their plight. It wasn’t clear to me when first working with it, and I couldn’t seem to find a quick tutorial on the web. While it’s in the new Oracle 12c PL/SQL Programming Book, that won’t be out until January 2014.

If you’re new to Oracle 12c, which is most of us, then it’s important to understand that PDB is a private data context. Most of it’s data catalog is private and separate from the overall database. Only a small portion of the database catalog is stored in the container database catalog, and new CDB_ administrative views are added to the database. A PDB is a great solution when you’re leveraging the Editioning feature of Oracle 11g database.

You should note the guarantee from page #9 of Oracle’s Oracle Multitenant:

From the point of view of the client connecting via Oracle Net, the PDB is the database. A PDB is fully compatible with a non-CDB. We shall refer to this from now on as the PDB/non-CDB compatibility guarantee. In other words, the installation scheme for an application backend that ran without error against a non-CDB will run, with no change, and without error, in a PDB and will produce the same result.

Here are the steps that work on both Windows, Linux, or Windows:

1. The following SQL command lets you create a pluggable database (PDB) with a video user assigned to it:

1
2
3
4
5
6
7

CREATE PLUGGABLE DATABASE videodb
  ADMIN USER videoadm IDENTIFIED BY Video1
  ROLES = (dba)
  DEFAULT TABLESPACE videots
    DATAFILE 'C:\APP\ORACLE\ORADATA\ORCL\VIDEO01.DBF' SIZE 500M ONLINE
  FILE_NAME_CONVERT = ('C:\APP\ORACLE\ORADATA\ORCL\PDBSEED\',
                       'C:\APP\ORACLE\ORADATA\ORCL\VIDEOPDB\');

CREATE PLUGGABLE DATABASE videodb
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01537: cannot add file 'C:\APP\ORACLE\ORADATA\ORCL\VIDEO01.DBF' - file already part of database

2. You need to stop the Oracle listener and modify the listener.ora file. Lines 9 through 12 configure a VIDEODB Oracle SID. After you make the changes, start the Oracle listener.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\oracle\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC =
      (SID_NAME = VIDEODB)
      (ORACLE_HOME = C:\app\oracle\product\12.1.0\dbhome_1)
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

3. You also need to add a VIDEO TNS alias to the tnsnames.ora file for the VIDEODB pluggable database (PDB).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
 
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
 
VIDEO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = videodb)
    )
  )

4. You connect as the SYSDBA for the VIDEO pluggable database with the following syntax (not presently an example in the SQL*Plus Guide). After connecting as the SYSDBA for the VIDEODB, you’ll be prompted for a password. The required password is the Video1 password that you used when you set up the VIDEODB database.

sqlplus sys@VIDEO AS sysdba

5. After authenticating as the SYSDBA, you need to start the VIDEODB pluggable database, like:

SQL> startup
Pluggable DATABASE opened.

SQL> COLUMN RESTRICTED FORMAT A10
SQL> SELECT   v.name
  2  ,        v.open_mode
  3  ,        NVL(v.restricted, 'n/a') AS restricted
  4  ,        d.status
  5  FROM     v$PDBs v INNER JOIN dba_pdbs d USING(guid)
  6  ORDER BY v.create_scn;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- --------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBORCL                        MOUNTED    n/a        NORMAL

SQL> ALTER SESSION SET container=pdborcl;
SQL> ALTER PLUGGABLE DATABASE pdborcl OPEN;
SQL> CREATE USER johnny IDENTIFIED BY johnny;

NAME                           OPEN_MODE  RESTRICTED STATUS
------------------------------ ---------- ---------- --------
PDB$SEED                       READ ONLY  NO         NORMAL
PDBORCL                        READ WRITE NO         NORMAL

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

6. As Oracle said during the Oracle 11gR2 release, the DBA role no longer grants UNLIMITED TABLESPACE. That means you need to grant it as the SYSDBA for the PDB, like:

GRANT UNLIMITED TABLESPACE TO videoadm;

7. After you’ve done all the prior steps, you can connect with the following as the Administrative VIDEO user:

sqlplus videoadm@VIDEO/Video1

C:\Users\mclaughlinm>sqlplus /nolog
 
SQL*Plus: Release 12.1.0.1.0 Production ON Tue Aug 13 01:28:30 2013
 
Copyright (c) 1982, 2013, Oracle.  ALL rights reserved.
 
SQL> CONNECT videoadm@VIDEO/Video1
Connected.
SQL> SHOW USER
USER IS "VIDEOADM"

8. Once you’re connected as the SYSDBA role, you can create standard (pre-Oracle 12c) user/schemas with the old syntax. Below, we create a student account:

SQL> CREATE USER student IDENTIFIED BY student;
SQL> GRANT CREATE cluster, CREATE indextype, CREATE operator
  2  ,     CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION
  3  ,     CREATE TABLE, CREATE TRIGGER, CREATE TYPE
  4  ,     CREATE VIEW, UNLIMITED TABLESPACE TO student;

This has shown you how to provision a pluggable database (PDB). As a side note, you should know that you can only create user-defined common users (at the CDB-level) with a c## prefix.

An addendum: DBMS_COMPARE isn’t provisioned automatically, and you need to manually apply it in your ADMIN user for the PDB. I’ve blogged about it here.

This answers “How you connect PHP programs to an Oracle 12c multitenant database. This shows you how to connect your PHP programs to a user-defined Container Database (CDB) and Pluggable Database (PDB). It presupposes you know how to provision a PDB, and configure your Oracle listener.ora and tnsnames.ora files.

CDB Connection:

This assumes you already created a user-defined c##plsql CDB user, and granted an appropriate role or set of privileges to the user. Assuming the demonstration database Oracle TNS Service Name of orcl, you would test your connection with this script:

PDB Connection:

This assumes you already created a user-defined videodb PDB, and video user in the PDB, and granted an appropriate role or set of privileges to the video user. Assuming the user-defined videodb PDB uses an Oracle TNS Service Name of videodb, you would test your connection with this script:

Line 3 above uses the TNS Service Name from the tnsnames.ora file, which is also the SID Name from the listener.ora file after the slash that follows the localhost. That’s the only trick you should need.

You should note that because the tnsnames.ora file uses a video service name, the connection from the command line differs:

sqlplus video@video/video

Hope this helps those trying to sort it out.

The 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:

@?/rdbms/admin/dbmscmp.sql
@?/rdbms/admin/prvtcmp.plb

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.