Archive for the ‘Oracle’ Category
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; /
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
/* 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
/* 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
/* 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
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.
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:
- The following SQL command lets you create a pluggable database (PDB) with a
videouser 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\');
Don’t try to create the
DEFAULT TABLESPACE before you provision the database. If you do, you’ll get the following exception:
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
- You need to stop the Oracle listener and modify the
listener.orafile. Lines 9 through 12 configure a
VIDEODBOracle 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)) ) )
- You also need to add a
VIDEOTNS alias to the
tnsnames.orafile for the
VIDEODBpluggable 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) ) )
- You connect as the
VIDEOpluggable database with the following syntax (not presently an example in the SQL*Plus Guide). After connecting as the
VIDEODB, you’ll be prompted for a password. The required password is the
Video1password that you used when you set up the
sqlplus sys@VIDEO AS sysdba
- After authenticating as the
SYSDBA, you need to start the
VIDEODBpluggable database, like:
SQL> startup Pluggable DATABASE opened.
You can also start the pluggable database from the generic
SYSDBA account. You only need to change the
CONTAINER context to pluggable database. You can find the status of pluggable databases with the following query:
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;
The generic sample database returns this after restarting the database:
NAME OPEN_MODE RESTRICTED STATUS ------------------------------ ---------- ---------- -------- PDB$SEED READ ONLY NO NORMAL PDBORCL MOUNTED n/a NORMAL
The following changes the
CONTAINER context, opens the
pdborcl PDB, and creates a traditional Oracle 11g and backward standard user/schema account in the pluggable database.
SQL> ALTER SESSION SET container=pdborcl; SQL> ALTER PLUGGABLE DATABASE pdborcl OPEN; SQL> CREATE USER johnny IDENTIFIED BY johnny;
Re-query the status of the pluggable databases, and you’ll see:
NAME OPEN_MODE RESTRICTED STATUS ------------------------------ ---------- ---------- -------- PDB$SEED READ ONLY NO NORMAL PDBORCL READ WRITE NO NORMAL
If you have more than one pluggable database, you can use the following to open them all as the CDB’s
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
- 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
SYSDBAfor the PDB, like:
GRANT UNLIMITED TABLESPACE TO videoadm;
- After you’ve done all the prior steps, you can connect with the following as the Administrative
Although making such a connection is a poor choice, it would be better to make a secure connection like the one below. The problem with the former is that it exposes information at the OS command line that would be visible to other users. Connecting to the Oracle SQL*Plus with the
/NOLOG option prevents disclosure of that information.
C:\Users\mclaughlinm>sqlplus /nolog SQL*Plus: Release 184.108.40.206.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"
- Once you’re connected as the
SYSDBArole, 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;
Naturally, you can also add a
USER from the CDB’s
SYSDBA role when in the proper
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
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
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:
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:
Hope this helps those trying to sort it out.
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.