Binding PLSQL Types in SQL (12.1 and Higher)

View All Scripts Login and Run Script

Script Name Binding PLSQL Types in SQL (12.1 and Higher)
Description In 12.1, Oracle Database extended its support for PL/SQL-specific datatypes in SQL statements. Before Oracle Database 12c, values with PL/SQL-only data types (for example, BOOLEAN, associative array, and record) could not be bound from client programs (OCI or JDBC) or from static and native dynamic SQL issued from PL/SQL in the server. As of Oracle Database 12c, it is possible to bind values with PL/SQL-only data types to anonymous blocks (which are SQL statements), PL/SQL function calls in SQL queries and CALL statements, and the TABLE operator in SQL queries. http://docs.oracle.com/database/121/LNPLS/release_changes.htm#LNPLS111
Category PL/SQL General
Contributor Steven Feuerstein (Oracle)
Created Tuesday November 24, 2015

Statement 1
If you want to make sure you get a clean compile (no errors, but also no flags from the PL/SQL compiler letting you know your code is sub-optimal in some way), turn on compile time warnings. All the time!
Turn on Compile-Time Warnings
```
ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL' 
```
Statement processed.
Statement 2
Notice the only difference is the name of the parameter. As you will see, this is a poorly designed overloading. You need more of a difference than that.
A Package with Overloading
```
CREATE OR replace PACKAGE salespkg AUTHID DEFINER  
IS  
   PROCEDURE calc_total (zone_in IN VARCHAR2);  
  
   PROCEDURE calc_total (reg_in IN VARCHAR2);  
  
END salespkg;  
```
Package created.

Statement 3

CREATE OR replace PACKAGE BODY salespkg 
IS 
   PROCEDURE calc_total (zone_in IN VARCHAR2) 
   IS 
   BEGIN DBMS_OUTPUT.PUT_LINE ('zone'); END; 
 
   PROCEDURE calc_total (reg_in IN VARCHAR2) 
   IS 
   BEGIN DBMS_OUTPUT.PUT_LINE ('region'); END; 
 
END salespkg;

Package Body created.

Statement 4
Nah. How can the compiler sort out which of the two to call? WE can look at it and say "Check out these string. CLEARLY, that is a zone!" But ahem we could be wrong and anyway the compiler doesn't examine the contents of literals and try to interpret our intent.
Will This Work?
```
BEGIN  
   salespkg.calc_total ('zone15');  
END; 
```
ORA-06550: line 2, column 4: PLS-00307: too many declarations of 'CALC_TOTAL' match this call
Statement 5
Named notation - explicitly associating the formal parameter with its actual value - is a sweet feature of PL/SQL, but it should not be NECESSARY to get your code to compile. Unfortunately, that's what it takes with this poorly designed package.
Get It To Work With Named Notation
```
BEGIN  
   salespkg.calc_total (zone_in => 'zone15');  
END; 
```
zone
Statement 6
This time we have the same parameter name, but different datatypes: CHAR vs VARCHAR2. Interestingly enough, the compiler says: "Sure, no problem."
Create New Version of Package
```
CREATE OR replace PACKAGE salespkg AUTHID DEFINER  
IS  
   PROCEDURE calc_total (reg_in IN CHAR);  
  
   PROCEDURE calc_total (reg_in IN VARCHAR2);  
  
END salespkg;  
```
Package created.

Statement 7

Ah, but how can you invoke either of these subprograms?

The Package Body Compiles Too

CREATE OR replace PACKAGE BODY salespkg  
IS  
   PROCEDURE calc_total (reg_in IN CHAR)  
   IS  
   BEGIN DBMS_OUTPUT.PUT_LINE ('region'); END;  
  
   PROCEDURE calc_total (reg_in IN VARCHAR2)  
   IS  
   BEGIN DBMS_OUTPUT.PUT_LINE ('region'); END;  
  
END salespkg;

Package Body created.

Statement 8
No good. How can the compiler tell if this is a fixed-length string or a variable-length string? C
Just Pass a String?
```
BEGIN  
   salespkg.calc_total ('reg11');  
END; 
```
ORA-06550: line 2, column 4: PLS-00307: too many declarations of 'CALC_TOTAL' match this call
Statement 9
No longer a viable option - both overloadings use the same parameter name.
Use Named Notation?
```
BEGIN  
   salespkg.calc_total (reg_in => 'reg11');  
END; 
```
ORA-06550: line 2, column 4: PLS-00307: too many declarations of 'CALC_TOTAL' match this call
Statement 10
Down to desperate measures. Declare a CHAR variable, assign value to it, pass it to subprogram. That should work, right? NO! The complier still refuses to recognize only one overloading as a match. Bottom line: your parameter datatypes need to be in different FAMILIES (number vs string, date vs Boolean, etc.) for overloading to work, for the most part.
Pass a Fixed Length String Explicitly?
```
DECLARE  
   l_char   CHAR (2) := 'ab';  
BEGIN  
   salespkg.calc_total (reg_in => l_char);  
END; 
```
ORA-06550: line 4, column 4: PLS-00307: too many declarations of 'CALC_TOTAL' match this call

Statement 11

At first glance, these two overloadings are very different CHAR-DATE vs VARCHAR2. But that date has a default value!

Ambiguous Overloading Due to Default Values

CREATE OR replace PACKAGE salespkg AUTHID DEFINER  
IS  
   PROCEDURE calc_total (zone_in IN CHAR, date_in in DATE DEFAULT SYSDATE);  
  
   PROCEDURE calc_total (zone_in IN VARCHAR2);  
  
END salespkg;

Package created.

Statement 12

CREATE OR replace PACKAGE BODY salespkg 
IS 
   PROCEDURE calc_total (zone_in IN CHAR, date_in in DATE DEFAULT SYSDATE) 
   IS 
   BEGIN DBMS_OUTPUT.PUT_LINE ('zone with date'); END; 
 
   PROCEDURE calc_total (zone_in IN VARCHAR2) 
   IS 
   BEGIN DBMS_OUTPUT.PUT_LINE ('zone'); END; 
 
END salespkg;

Package Body created.

Statement 13
Since date_in has a default value, I can invoke that subprogram with only one actual value - which then brings us right back to a problem: the PL/SQL compiler doesn't know which of the two to invoke.
Oh No! Still Ambiguous....
```
BEGIN  
   salespkg.calc_total ('reg11');  
END; 
```
ORA-06550: line 2, column 4: PLS-00307: too many declarations of 'CALC_TOTAL' match this call
Statement 14
Only one overloading has two arguments, so this executes just fine.
Ah, This One Works
```
BEGIN  
   salespkg.calc_total ('reg11', sysdate);  
END; 
```
zone with date

Statement 15

Root type is food, all desserts are a type of food, all cakes are a type of dessert. But not all desserts are cakes. Not all food is dessert. Too bad! :-)

Create the Object Type Hierarchy

CREATE TYPE food_t AS OBJECT  
       (name VARCHAR2 (100)  
      , food_group VARCHAR2 (100)  
      , grown_in VARCHAR2 (100)  
      , MEMBER FUNCTION to_string  
           RETURN VARCHAR2)  
          NOT FINAL;

Type created.

Statement 16

CREATE OR REPLACE TYPE BODY food_t 
IS 
   MEMBER FUNCTION to_string 
      RETURN VARCHAR2 
   IS 
   BEGIN 
      RETURN    'FOOD! ' 
             || self.name 
             || '-' 
             || self.food_group 
             || '-' 
             || self.grown_in; 
   END; 
END;

Type created.

Statement 17

Tweak the string representation of a dessert to specialize it, compared to general dessert strings.

Override to_string Method

CREATE TYPE dessert_t  
          UNDER food_t  
       (contains_chocolate CHAR (1)  
      , OVERRIDING MEMBER FUNCTION to_string  
           RETURN VARCHAR2)  
          NOT FINAL;

Type created.

Statement 18

CREATE OR REPLACE TYPE BODY dessert_t 
IS 
   OVERRIDING MEMBER FUNCTION to_string 
      RETURN VARCHAR2 
   IS 
   BEGIN 
      /* Display the dessert information + food info. */ 
 
      RETURN 'DESSERT! Chocolate='  
          || contains_chocolate  
          || ' '  
          || (SELF as food_t).to_string   ; 
   END; 
END;

Type created.

Statement 19

Further specialize the way a cake is represented as a string

Second Override of to_string Method

CREATE TYPE cake_t  
          UNDER dessert_t  
       (diameter NUMBER  
      , OVERRIDING MEMBER FUNCTION to_string  
           RETURN VARCHAR2);

Type created.

Statement 20

CREATE OR REPLACE TYPE BODY cake_t 
IS 
   OVERRIDING MEMBER FUNCTION to_string 
      RETURN VARCHAR2 
   IS 
   BEGIN 
      /* Call two supertype methods... */ 
 
      RETURN    'CAKE! Diameter=' 
             || self.diameter 
             || ' ' 
             || (SELF as dessert_t).to_string; 
   END; 
END;

Type created.

Statement 21

At runtime, the engine determines which method in the hierarchy should be invoked. That's pretty cool. But what if you want to override that and pick a specific supertype method?

Dynamic Polymorphism At Work

DECLARE  
   TYPE foodstuffs_nt IS TABLE OF food_t;  
  
   fridge_contents   foodstuffs_nt  
      := foodstuffs_nt (food_t ('Eggs benedict', 'PROTEIN', 'Farm')  
                      , dessert_t ('Strawberries and cream'  
                                 , 'FRUIT'  
                                 , 'Backyard'  
                                 , 'N')  
                      , cake_t ('Chocolate Supreme'  
                              , 'CARBOHYDATE'  
                              , 'Kitchen'  
                              , 'Y'  
                              , 8));  
BEGIN  
   FOR indx IN fridge_contents.FIRST .. fridge_contents.LAST  
   LOOP  
      DBMS_OUTPUT.put_line (fridge_contents (indx).to_string);  
   END LOOP;  
END;

FOOD! Eggs benedict-PROTEIN-Farm
DESSERT! Chocolate=N FOOD! Strawberries and cream-FRUIT-Backyard
CAKE! Diameter=8 DESSERT! Chocolate=Y FOOD! Chocolate Supreme-CARBOHYDATE-Kitchen

Statement 22

I don't want to invoke cake's to_string. I don't even want to invoke dessert's to_string. I want to go all the way up to food: (SELF as food_t).to_string

Change Cake's to_string to "Skip" Over Dessert

CREATE OR REPLACE TYPE BODY cake_t  
IS  
   OVERRIDING MEMBER FUNCTION to_string  
      RETURN VARCHAR2  
   IS  
   BEGIN  
      /* Call two supertype methods... */  
  
      RETURN    'CAKE! Diameter='  
             || self.diameter  
             || ' '  
             || (SELF as food_t).to_string;  
   END;  
END;

Type created.

Statement 23

Cake Display Now Different!

DECLARE  
   TYPE foodstuffs_nt IS TABLE OF food_t;  
  
   fridge_contents   foodstuffs_nt  
      := foodstuffs_nt (food_t ('Eggs benedict', 'PROTEIN', 'Farm')  
                      , dessert_t ('Strawberries and cream'  
                                 , 'FRUIT'  
                                 , 'Backyard'  
                                 , 'N')  
                      , cake_t ('Chocolate Supreme'  
                              , 'CARBOHYDATE'  
                              , 'Kitchen'  
                              , 'Y'  
                              , 8));  
BEGIN  
   FOR indx IN fridge_contents.FIRST .. fridge_contents.LAST  
   LOOP  
      DBMS_OUTPUT.put_line (fridge_contents (indx).to_string);  
   END LOOP;  
END;

FOOD! Eggs benedict-PROTEIN-Farm
DESSERT! Chocolate=N FOOD! Strawberries and cream-FRUIT-Backyard
CAKE! Diameter=8 FOOD! Chocolate Supreme-CARBOHYDATE-Kitchen

Statement 24

CREATE TABLE employees AS SELECT * FROM hr.employees

Table created.

Statement 25

Prior to 12.1, you could not execute this function inside a SQL statement.

Function with Boolean Parameter

CREATE OR REPLACE FUNCTION uc_last_name (  
   employee_id_in   IN employees.employee_id%TYPE,  
   upper_in         IN BOOLEAN)  
   RETURN employees.last_name%TYPE  
IS  
   l_return   employees.last_name%TYPE;  
BEGIN  
   SELECT last_name  
     INTO l_return  
     FROM employees  
    WHERE employee_id = employee_id_in;  
  
   RETURN CASE WHEN upper_in THEN UPPER (l_return) ELSE l_return END;  
END;

Function created.

Statement 26

Hurray! Note, however, that a direct reference to a Boolean literal will NOT work, as in uc_last_name (employee_id, TRUE)

Reference a Boolean Inside SQL!

DECLARE 
   b BOOLEAN := TRUE; 
BEGIN  
   FOR rec IN (SELECT uc_last_name (employee_id, b) lname  
                 FROM employees  
                WHERE department_id = 10)  
   LOOP  
      DBMS_OUTPUT.put_line (rec.lname);  
   END LOOP;  
END;

WHALEN

Statement 27

Bind Boolean in Dynamic SQL

DECLARE  
   l_uc   BOOLEAN := TRUE;  
BEGIN  
   EXECUTE IMMEDIATE  
      'BEGIN DBMS_OUTPUT.PUT_LINE (uc_last_name (138, :b)); END;'  
      USING l_uc;  
END;

STILES

Statement 28

CREATE OR REPLACE FUNCTION is_it_null (value_in IN VARCHAR2) 
   RETURN BOOLEAN 
IS 
BEGIN 
   RETURN value_in IS NULL; 
END;

Function created.

Statement 29
Yep, that works, too - so long as you remember to add the OUT clause to USING! (My first attempts failed due to this).
Bind Variable to Boolean Returned by Function
```
DECLARE  
   l_uc   BOOLEAN := TRUE;  
BEGIN  
   EXECUTE IMMEDIATE 'BEGIN :is_it_null := is_it_null (''abc''); END;'  
      USING OUT l_uc;  
END; 
```
Statement processed.

Statement 30

CREATE OR REPLACE FUNCTION f (x BOOLEAN, y PLS_INTEGER) 
   RETURN employees.employee_id%TYPE 
   AUTHID CURRENT_USER 
AS 
BEGIN 
   IF x 
   THEN 
      RETURN y; 
   ELSE 
      RETURN 2 * y; 
   END IF; 
END;

Function created.

Statement 31

I can't stop! It's too much fun!

More Fun with Booleans in SELECTs

DECLARE  
   name   employees.last_name%TYPE;  
   b      BOOLEAN := TRUE;  
BEGIN  
   SELECT last_name  
     INTO name  
     FROM employees  
    WHERE employee_id = f (b, 100);  
  
   DBMS_OUTPUT.put_line (name);  
 
   b := FALSE;  
  
   SELECT last_name  
     INTO name  
     FROM employees  
    WHERE employee_id = f (b, 100);  
  
   DBMS_OUTPUT.put_line (name);  
END;

King
Whalen

Statement 32

Can we use this associative array type inside SQL with TABLE operator?

Package with Collection of Records (Type)

CREATE OR REPLACE PACKAGE pkg  
   AUTHID DEFINER  
AS  
   TYPE rec IS RECORD  
   (  
      f1   NUMBER,  
      f2   VARCHAR2 (30)  
   );  
  
   TYPE mytab IS TABLE OF rec  
      INDEX BY PLS_INTEGER;  
END;

Package created.

Statement 33

Use of TABLE is no longer restricted to nested tables and varrays.

Yes We Can! (in 12.1)

DECLARE  
   v1   pkg.mytab;    
   v2   pkg.rec;  
   c1   SYS_REFCURSOR;  
BEGIN  
   OPEN c1 FOR SELECT * FROM TABLE (v1);  
  
   FETCH c1 INTO v2;  
  
   CLOSE c1;  
END;

Statement processed.

Statement 34
This has been possible for several versions of Oracle before 12.1.
First, TABLE Operator with Nested Table
```
CREATE OR REPLACE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100); 
```
Type created.

Statement 35

DECLARE 
   happyfamily   list_of_names_t 
                    := list_of_names_t ('Sally', 'Sam', 'Agatha'); 
BEGIN 
   FOR rec IN (  SELECT COLUMN_VALUE family_name 
                   FROM TABLE (happyfamily) 
               ORDER BY family_name) 
   LOOP 
      DBMS_OUTPUT.put_line (rec.family_name); 
   END LOOP; 
END;

Agatha
Sally
Sam

Statement 36

Now TABLE with Package-based Associative Array Type

CREATE OR REPLACE PACKAGE names_pkg  
IS  
   TYPE list_of_names_t IS TABLE OF VARCHAR2 (100)  
      INDEX BY PLS_INTEGER;  
END;

Package created.

Statement 37

Just like with selecting from a relational table, even when I delete elements from the collection referenced in TABLE(), those changes are not reflected in the dataset returned by the query!

Read-Consistency with TABLE (Array)

DECLARE  
   happyfamily   names_pkg.list_of_names_t;  
BEGIN  
   happyfamily (1) := 'Sally';  
   happyfamily (2) := 'Sam';  
   happyfamily (3) := 'Agatha';  
  
   FOR rec IN (  SELECT COLUMN_VALUE family_name  
                   FROM TABLE (happyfamily)  
               ORDER BY family_name)  
   LOOP  
      happyfamily.delete;  
      DBMS_OUTPUT.put_line (rec.family_name);  
      DBMS_OUTPUT.put_line (happyfamily.COUNT);  
   END LOOP;  
  
   DBMS_OUTPUT.put_line (happyfamily.COUNT);  
END;

Agatha
0
Sally
0
Sam
0
0

Statement 38

Again, not possible before 12.1!

Bind a User-Defined Record Type

CREATE OR REPLACE PACKAGE rec_pkg  
AS 
   TYPE rec_t IS RECORD ( 
      number1   NUMBER, 
      number2   NUMBER 
   ); 
 
   PROCEDURE set_rec (n1_in IN NUMBER, n2_in IN NUMBER,  
      rec_out OUT rec_t); 
END rec_pkg;

Package created.

Statement 39

Again, not possible before 12.1!

Bind a User-Defined Record Type

CREATE OR REPLACE PACKAGE BODY rec_pkg 
AS 
   PROCEDURE set_rec (n1_in IN NUMBER, n2_in IN NUMBER,  
      rec_out OUT rec_t) 
   AS 
   BEGIN 
      rec_out.number1 := n1_in; 
      rec_out.number2 := n2_in; 
   END set_rec; 
END rec_pkg;

Package Body created.

Statement 40

Again, not possible before 12.1!

Bind a User-Defined Record Type

DECLARE 
   l_record  rec_pkg.rec_t; 
BEGIN 
   EXECUTE IMMEDIATE 'BEGIN rec_pkg.set_rec (10, 20, :rec); END;'  
      USING OUT l_record; 
 
   DBMS_OUTPUT.put_line ('number1 = ' || l_record.number1); 
   DBMS_OUTPUT.put_line ('number2 = ' || l_record.number2); 
END;

number1 = 10
number2 = 20

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database
Latest Database Tutorials