Archive for the ‘pl/sql’ Category
Find Type Dependents
Somebody thought it was nice to show how to drop object type dependents in Oracle Database 11g, but they thought I should show how you discover dependent object types first. More or less, they were concerned how they discover type dependents when they raise the following error:
DROP TYPE item_object * ERROR at line 1: ORA-02303: cannot DROP OR REPLACE a TYPE WITH TYPE OR TABLE dependents |
They had a great point, so here I’m providing and object table function (object table functions are covered in Chapter 8 of Oracle Database 12c PL/SQL Programming book) that displays an object dependency tree.
Unlike table functions in Microsoft SQL Server or PostgreSQL, Oracle requires that you create the object type before you can return a result set table from a parameter-driven function. Here’s the object type for this object table function:
1 2 3 4 5 6 7 8 9 10 11 | -- Create an object type that mimics a record structure. CREATE OR REPLACE TYPE type_tree IS object ( level_id NUMBER , type_name VARCHAR2(30) , object VARCHAR2(10) , parent_type VARCHAR2(30)); / -- Create a UDT Collection. CREATE OR REPLACE TYPE type_tree_table IS TABLE OF type_tree; / |
Now, you can create the object table function that returns an object type dependent tree. This function uses branching recursion because you need to walk the dependency tree. The cursor against the three types of metadata in the database catalog lets us walk the tree. Each object type, and their dependents may have:
- A dependent object type
- A dependent collection, which uses the object type as a base object type.
- A dependent schema function, which uses the object type as a parameter data type or return data type.
- A dependent schema procedure, which uses the object type as a parameter data type.
- A dependent package, which contains a function or procedure using the object type as a parameter data type, or a function using the object type as a return data type.
Here’s the type_dependent function that walks the tree and displays the hierarchical results:
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 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 | CREATE OR REPLACE FUNCTION type_dependents ( pv_base_object_type VARCHAR2 , pv_level_id NUMBER DEFAULT 1 , pv_collection TYPE_TREE_TABLE DEFAULT NULL ) RETURN TYPE_TREE_TABLE IS /* Declare a return data type. */ lv_level_id NUMBER; /* Declare item type. */ lv_type_name VARCHAR2(30); lv_object_type VARCHAR2(30); lv_parent_type VARCHAR2(30); /* Declare a collection variable. */ lv_collection TYPE_TREE_TABLE := type_tree_table(); lv_incoming TYPE_TREE_TABLE := type_tree_table(); /* The first part of the cursor finds the dependent type names of complex object types, and the second part of the cursor finds the dependent collection types. Effectively the set operator finds two distinct branches because you may use any base type as an element of a complex object or of a collection. */ CURSOR base_type ( cv_level_id NUMBER , cv_base_type VARCHAR2 ) IS SELECT (cv_level_id) AS level_id , LPAD(' ', 2*(cv_level_id - 1)) || ut.type_name AS type_name , ut.typecode AS object_type , NULL AS parent_type FROM user_types ut WHERE ut.type_name = cv_base_type; CURSOR dependent_type ( cv_level_id NUMBER , cv_base_type VARCHAR2 ) IS SELECT cv_level_id AS level_id , LPAD(' ', 2*(cv_level_id - 1)) || uta.type_name AS type_name , ut.typecode AS object_type , ut.type_name AS parent_type FROM user_type_attrs uta INNER JOIN user_types ut ON uta.attr_type_name = ut.type_name WHERE ut.type_name = cv_base_type UNION ALL SELECT cv_level_id AS level_id , LPAD(' ', 2*(cv_level_id - 1)) || uct.type_name AS type_name , CASE WHEN uct.coll_type = 'TABLE' THEN uct.coll_type ELSE 'VARRAY' END AS object_type , ut.type_name AS parent_type FROM user_types ut INNER JOIN user_coll_types uct ON ut.type_name = uct.elem_type_name WHERE uct.elem_type_name = cv_base_type UNION ALL SELECT cv_level_id AS level_id , CASE WHEN package_name IS NULL THEN LPAD(' ', 2*(cv_level_id - 1)) || ua.object_name ELSE LPAD(' ', 2*(cv_level_id - 1)) || ua.package_name END AS type_name , CASE WHEN package_name IS NULL THEN uo.object_type ELSE 'PACKAGE' END AS object_type , ua.type_name AS parent_type FROM user_arguments ua LEFT JOIN user_objects uo ON ua.package_name = uo.object_name OR ua.object_name = uo.object_name WHERE type_name = cv_base_type ORDER BY object_type; BEGIN /* Set the call level indicator. */ lv_level_id := pv_level_id; /* Check if the collection holds values, which should occur in recursive calls. */ IF pv_collection IS NOT NULL THEN lv_collection := pv_collection; ELSE /* Open the base type and record level 1. */ OPEN base_type (lv_level_id, pv_base_object_type); FETCH base_type INTO lv_level_id , lv_type_name , lv_object_type , lv_parent_type; CLOSE base_type; /* Extend the collection. */ lv_collection.EXTEND; lv_collection(lv_collection.COUNT) := type_tree( lv_level_id , lv_type_name , lv_object_type , lv_parent_type ); /* Increment the type dependency level. */ lv_level_id := lv_level_id + 1; END IF; /* Loop through and return records. */ FOR i IN dependent_type(lv_level_id, pv_base_object_type) LOOP /* Extend the collection. */ lv_collection.EXTEND; lv_collection(lv_collection.COUNT) := type_tree( i.level_id , i.type_name , i.object_type , i.parent_type ); /* Recursively call down to dependent types. */ IF i.type_name <> i.parent_type THEN lv_collection := type_dependents(TRIM(i.type_name), (lv_level_id + 1), lv_collection); END IF; END LOOP; /* Return 0 for false. */ RETURN lv_collection; END; / |
You would query the function with the TABLE function, like this:
COLUMN level_id FORMAT 99999 HEADING "Level|ID #" COLUMN type_name FORMAT A30 HEADING "Type Name" COLUMN object FORMAT A10 HEADING "Object Type" COLUMN parent_type FORMAT A30 HEADING "Parent Type" SELECT * FROM TABLE(type_dependents('ITEM_OBJECT',1)); |
It displays the following results based on the sample types created by the original blog post:
Level
ID # Type Name Object Typ Parent Type
------ ------------------------------ ---------- ------------------------
1 ITEM_OBJECT OBJECT
2 GET_ITEM_OBJECT FUNCTION ITEM_OBJECT
2 IDENTIFIED_OBJECT OBJECT ITEM_OBJECT
2 ITEM_PACKAGE PACKAGE ITEM_OBJECT
2 ITEM_TABLE TABLE ITEM_OBJECT
3 ITEM_ASYNC_TABLE COLLECTION ITEM_TABLE
4 ITEM_LIST TABLE ITEM_ASYNC_TABLE
2 ITEM_VARRAY VARRAY ITEM_OBJECT
3 ITEM_ASYNC_VARRAY COLLECTION ITEM_VARRAY
4 ITEM_ARRAY VARRAY ITEM_ASYNC_VARRAY
10 rows selected. |
I hope this helps those working with Oracle Database 10g (de-supported), 11g, or 12c. As a note, you don’t need to drop type dependents in Oracle 12c because it supports type evolution. I cover type evolution in Appendix B of Oracle Database 12c PL/SQL Programming. As always, I hope this helps those trying to determine type dependents in an Oracle database.
Drop Types Recursively
As covered in my new Oracle Database 12c PL/SQL Programming book (publisher’s satisfied), you can evolve object types. That means you can change a base object type and the change cascades through dependents. Somebody asked how to remove an object type chain without appending the FORCE clause.
It’s quite easy if you understand writing a recursive function in PL/SQL, as done here:
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 107 | -- Create a recursive function. CREATE OR REPLACE FUNCTION drop_dependents (pv_base_object_type VARCHAR2) RETURN NUMBER IS /* Declare a return data type. */ lv_retval NUMBER := 0; /* Declare item type. */ lv_type_name VARCHAR2(30); lv_object_name VARCHAR2(30); /* The first part of the cursor finds the dependent type names of complex object types, and the second part of the cursor finds the dependent collection types. Effectively the set operator finds two distinct branches because you may use any base type as an element of a complex object or of a collection. */ CURSOR base_type (cv_base_type VARCHAR2) IS SELECT uta.type_name , NULL AS object_name FROM user_type_attrs uta INNER JOIN user_types ut ON uta.attr_type_name = ut.type_name WHERE ut.type_name = cv_base_type UNION ALL SELECT uct.type_name , NULL AS object_name FROM user_types ut INNER JOIN user_coll_types uct ON ut.type_name = uct.elem_type_name WHERE uct.elem_type_name = cv_base_type UNION ALL SELECT CASE WHEN package_name IS NULL THEN uo.object_type ELSE 'PACKAGE' END AS type_name , CASE WHEN package_name IS NULL THEN ua.object_name ELSE ua.package_name END AS object_name FROM user_arguments ua LEFT JOIN user_objects uo ON ua.package_name = uo.object_name OR ua.object_name = uo.object_name WHERE type_name = cv_base_type; BEGIN /* Open a parameterized cursor. */ OPEN base_type(pv_base_object_type); /* Loop through return records. */ LOOP /* Fetch records. */ FETCH base_type INTO lv_type_name , lv_object_name; /* Drop type without dependents, or drop leaf node dependent. */ IF base_type%NOTFOUND THEN /* Drop functions when they include an object type or object type dependent as a formal parameter type or return type. Drop procedures when they include an object type or object type dependent. Drop procedures when any function or procedure uses an object type or object type dependent. */ IF lv_type_name IN ('FUNCTION','PACKAGE','PROCEDURE') THEN /* Drop the base type when no dependents are found. */ EXECUTE IMMEDIATE 'DROP '||lv_type_name||' '||lv_object_name; ELSE /* Drop the base type when no dependents are found. */ EXECUTE IMMEDIATE 'DROP TYPE '||pv_base_object_type; END IF; /* Set exit state to one or true. */ lv_retval := 1; /* Exit the loop. */ EXIT; ELSE /* A type must exclude function, package, and procedure; and the object name must be null before you recurse to another level. */ IF lv_type_name NOT IN ('FUNCTION','PACKAGE','PROCEDURE') AND lv_object_name IS NOT NULL THEN /* Drop base type when no dependents are found. */ lv_retval := drop_dependents(lv_type_name); END IF; END IF; END LOOP; /* Close open cursor. */ CLOSE base_type; /* Return 0 for false. */ RETURN lv_retval; END; / |
Somebody asked me to provide a test case of a hierarchy of object types to support the drop_dependents function. So, here’s the test case code:
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 | -- Create object type. CREATE OR REPLACE TYPE item_object IS OBJECT ( item_name VARCHAR2(30) , item_subname VARCHAR2(30)); / -- Create object type. CREATE OR REPLACE TYPE identified_object IS OBJECT ( identified_id NUMBER , identified_object item_object); / -- Create object collection. CREATE OR REPLACE TYPE item_table IS TABLE OF item_object; / -- Create object collection. CREATE OR REPLACE TYPE item_varray IS VARRAY(5) OF item_object; / -- Create object type. CREATE OR REPLACE TYPE item_async_table IS OBJECT ( item_name VARCHAR2(30) , item_collection item_table); / -- Create object type. CREATE OR REPLACE TYPE item_async_varray IS OBJECT ( item_name VARCHAR2(30) , item_collection item_varray); / -- Create object collection. CREATE OR REPLACE TYPE item_list IS TABLE OF item_async_table; / -- Create object collection. CREATE OR REPLACE TYPE item_array IS VARRAY(10) OF item_async_varray; / -- Create package specification. CREATE OR REPLACE PACKAGE item_package AS /* A published function of the package. */ FUNCTION initialize_object ( id NUMBER , name NUMBER ) RETURN ITEM_OBJECT; END item_package; / -- Create a schema function. CREATE OR REPLACE FUNCTION get_item_object ( pv_id NUMBER , pv_name NUMBER ) RETURN ITEM_OBJECT IS /* Declare a local variable. */ lv_item_object ITEM_OBJECT; BEGIN /* Initialize the object type. */ lv_item_object := item_object(pv_id, pv_name); /* Return the dat type. */ RETURN lv_item_object; END; / |
If you call the function with the base type, it’ll drop the most dependent object type first, and the base object type last. The rest are dropped in their order of dependency. You can call a drop_dependents function with a base type, like ITEM_OBJECT, by using the following syntax:
1 2 3 4 5 6 7 | SET SERVEROUTPUT ON SIZE UNLIMITED BEGIN IF drop_dependents('ITEM_OBJECT') = 1 THEN dbms_output.put_line('Objects dropped.'); END IF; END; / |
Hope this helps those looking to drop a chain of object types in an Oracle database.
Oracle 12c PL/SQL Published
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
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.
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.
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.
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