Oracle 12c | MacLochlainns Weblog

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:

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.

    /* 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.

Oracle Database 12c provides a limit syntax in SQL for a query with the following clause:

[OFFSET n ROWS] FETCH FIRST m ROWS ONLY

Unfortunately, it can’t be used dynamically like this in a stored function or procedure:

  CURSOR dynamic_cursor
  ( cv_offset  NUMBER
  , cv_rows    NUMBER ) IS
    SELECT   i.item_title
    FROM     item i
    OFFSET cv_offset ROWS FETCH FIRST cv_rows ROWS ONLY;

If you attempt it, you would raise the following error:

CREATE OR REPLACE FUNCTION dynamic_range
*
ERROR at line 1:
ORA-03113: end-of-file ON communication channel
Process ID: 4516
SESSION ID: 78 Serial NUMBER: 4467

The easy solution is to simply write it as a function returning an ADT (Attribute Data Type) collection like:

1
2
3

CREATE OR REPLACE
  TYPE item_title_table AS TABLE OF VARCHAR2(60);
/

The following dynamic_range function returns a collection with a dynamic range bound to the limiting clause:

CREATE OR REPLACE FUNCTION dynamic_range
( pv_offset  NUMBER
, pv_rows    NUMBER ) RETURN item_title_table IS
 
  /* Declare a collection type. */
  lv_item_title_table  ITEM_TITLE_TABLE := item_title_table();
 
  /* Local variable length string. */
  lv_item_title   VARCHAR2(60);
 
  /* Declare a local counter. */
  lv_counter  NUMBER := 1;
 
  /* Local NDS statement and cursor variables. */
  lv_stmt    VARCHAR2(2000);
  lv_cursor  SYS_REFCURSOR;
 
BEGIN
 
  /* Assigned a dynamic SQL statement to local variable. */
  lv_stmt := 'SELECT   i.item_title'||CHR(10)
          || 'FROM     item i'||CHR(10)
          || 'OFFSET :bv_offset ROWS FETCH FIRST :bv_rows ROWS ONLY';
 
  /* Open cursor for dynamic DNS statement. */
  OPEN lv_cursor FOR lv_stmt USING pv_offset, pv_rows;
  LOOP
    /* Fetch element from cursor and assign to local variable. */
    FETCH lv_cursor INTO lv_item_title;
 
    /* Exit when no more record found. */
    EXIT WHEN lv_cursor%NOTFOUND;
 
    /* Extend space, assign a value, and increment counter. */
    lv_item_title_table.EXTEND;
    lv_item_title_table(lv_counter) := lv_item_title;
    lv_counter := lv_counter + 1;
  END LOOP;
 
  /* Close cursor. */
  CLOSE lv_cursor;
 
  /* Return collection. */
  RETURN lv_item_title_table;
END;
/

By using, the following query:

1 2	SELECT COLUMN_VALUE AS item_title FROM TABLE(dynamic_range(2,5));

Hope this helps anybody who wants to make the limiting clause dynamic. You can find out how to embed it in PHP in Chapter 2 of the Oracle Database 12c PL/SQL Programming.

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:

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:

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:

/* 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.

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:

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

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.ora file. Lines 9 through 12 configure a VIDEODB Oracle SID. After you make the changes, start the Oracle listener.

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 VIDEO TNS alias to the tnsnames.ora file for the VIDEODB pluggable database (PDB).

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

After authenticating as the SYSDBA, you need to start the VIDEODB pluggable 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 SYSDBA:

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 SYSDBA for 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 VIDEO user:

sqlplus videoadm@VIDEO/Video1

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 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"

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;

Naturally, you can also add a USER from the CDB’s SYSDBA role when in the proper CONTAINER context.

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:

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:

  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.

A friend asked me how to get an Oracle view definition out of a LONG column and into a web application. I thought it was an interesting question because I ran into a similar problem when writing the Oracle Database 12c PL/SQL Programming book.

One of the new Oracle 12c features is the DBMS_UTILITY‘s new EXPAND_SQL_TEXT procedure. It lets you expand a view’s definition to include any views that the master view uses. It produces a single queries with all the base tables that support the view. Clearly, it’s an effective tool when it comes to understanding how those large ERP views work in the E-Business Suite.

LONG to CLOB Data Type

Here’s a version of the function that converts the LONG data type into a CLOB data type:

CREATE OR REPLACE FUNCTION long_to_clob
( pv_view_name      VARCHAR2
, pv_column_length  INTEGER )
RETURN CLOB AS
 
  /* Declare local variables. */
  lv_cursor    INTEGER := dbms_sql.open_cursor;
  lv_feedback  INTEGER;         -- Acknowledgement of dynamic execution
  lv_length    INTEGER;          -- Length of string
  lv_return    CLOB;            -- Function output
  lv_stmt      VARCHAR2(2000);  -- Dynamic SQL statement
  lv_string    VARCHAR2(32760); -- Maximum length of LONG data type
 
BEGIN
  /* Create dynamic statement. */
  lv_stmt := 'SELECT text'||CHR(10)
          || 'FROM user_views'||CHR(10)
          || 'WHERE view_name = '''||pv_view_name||'''';
 
  /* Parse and define a long column. */
  dbms_sql.parse(lv_cursor, lv_stmt, dbms_sql.native);
  dbms_sql.define_column_long(lv_cursor,1);
 
  /* Only attempt to process the return value when fetched. */
  IF dbms_sql.execute_and_fetch(lv_cursor) = 1 THEN
    dbms_sql.column_value_long(
        lv_cursor
      , 1
      , pv_column_length
      , 0
      , lv_string
      , lv_length);
  END IF;
 
  /* Check for an open cursor. */
  IF dbms_sql.is_open(lv_cursor) THEN
    dbms_sql.close_cursor(lv_cursor);
  END IF;
 
  /* Create a local temporary CLOB in memory:
     - It returns a constructed lv_return_result.
     - It disables a cached version.
     - It set the duration to 12 (the value of the dbms_lob.call
       package-level variable) when the default is 10. */
  dbms_lob.createtemporary(lv_return, FALSE, dbms_lob.call);
 
  /* Append the Long to the empty temporary CLOB. */
  dbms_lob.write(lv_return, pv_column_length, 1, lv_string);
 
  RETURN lv_return;
END long_to_clob;
/

This wraps the conversion of a LONG to CLOB, which is necessary to pre-size the LONG data type. Pre-sizing avoids reading the LONG column’s value character-by-character.

CREATE OR REPLACE FUNCTION expand_view 
( pv_view_name  VARCHAR2 ) RETURN CLOB IS
 
  /* Declare containers for views. */
  lv_input_view   CLOB;
  lv_output_view  CLOB;
 
  /* Declare a target variable,  because of the limit of SELECT-INTO. */
  lv_long_view  LONG;
 
  /* Declare a dynamic cursor. */
  CURSOR c (cv_view_name VARCHAR2) IS
    SELECT   text
    FROM     user_views
    WHERE    view_name = cv_view_name;
 
BEGIN
  /* Open, fetch, and close cursor to capture view text. */
  OPEN c(pv_view_name);
  FETCH c INTO lv_long_view;
  CLOSE c;
 
  /* Convert a LONG return type to a CLOB. */
  lv_input_view := long_to_clob(pv_view_name, LENGTH(lv_long_view));
 
  /* Send in the view text and receive the complete text. */
  dbms_utility.expand_sql_text(lv_input_view, lv_output_view);
 
  /* Return the output CLOB value. */
  RETURN lv_output_view;
END;
/

LONG to VARCHAR2 Data Type

Here’s a version of the function that converts the LONG data type into a VARCHAR2 data type:

CREATE OR REPLACE FUNCTION long_to_varchar2
( pv_view_name      VARCHAR2
, pv_column_length  INTEGER )
RETURN VARCHAR2 AS
 
  /* Declare local variables. */
  lv_cursor    INTEGER := dbms_sql.open_cursor;
  lv_feedback  INTEGER;          -- Acknowledgement of dynamic execution
  lv_length    INTEGER;          -- Length of string
  lv_return    VARCHAR2(32767);  -- Function output
  lv_stmt      VARCHAR2(2000);   -- Dynamic SQL statement
  lv_string    VARCHAR2(32760);  -- Maximum length of LONG data type
 
BEGIN
  /* Create dynamic statement. */
  lv_stmt := 'SELECT text'||CHR(10)
          || 'FROM user_views'||CHR(10)
          || 'WHERE view_name = '''||pv_view_name||'''';
 
  /* Parse and define a long column. */
  dbms_sql.parse(lv_cursor, lv_stmt, dbms_sql.native);
  dbms_sql.define_column_long(lv_cursor,1);
 
  /* Only attempt to process the return value when fetched. */
  IF dbms_sql.execute_and_fetch(lv_cursor) = 1 THEN
    dbms_sql.column_value_long(
        lv_cursor
      , 1
      , pv_column_length
      , 0
      , lv_string
      , lv_length);
  END IF;
 
  /* Check for an open cursor. */
  IF dbms_sql.is_open(lv_cursor) THEN
    dbms_sql.close_cursor(lv_cursor);
  END IF;
 
  /* Convert the long length string to a maximum size length. */
  lv_return := lv_string;
 
  RETURN lv_return;
END long_to_varchar2;
/

This wraps the conversion of a LONG to VARCHAR2, which is necessary to pre-size the LONG data type. Pre-sizing avoids reading the LONG column’s value character-by-character.

CREATE OR REPLACE FUNCTION return_view_text 
( pv_view_name  VARCHAR2 ) RETURN VARCHAR2 IS
 
  /* Declare a target variable,  because of the limit of SELECT-INTO. */
  lv_long_view  LONG;
 
  /* Declare a dynamic cursor. */
  CURSOR c (cv_view_name VARCHAR2) IS
    SELECT   text
    FROM     user_views
    WHERE    view_name = cv_view_name;
 
BEGIN
  /* Open, fetch, and close cursor to capture view text. */
  OPEN c(pv_view_name);
  FETCH c INTO lv_long_view;
  CLOSE c;
 
  /* Return the output CLOB value. */
  RETURN long_to_varchar2(pv_view_name, LENGTH(lv_long_view));
END;
/

Wrapper to DBMS_UTILITY‘s EXPAND_SQL_TEXT Procedure

As a response to somebody who simply wants a wrapper to the new dbms_utility‘s expand_sql_text procedure, I wrote the wrapper. Although, my reflection on this is why does a new procedure require a new wrapper to be useful? Did the use case get stated incorrectly. Anyway, here’s the wrapper:

-- Converts a long column to a CLOB data type.
CREATE OR REPLACE FUNCTION expand_sql_text
( pv_view_name      VARCHAR2 )
RETURN CLOB AS
 
  /* Declare containers for views. */
  lv_input_view   CLOB;
  lv_output_view  CLOB;
 
  /* Declare a target variable,  because of the limit of SELECT-INTO. */
  lv_long_view  LONG;
 
  /* Declare local variables for dynamic SQL. */
  lv_cursor    INTEGER := dbms_sql.open_cursor;
  lv_feedback  INTEGER;         -- Acknowledgement of dynamic execution
  lv_length    INTEGER;          -- Length of string
  lv_return    CLOB;            -- Function output
  lv_stmt      VARCHAR2(2000);  -- Dynamic SQL statement
  lv_string    VARCHAR2(32760); -- Maximum length of LONG data type
 
  /* Declare user-defined exception. */
  invalid_view_name  EXCEPTION;
  PRAGMA EXCEPTION_INIT(invalid_view_name, -20001);
 
  /* Declare a dynamic cursor. */
  CURSOR c (cv_view_name VARCHAR2) IS
    SELECT   text
    FROM     user_views
    WHERE    view_name = cv_view_name;
 
  FUNCTION verify_view_name
  ( pv_view_name      VARCHAR2 )
  RETURN BOOLEAN AS
    /* Default return value. */
    lv_return_result  BOOLEAN := FALSE;
 
    /* Declare cursor to check view name. */
    CURSOR c (cv_view_name  VARCHAR2) IS
      SELECT   NULL
      FROM     user_views
      WHERE    view_name = cv_view_name;
  BEGIN
    FOR i IN c (pv_view_name) LOOP
      lv_return_result := TRUE;
    END LOOP;
 
    RETURN lv_return_result;
  END verify_view_name;
BEGIN
  /* Throw exception when invalid view name. */
  IF NOT verify_view_name(pv_view_name) THEN
     RAISE invalid_view_name;
  END IF;
 
  /* Open, fetch, and close cursor to capture view text. */
  OPEN c(pv_view_name);
  FETCH c INTO lv_long_view;
  CLOSE c;
 
  /* Create dynamic statement. */
  lv_stmt := 'SELECT text'||CHR(10)
          || 'FROM user_views'||CHR(10)
          || 'WHERE view_name = '''||pv_view_name||'''';
 
  /* Parse and define a long column. */
  dbms_sql.parse(lv_cursor, lv_stmt, dbms_sql.native);
  dbms_sql.define_column_long(lv_cursor,1);
 
  /* Only attempt to process the return value when fetched. */
  IF dbms_sql.execute_and_fetch(lv_cursor) = 1 THEN
    dbms_sql.column_value_long(
        lv_cursor
      , 1
      , LENGTH(lv_long_view)
      , 0
      , lv_string
      , lv_length);
  END IF;
 
  /* Check for an open cursor. */
  IF dbms_sql.is_open(lv_cursor) THEN
    dbms_sql.close_cursor(lv_cursor);
  END IF;
 
  /* Create a local temporary CLOB in memory:
     - It returns a constructed lv_return_result.
     - It disables a cached version.
     - It set the duration to 12 (the value of the dbms_lob.call
       package-level variable) when the default is 10. */
  dbms_lob.createtemporary(lv_input_view, FALSE, dbms_lob.call);
 
  /* Append the Long to the empty temporary CLOB. */
  dbms_lob.write(lv_input_view, LENGTH(lv_long_view), 1, lv_string);
 
  /* Send in the view text and receive the complete text. */
  dbms_utility.expand_sql_text(lv_input_view, lv_output_view);
 
  /* Return the output CLOB value. */
  RETURN lv_output_view;
EXCEPTION
  WHEN invalid_view_name THEN
    RAISE_APPLICATION_ERROR(-20001,'Invalid View Name.');
  WHEN OTHERS THEN
    RETURN NULL;
END expand_sql_text;
/

-- Converts a long column to a CLOB data type. CREATE OR REPLACE FUNCTION expand_sql_text ( pv_view_name VARCHAR2 ) RETURN CLOB AS /* Declare containers for views. */ lv_input_view CLOB; lv_output_view CLOB; /* Declare a target variable, because of the limit of SELECT-INTO. */ lv_long_view LONG; /* Declare local variables for dynamic SQL. */ lv_cursor INTEGER := dbms_sql.open_cursor; lv_feedback INTEGER; -- Acknowledgement of dynamic execution lv_length INTEGER; -- Length of string lv_return CLOB; -- Function output lv_stmt VARCHAR2(2000); -- Dynamic SQL statement lv_string VARCHAR2(32760); -- Maximum length of LONG data type /* Declare user-defined exception. */ invalid_view_name EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_view_name, -20001); /* Declare a dynamic cursor. */ CURSOR c (cv_view_name VARCHAR2) IS SELECT text FROM user_views WHERE view_name = cv_view_name; FUNCTION verify_view_name ( pv_view_name VARCHAR2 ) RETURN BOOLEAN AS /* Default return value. */ lv_return_result BOOLEAN := FALSE; /* Declare cursor to check view name. */ CURSOR c (cv_view_name VARCHAR2) IS SELECT null FROM user_views WHERE view_name = cv_view_name; BEGIN FOR i IN c (pv_view_name) LOOP lv_return_result := TRUE; END LOOP; RETURN lv_return_result; END verify_view_name; BEGIN /* Throw exception when invalid view name. */ IF NOT verify_view_name(pv_view_name) THEN RAISE invalid_view_name; END IF; /* Open, fetch, and close cursor to capture view text. */ OPEN c(pv_view_name); FETCH c INTO lv_long_view; CLOSE c; /* Create dynamic statement. */ lv_stmt := 'SELECT text'||CHR(10) || 'FROM user_views'||CHR(10) || 'WHERE view_name = '''||pv_view_name||''''; /* Parse and define a long column. */ dbms_sql.parse(lv_cursor, lv_stmt, dbms_sql.native); dbms_sql.define_column_long(lv_cursor,1); /* Only attempt to process the return value when fetched. */ IF dbms_sql.execute_and_fetch(lv_cursor) = 1 THEN dbms_sql.column_value_long( lv_cursor , 1 , LENGTH(lv_long_view) , 0 , lv_string , lv_length); END IF; /* Check for an open cursor. */ IF dbms_sql.is_open(lv_cursor) THEN dbms_sql.close_cursor(lv_cursor); END IF; /* Create a local temporary CLOB in memory: - It returns a constructed lv_return_result. - It disables a cached version. - It set the duration to 12 (the value of the dbms_lob.call package-level variable) when the default is 10. */ dbms_lob.createtemporary(lv_input_view, FALSE, dbms_lob.call); /* Append the Long to the empty temporary CLOB. */ dbms_lob.write(lv_input_view, LENGTH(lv_long_view), 1, lv_string); /* Send in the view text and receive the complete text. */ dbms_utility.expand_sql_text(lv_input_view, lv_output_view); /* Return the output CLOB value. */ RETURN lv_output_view; EXCEPTION WHEN invalid_view_name THEN RAISE_APPLICATION_ERROR(-20001,'Invalid View Name.'); WHEN OTHERS THEN RETURN NULL; END expand_sql_text; /

As always, I hope this provides folks with a leg up on tricky syntax.

MacLochlainns Weblog

Archive for the ‘Oracle 12c’ Category

Cleaning up a Schema

Oracle 12c Offset & Rows

Excel PowerPivot & DAX

OUT Parameter Trick

WITH Clause Functions

OOW & JavaOne 2013 Over

Provision Oracle 12c PDB

Oracle 12c & PHP

DBMS_COMPARISON Missing?

Convert LONG to CLOB

Recent Posts

Things Written About

Pages

Blogroll

Archives