Wednesday, 21 September 2016

PL/SQL-to-SQL Interface Enhancements for PL/SQL-Only Data Types in Oracle Database 12c

The PL/SQL language is a combination of two distinct parts:
  1. PL : Procedural Logic.
  2. 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.
  1. BOOLEAN Types
  2. Record Types
  3. Collections
  4. TABLE Operator

1. 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>

2. 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>

3. 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>


4. 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>