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