DBA Hub

📋Steps in this guide1/4

PL/SQL-to-SQL Interface Enhancements for PL/SQL-Only Data Types in Oracle Database 12c Release 1 (12.1)

Oracle 12c now supports the binding of additional PL/SQL-only data types to SQL.

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

BOOLEAN Types

The following code shows an example of binding a type as an in parameter to a PL/SQL function, called in a dynamic SQL statement. Notice what happens if we try to substitute the bound BOOLEAN type for a BOOLEAN literal.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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>

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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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 can be bound in this way, not those using . The following example is similar to the previous associative array example, but it uses a nested table type. The example below is very similar to that for nested tables.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
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>

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>

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. For more information see: - EXECUTE IMMEDIATE Statement Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!