8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
PL/SQL-to-SQL Interface Enhancements for PL/SQL-Only Data Types in Oracle Database 12c Release 1 (12.1)
The PL/SQL language is a combination of two distinct parts:
- PL : Procedural Logic.
- SQL : Structured Query Language
The two are meshed together so well it is easy to forget this separation exists. One area where the separation does show is in the differing support data types. In previous database versions, binding of PL/SQL-only data types from JDBC, OCI, static and native dynamic SQL was a problem. Oracle 12c now supports the binding of additional PL/SQL-only data types to anonymous blocks, PL/SQL function calls in SQL, the TABLE operator in SQL and CALL statements.
There are some restrictions associated with this functionality.
- The PL/SQL-only data type must be a built-in type, or defined in a package specification.
- For associative arrays, only the INDEX BY PLS_INTEGER for us currently supported.
- PL/SQL functions called in SQL can have PL/SQL-only data types bound as input parameters, but they can not return PL/SQL-only data types as SQL does not understand them. Even if they are defined in the package specification.
- SQL still does not understand BOOLEAN types, so a BOOLEAN variable can be bound into an input parameter to a PL/SQL function call in a SQL statement, but BOOLEAN literals (TRUE, FALSE, or NULL) cannot be used directly.
The examples in the documentation are very good, but I will repeat similar tests here to make sure they work as described.
For most PL/SQL programmers, the biggest impact of this change will be be seen in dynamic SQL.
BOOLEAN Types
The following code shows an example of binding a BOOLEAN
type as an in parameter to a PL/SQL function, called in a dynamic SQL statement.
CREATE OR REPLACE FUNCTION boolean_test (p_boolean IN BOOLEAN) RETURN VARCHAR2 AUTHID DEFINER AS BEGIN IF p_boolean THEN RETURN 'TRUE'; ELSE RETURN 'FALSE'; END IF; END; / SET SERVEROUTPUT ON DECLARE l_sql VARCHAR2(32767); l_boolean BOOLEAN := TRUE; l_result VARCHAR2(10); BEGIN l_sql := 'SELECT boolean_test(:l_boolean) INTO :l_result FROM dual'; EXECUTE IMMEDIATE l_sql INTO l_result USING l_boolean; DBMS_OUTPUT.put_line('l_result=' || l_result); END; / l_result=TRUE PL/SQL procedure successfully completed. SQL>
Notice what happens if we try to substitute the bound BOOLEAN type for a BOOLEAN literal.
DECLARE l_sql VARCHAR2(32767); l_result VARCHAR2(10); BEGIN l_sql := 'SELECT boolean_test(TRUE) INTO :l_result FROM dual'; EXECUTE IMMEDIATE l_sql INTO l_result; DBMS_OUTPUT.put_line('l_result=' || l_result); END; / * ERROR at line 1: ORA-00904: "TRUE": invalid identifier ORA-06512: at line 6 SQL>
Record Types
The following code shows an example of binding record types in an anonymous blocked called from dynamic SQL.
CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER AS TYPE rec_type IS RECORD (id NUMBER, description VARCHAR2(50)); PROCEDURE record_test (p_rec OUT rec_type); END; / CREATE OR REPLACE PACKAGE BODY test_pkg AS PROCEDURE record_test (p_rec OUT rec_type) AS BEGIN p_rec.id := 1; p_rec.description := 'ONE'; END; END; / SET SERVEROUTPUT ON DECLARE l_sql VARCHAR2(32767); l_record test_pkg.rec_type; BEGIN l_sql := 'BEGIN test_pkg.record_test(:l_record); END;'; EXECUTE IMMEDIATE l_sql USING OUT l_record; DBMS_OUTPUT.PUT_LINE('l_record.id = ' || l_record.id); DBMS_OUTPUT.PUT_LINE('l_record.description = ' || l_record.description); END; / l_record.id = 1 l_record.description = ONE PL/SQL procedure successfully completed. SQL>
Collections
The following code shows an asociative array being bound. Currently, only associative arrays using INDEX BY PLS_INTEGER
can be bound in this way, not those using INDEX BY VARCHAR2
.
CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER AS TYPE collection_type IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER; FUNCTION display_collection_contents (p_collection IN collection_type) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY test_pkg AS FUNCTION display_collection_contents (p_collection IN collection_type) RETURN NUMBER IS BEGIN FOR i IN p_collection.FIRST .. p_collection.LAST LOOP DBMS_OUTPUT.put_line(i || ' : ' || p_collection(i)); END LOOP; RETURN p_collection.COUNT; END; END; / SET SERVEROUTPUT ON DECLARE l_sql VARCHAR2(32767); l_collection test_pkg.collection_type; l_result NUMBER; BEGIN l_collection(1) := 'ONE'; l_collection(2) := 'TWO'; l_collection(3) := 'THREE'; l_sql := 'SELECT test_pkg.display_collection_contents(:l_collection) INTO :l_result FROM dual'; EXECUTE IMMEDIATE l_sql INTO l_result USING l_collection; DBMS_OUTPUT.put_line('l_result=' || l_result); END; / 1 : ONE 2 : TWO 3 : THREE l_result=3 PL/SQL procedure successfully completed. SQL>
The following example is similar to the previous associative array example, but it uses a nested table type.
CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER AS TYPE collection_type IS TABLE OF VARCHAR2(10); FUNCTION display_collection_contents (p_collection IN collection_type) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY test_pkg AS FUNCTION display_collection_contents (p_collection IN collection_type) RETURN NUMBER IS BEGIN FOR i IN p_collection.FIRST .. p_collection.LAST LOOP DBMS_OUTPUT.put_line(i || ' : ' || p_collection(i)); END LOOP; RETURN p_collection.COUNT; END; END; / SET SERVEROUTPUT ON DECLARE l_sql VARCHAR2(32767); l_collection test_pkg.collection_type; l_result NUMBER; BEGIN l_collection := test_pkg.collection_type('ONE', 'TWO', 'THREE'); l_sql := 'SELECT test_pkg.display_collection_contents(:l_collection) INTO :l_result FROM dual'; EXECUTE IMMEDIATE l_sql INTO l_result USING l_collection; DBMS_OUTPUT.put_line('l_result=' || l_result); END; / 1 : ONE 2 : TWO 3 : THREE l_result=3 PL/SQL procedure successfully completed. SQL>
The VARRAY
example below is very similar to that for nested tables.
CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER AS TYPE collection_type IS VARRAY(10) OF VARCHAR2(10); FUNCTION display_collection_contents (p_collection IN collection_type) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY test_pkg AS FUNCTION display_collection_contents (p_collection IN collection_type) RETURN NUMBER IS BEGIN FOR i IN p_collection.FIRST .. p_collection.LAST LOOP DBMS_OUTPUT.put_line(i || ' : ' || p_collection(i)); END LOOP; RETURN p_collection.COUNT; END; END; / SET SERVEROUTPUT ON DECLARE l_sql VARCHAR2(32767); l_collection test_pkg.collection_type; l_result NUMBER; BEGIN l_collection := test_pkg.collection_type('ONE', 'TWO', 'THREE'); l_sql := 'SELECT test_pkg.display_collection_contents(:l_collection) INTO :l_result FROM dual'; EXECUTE IMMEDIATE l_sql INTO l_result USING l_collection; DBMS_OUTPUT.put_line('l_result=' || l_result); END; / 1 : ONE 2 : TWO 3 : THREE l_result=3 PL/SQL procedure successfully completed. SQL>
TABLE Operator
The following code defines a pipelined table function that accepts a PL/SQL collection and pipes its contents out a rows, so it can be queried using the TABLE operator.
CREATE OR REPLACE PACKAGE test_pkg AUTHID DEFINER AS TYPE collection_type IS TABLE OF VARCHAR2(10); FUNCTION display_collection_contents (p_collection IN collection_type) RETURN collection_type PIPELINED; END; / CREATE OR REPLACE PACKAGE BODY test_pkg AS FUNCTION display_collection_contents (p_collection IN collection_type) RETURN collection_type PIPELINED IS BEGIN FOR i IN p_collection.FIRST .. p_collection.LAST LOOP PIPE ROW (p_collection(i)); END LOOP; RETURN; END; END; / SET SERVEROUTPUT ON DECLARE l_sql VARCHAR2(32767); l_collection test_pkg.collection_type; l_result NUMBER; BEGIN l_collection := test_pkg.collection_type('ONE', 'TWO', 'THREE'); l_sql := 'SELECT COUNT(*) INTO :l_result FROM TABLE(test_pkg.display_collection_contents(:l_collection))'; EXECUTE IMMEDIATE l_sql INTO l_result USING l_collection; DBMS_OUTPUT.put_line('l_result=' || l_result); END; / l_result=3 PL/SQL procedure successfully completed. SQL>
For more information see:
Hope this helps. Regards Tim...