Archive for the ‘sql’ Category
Cleaning up a Schema
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.
Oracle 12c Offset & Rows
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:
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 | 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.
OUT Parameter Trick
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.
WITH Clause Functions
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.
MySQL Synonym?
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.
Provision Oracle 12c PDB
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 aVIDEODBOracle 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 thetnsnames.orafile for theVIDEODBpluggable 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
SYSDBAfor theVIDEOpluggable database with the following syntax (not presently an example in the SQL*Plus Guide). After connecting as theSYSDBAfor theVIDEODB, you’ll be prompted for a password. The required password is theVideo1password that you used when you set up theVIDEODBdatabase.
sqlplus sys@VIDEO AS sysdba |
- After authenticating as the
SYSDBA, you need to start theVIDEODBpluggable 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 theSYSDBAfor 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
VIDEOuser:
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
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 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.
DBMS_COMPARISON Missing?
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.
Convert LONG to CLOB
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:
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 47 48 49 50 51 52 | 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.
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 | 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:
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 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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:
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 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 | -- 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.
SQL Like Comparisons
SQL tidbits are always valuable and highly searched for by newbies (as opposed to reading the SQL documentation). Sometimes we seasoned SQL developers take for granted little things like when a single- or multiple-character wildcard comparison works. It seems we know what newbies don’t. That you need a wildcard comparison operator not simply and equality comparison operator.
The question posed to me was, “Why doesn’t my wildcard comparison work?” Here’s a simplified example of their question.
SELECT 'Valid' AS "Test" FROM dual WHERE 'Treat' = 'Tre_t' OR 'Treet' = 'Tre_t'; |
Naturally, the answer is that the equality operator compares the strings based on their exact match (character sensitively in Oracle and character insensitively in MySQL). It needs to be rewritten by replacing the equals (=) comparison operator with the LIKE comparison operator. The following query does that:
SELECT 'Valid' AS "Test" FROM dual WHERE 'Treat' LIKE 'Tre_t' OR 'Treet' LIKE 'Tre_t' |
The same behavior exists for the multiple-character wildcard (%). I hope this helps those looking for this answer.
Finding DBMS_TYPES value?
Somebody asked me why they can’t query the DBMS_TYPES.TYPECODE_OBJECT value because they get an ORA-06553 error. Their query attempt is:
SELECT dbms_types.typecode_object FROM dual; |
Naturally, it raises the following exception:
SELECT dbms_types.typecode_object * ERROR at line 1: ORA-06553: PLS-221: 'TYPECODE_OBJECT' IS NOT a PROCEDURE OR IS undefined |
The explanation is very simple. It’s a package scoped variable and in Oracle 11g only accessible in a PL/SQL block. Here’s an anonymous block that would print the value to the console:
BEGIN dbms_output.put_line(dbms_types.typecode_object); END; / |
Hope that helps those trying to discover what a package variable’s value is.
Subscribe via RSS