New improvements are available in Oracle Database 12c for PL/SQL-only data types.

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
• the TABLE operator in SQL queries.

Bind variables to anonymous blocks

example for predefined type BOOLEAN

CREATE OR REPLACE PROCEDURE prc_test_boolean(in_boolean IN BOOLEAN) 
IS
BEGIN
  IF in_boolean THEN
    dbms_output.put_line('true');
  END IF;
END;
/

--bind to anonymous block
DECLARE
  l_sql      CLOB;
  l_boolean  BOOLEAN := TRUE;
BEGIN
  l_sql := 'BEGIN prc_test_boolean(:x); END;';
  EXECUTE IMMEDIATE l_sql 
    USING l_boolean; /* <- not possible in 11G */
END;
/

example for defined type in package

CREATE OR REPLACE PACKAGE test_pkg
AS
  TYPE t_rec IS record 
  (
    num1 NUMBER, 
    num2 NUMBER
  );
 
  PROCEDURE test_prc
  (
    out_rec  OUT t_rec, 
    in_num1   IN NUMBER, 
    in_num2   IN NUMBER
  );
END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg AS
  PROCEDURE test_prc
  (
    out_rec OUT t_rec, 
    in_num1  IN NUMBER, 
    in_num2  IN NUMBER
  )
  IS
  BEGIN
    out_rec.num1 := in_num1;
    out_rec.num2 := in_num2;
  END test_prc;
END test_pkg;
/

--anonymous block
DECLARE
  l_rec   test_pkg.t_rec;
  l_sql   CLOB;
BEGIN
  l_sql := 'BEGIN test_pkg.test_prc(:out_rec, 5, 3); END;';
 
  EXECUTE IMMEDIATE l_sql 
    USING OUT l_rec; /* <- not possible in 11G */
END;
/

bind variables to SQL queries and  CALL statements

Example for sql queries

CREATE OR REPLACE FUNCTION fn_test_boolean(in_boolean IN boolean) 
RETURN NUMBER
IS
BEGIN
  IF in_boolean THEN
    dbms_output.put_line('true');
  END IF;
  
  RETURN 0;
END;
/

--bind to sql statment
DECLARE
  l_sql      CLOB;
  l_boolean  boolean := TRUE;
  l_num      NUMBER;
BEGIN
  l_sql := 'SELECT fn_test_boolean(:x) FROM DUAL';

  EXECUTE IMMEDIATE l_sql INTO l_num 
  USING l_boolean; /* <- not possible in 11G */

  SELECT fn_test_boolean(l_boolean) /* <- not possible in 11G */
    INTO l_num
    FROM DUAL;
END;
/

TABLE operator in sql queries

Example for TABLE operator. Finally it can work directly on type in package. It’s not necessary to define type outside package like for previous releases.

CREATE OR REPLACE PACKAGE pkg_test 
AS
  TYPE t_rec IS record
  (
    p1  NUMBER, 
    p2  VARCHAR2(30)
  );
  
  TYPE t_tab IS TABLE OF t_rec INDEX BY BINARY_INTEGER;
END;
/

set serveroutput on
DECLARE
  l_tbl pkg_test.t_tab;  -- collection of records
  l_rec pkg_test.t_rec;
  l_cur SYS_REFCURSOR;
BEGIN
  l_tbl(1).p1 := 10;
  l_tbl(1).p2 := 'test data';
  
  OPEN  l_cur 
   FOR 'SELECT * FROM TABLE(:1)' /* <- not possible in 11G */
  USING l_tbl;
  FETCH l_cur INTO l_rec;
  CLOSE l_cur;
  
  DBMS_OUTPUT.PUT_LINE('P1: ' || l_rec.p1 || ' P2: ' || l_rec.p2);
  
  FOR i IN (SELECT * FROM TABLE(l_tbl)) /* <- not possible in 11G */
  loop
    dbms_output.put_line('P1: ' || i.p1 || ' P2: ' || i.p2);
  end loop;
END;
/

Limitations:

• The PL/SQL-only data type must be either predefined like BOOLEAN or declared in a package specification
• If the PL/SQL-only data type is an associative array, then it must be indexed by PLS_INTEGER
• A PL/SQL function cannot return a value of a PL/SQL-only type to SQL.
• A BOOLEAN literal (TRUE,FALSE or NULL) cannot be an argument to a PL/SQL function that is called from a static SQL query or from a Java/JDBC application.
• In SQL contexts, you cannot use a function whose return type was declared in a package specification.

Have a fun 🙂

Tomasz