Oracle PL/SQL by Example, Fifth Edition (2015)

---

As mentioned previously, the ability to bind collection data types when employing dynamic SQL has been added in Oracle 12c. Recall that dynamic SQL was covered in Chapter 17, where you learned how to use the EXECUTE IMMEDIATE statement and OPEN-FOR, FETCH, and CLOSEstatements.

Binding Collections with EXECUTE IMMEDIATE Statements

In Chapter 17, you saw numerous examples of the EXECUTE IMMEDIATE statement. All of these examples have one thing in common: The data types of the bind variables are known SQL types. In other words, these data types are all supported by SQL, such as NUMBER and VARCHAR2. In Oracle 12c, it is possible to use bind variables based on the collection and record types, albeit with one restriction applied: The collection or record data type must be declared in the package specification.

Consider the package called TEST_ADM_PKG, shown in Listing 18.3. This package contains definitions of two collection types and three procedures that insert, update, and delete records from the TEST table. (Procedures, functions, and packages are covered in detail in Chapters 19through 21.)

Listing 18.3 TEST_ADM_PKG Package with Collection Types

CREATE OR REPLACE PACKAGE test_adm_pkg
AS
-- Define collection types
TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE row_text_type IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;

-- Define procedures
PROCEDURE populate_test (row_num_tab ROW_NUM_TYPE
,row_num_type ROW_TEXT_TYPE);

PROCEDURE update_test (row_num_tab ROW_NUM_TYPE
,row_num_type ROW_TEXT_TYPE);

PROCEDURE delete_test (row_num_tab ROW_NUM_TYPE);
END test_adm_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_adm_pkg
AS
PROCEDURE populate_test (row_num_tab ROW_NUM_TYPE
,row_num_type ROW_TEXT_TYPE)
IS
BEGIN
FORALL i IN 1..10
INSERT INTO test (row_num, row_text)
VALUES (row_num_tab(i), row_num_type(i));
END populate_test;

PROCEDURE update_test (row_num_tab ROW_NUM_TYPE
,row_num_type ROW_TEXT_TYPE)
IS
BEGIN
FORALL i IN 1..10
UPDATE test
SET row_text = row_num_type(i)
WHERE row_num = row_num_tab(i);
END update_test;

PROCEDURE delete_test (row_num_tab ROW_NUM_TYPE)
IS
BEGIN
FORALL i IN 1..10
DELETE from test
WHERE row_num = row_num_tab(i);
END delete_test;

END test_adm_pkg;
/

This package has both a package specification and a package body. The package specification contains declarations of two associative array types (ROW_NUM_TYPE and ROW_TEXT_TYPE) and three procedures (POPULATE_TEST, UPDATE_TEST, and DELETE_TEST). Each procedure has parameters ROW_NUM_TAB and ROW_TEXT_TAB that are based on the collection types defined in this package. The package body contains the code for the procedures declared in the package specification.

The following example uses this newly created package. References to the package objects are highlighted in bold.

For Example ch18_11a.sql

DECLARE
row_num_tab test_adm_pkg.row_num_type;
row_text_tab test_adm_pkg.row_text_type;

v_rows NUMBER;

BEGIN
-- Populate collections
FOR i IN 1..10
LOOP
row_num_tab(i) := i;
row_text_tab(i) := 'row '||i;
END LOOP;

-- Delete previously added data from the TEST table
test_adm_pkg.delete_test (row_num_tab);

-- Populate TEST table
test_adm_pkg.populate_test (row_num_tab, row_text_tab);
COMMIT;

-- Check how many rows where inserted in the TEST table
-- and display this number on the screen
SELECT COUNT(*)
INTO v_rows
FROM TEST;

DBMS_OUTPUT.PUT_LINE ('There are '||v_rows||' rows in the TEST table');
END;

This example is very similar to the example ch18_1a.sql used in Lab 18.1. It populates the TEST table, checks how many records were added to the TEST table, and displays this information on the screen. The main difference is that it references the TEST_ADM_PKG package when declaring two collection variables and it calls the DELETE_TEST and POPULATE_TEST procedures to delete previously added records to the TEST table and repopulate the table with the new data. Note that all of the references to the packaged objects are prefixed by the package name.

When run, this example produces the following output:

There are 10 rows in the TEST table

Now consider a modified version of this example where calls to the procedures DELETE_TEST and POPULATE_TEST are embedded in the dynamic SQL. All changes are shown in bold.

For Example ch18_11b.sql

DECLARE
row_num_tab test_adm_pkg.row_num_type;
row_text_tab test_adm_pkg.row_text_type;

v_dyn_sql VARCHAR2(1000);
v_rows NUMBER;

BEGIN
-- Populate collections
FOR i IN 1..10
LOOP
row_num_tab(i) := i;
row_text_tab(i) := 'row '||i;
END LOOP;

-- Delete previously added data from the TEST table
v_dyn_sql := 'begin test_adm_pkg.delete_test (:row_num_tab); end;';
EXECUTE IMMEDIATE v_dyn_sql USING row_num_tab;

-- Populate TEST table
v_dyn_sql := 'begin test_adm_pkg.populate_test (:row_num_tab, :row_text_tab); end;';
EXECUTE IMMEDIATE v_dyn_sql USING row_num_tab, row_text_tab;
COMMIT;

-- Check how many rows where inserted in the TEST table
-- display it on the screen
SELECT COUNT(*)
INTO v_rows
FROM TEST;

DBMS_OUTPUT.PUT_LINE ('There are '||v_rows||' rows in the TEST table');
END;

This version of the script declares a new variable, v_dyn_sql, that is used to store dynamic SQL statement. Next, the calls to the DELETE_TEST and POPULATE_TEST procedures are replaced by the dynamic SQL statements that are executed by the EXECUTE IMMEDIATEstatement.

Notice the syntax of the dynamic SQL statements. In the original example, the packaged procedures are invoked in this way:

-- Delete previously added data from the TEST table
test_adm_pkg.delete_test (row_num_tab);

-- Populate TEST table
test_adm_pkg.populate_test (row_num_tab, row_text_tab);

In the modified version of the example, calls to these procedures are placed between the BEGIN and END statements:

-- Delete previously added data from the TEST table
v_dyn_sql := 'begin test_adm_pkg.delete_test (:row_num_tab); end;';
EXECUTE IMMEDIATE v_dyn_sql USING row_num_tab;

-- Populate TEST table
v_dyn_sql := 'begin test_adm_pkg.populate_test (:row_num_tab, :row_text_tab); end;';
EXECUTE IMMEDIATE v_dyn_sql USING row_num_tab, row_text_tab;

This approach is used because each dynamic SQL statement is executed as an anonymous PL/SQL block and, therefore, requires BEGIN and END statements. If these BEGIN and END statements are omitted from the dynamic SQL, the script will not execute successfully. This is illustrated by the following example (affected statements are shown in bold):

For Example ch18_11c.sql

DECLARE
row_num_tab test_adm_pkg.row_num_type;
row_text_tab test_adm_pkg.row_text_type;

v_dyn_sql VARCHAR2(1000);
v_rows NUMBER;

BEGIN
-- Populate collections
FOR i IN 1..10
LOOP
row_num_tab(i) := i;
row_text_tab(i) := 'row '||i;
END LOOP;

-- Delete previously added data from the TEST table
v_dyn_sql := 'test_adm_pkg.delete_test (:row_num_tab);';
EXECUTE IMMEDIATE v_dyn_sql USING row_num_tab;

-- Populate TEST table
v_dyn_sql := 'test_adm_pkg.populate_test (:row_num_tab, :row_text_tab);';
EXECUTE IMMEDIATE v_dyn_sql USING row_num_tab, row_text_tab;
COMMIT;

-- Check how many rows where inserted in the TEST table
-- display it on the screen
SELECT COUNT(*)
INTO v_rows
FROM TEST;

DBMS_OUTPUT.PUT_LINE ('There are '||v_rows||' rows in the TEST table');
END;

This version of the script produces the following error:

ORA-00900: invalid SQL statement
ORA-06512: at line 18

As mentioned previously, starting with Oracle 12c you can also use bind variables based on the record types. Similar to the collection types, the record types must be defined in the package specification. Consider the modified version of TEST_ADM_PKG shown in Listing 18.4. The package now contains a definition of a record type and a new procedure that populates a record variable from the TEST table using the newly created record type. Newly added items are shown in bold.

Listing 18.4 TEST_ADM_PKG Package with Record Type

CREATE OR REPLACE PACKAGE test_adm_pkg
AS
-- Define collection types
TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE row_text_type IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;

-- Define record type
TYPE rec_type IS RECORD
(row_num NUMBER
,row_text VARCHAR2(10));

-- Define procedures
PROCEDURE populate_test (row_num_tab ROW_NUM_TYPE
,row_num_type ROW_TEXT_TYPE);

PROCEDURE update_test (row_num_tab ROW_NUM_TYPE
,row_num_type ROW_TEXT_TYPE);

PROCEDURE delete_test (row_num_tab ROW_NUM_TYPE);

PROCEDURE populate_test_rec (row_num_val IN NUMBER
,test_rec OUT REC_TYPE);
END test_adm_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_adm_pkg
AS
PROCEDURE populate_test (row_num_tab ROW_NUM_TYPE
,row_num_type ROW_TEXT_TYPE);
IS
BEGIN
FORALL i IN 1..10
INSERT INTO test (row_num, row_text)
VALUES (row_num_tab(i),row_num_type(i));
END populate_test;

PROCEDURE update_test (row_num_tab ROW_NUM_TYPE
,row_num_type ROW_TEXT_TYPE);
IS
BEGIN
FORALL i IN 1..10
UPDATE test
SET row_text = row_num_type(i)
WHERE row_num = row_num_tab(i);
END update_test;

PROCEDURE delete_test (row_num_tab ROW_NUM_TYPE)
IS
BEGIN
FORALL i IN 1..10
DELETE from test
WHERE row_num = row_num_tab(i);
END delete_test;
PROCEDURE populate_test_rec (row_num_val IN NUMBER
,test_rec OUT REC_TYPE)
IS
BEGIN
SELECT *
INTO test_rec
FROM test
WHERE row_num = row_num_val;
END populate_test_rec;

END test_adm_pkg;
/

This version of the package contains definitions of the user-defined record type, rec_type, and a new procedure, POPULATE_TEST_REC, that selects a record from the TEST table into the test_rec parameter based on the row_num value provided at run time. Note the IN and OUTparameter modes specified in the POPULATE_TEST_REC procedure header: They denote that the row_num_val parameter is used to pass a value into the procedure, and that the test_rec parameter is used to pass a value from the procedure. (Parameter modes are covered in detail inChapters 19 through 21.)

The next example uses the newly created record type and procedure to display a record from the TEST table.

For Example ch18_12a.sql

DECLARE
test_rec test_adm_pkg.rec_type;

v_dyn_sql VARCHAR2(1000);

BEGIN
-- Select record from the TEST table
v_dyn_sql := 'begin test_adm_pkg.populate_test_rec (:val, :rec); end;';
EXECUTE IMMEDIATE v_dyn_sql USING IN 10, OUT test_rec;
COMMIT;

-- Display newly selected record
DBMS_OUTPUT.PUT_LINE ('test_rec.row_num = '||test_rec.row_num);
DBMS_OUTPUT.PUT_LINE ('test_rec.row_text = '||test_rec.row_text);
END;

In this example, the USING clause in the EXECUTE IMMEDIATE statement contains parameter modes. This is done to ensure that the variables used by the EXECUTE IMMEDIATE statement have the same modes as the parameters in the procedure. When run, this example produces the following output:

test_rec.row_num = 10
test_rec.row_text = row 10

Binding Collections with OPEN-FOR, FETCH, and CLOSE Statements

Recall that the OPEN-FOR, FETCH, and CLOSE statements are used with multirow queries or cursors. This is illustrated by the example below.

For Example ch18_13a.sql

DECLARE
TYPE student_cur_typ IS REF CURSOR;

student_cur student_cur_typ;
student_rec student%ROWTYPE;

v_zip_code student.zip%TYPE := '06820';

BEGIN
OPEN student_cur
FOR 'SELECT * FROM student WHERE zip = :my_zip' USING v_zip_code;

LOOP
FETCH student_cur INTO student_rec;
EXIT WHEN student_cur%NOTFOUND;

-- Display student ID, first and last names
DBMS_OUTPUT.PUT_LINE ('student_rec.student_id = '||student_rec.student_id);
DBMS_OUTPUT.PUT_LINE ('student_rec.first_name = '||student_rec.first_name);
DBMS_OUTPUT.PUT_LINE ('student_rec.last_name = '||student_rec.last_name);
END LOOP;
CLOSE student_cur;
END;

The declaration portion of this script specifies the cursor type, student_cur_typ, defined as REF CURSOR, and a cursor variable, student_cur, based on this type. Next, it defines a record variable, student_rec, based on the STUDENT table.

The executable portion of the script associates the SELECT statement with the STUDENT table for a given ZIP code with the student_cur variable and opens it. Next, each row returned by the SELECT statement is fetched into the student_rec variable and the student’s ID, first name, and last name are displayed on the screen. Once all the records returned by the SELECT statement are fetched, the cursor terminates.

When run, this example produces the following output:

student_rec.student_id = 240
student_rec.first_name = Z.A.
student_rec.last_name = Scrittorale
student_rec.student_id = 326
student_rec.first_name = Piotr
student_rec.last_name = Padel
student_rec.student_id = 360
student_rec.first_name = Calvin
student_rec.last_name = Kiraly

Next, consider a modified version of this example where all student records for a given ZIP code are fetched at once into a collection of records. Recall that to bind a collection or a record type, the following restriction must be respected: The collection or record data type must be declared in the package specification. To comply with this rule, the STUDENT_ADM_PKG package shown in Listing 18.5 is created specifically for this purpose.

Listing 18.5 STUDENT_ADM_PKG Package with Record and Collection Types

CREATE OR REPLACE PACKAGE student_adm_pkg
AS
-- Define collection type
TYPE student_tab_type IS TABLE OF student%ROWTYPE INDEX BY PLS_INTEGER;

-- Define procedures
PROCEDURE populate_student_tab (zip_code IN VARCHAR2
,student_tab OUT student_tab_type);

PROCEDURE display_student_info (student_rec student%ROWTYPE);

END student_adm_pkg;
/

CREATE OR REPLACE PACKAGE BODY student_adm_pkg
AS
PROCEDURE populate_student_tab (zip_code IN VARCHAR2
,student_tab OUT student_tab_type)

IS
BEGIN
SELECT *
BULK COLLECT INTO student_tab
FROM student
WHERE zip = zip_code;
END populate_student_tab;

PROCEDURE display_student_info (student_rec student%ROWTYPE)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE ('student_rec.zip = '||student_rec.zip);
DBMS_OUTPUT.PUT_LINE ('student_rec.student_id = '||student_rec.student_id);
DBMS_OUTPUT.PUT_LINE ('student_rec.first_name = '||student_rec.first_name);
DBMS_OUTPUT.PUT_LINE ('student_rec.last_name = '||student_rec.last_name);
END display_student_info;

END student_adm_pkg;
/

The package specification declares the associative array type, student_tab_type, where each element of the collection is a record of the student%ROWTYPE. Next, it declares the populate_student_tab procedure, which accepts the value of a ZIP code and returns a collection of records, student_tab, populated via the BULK COLLECT INTO statement. Note the parameter modes specified in the procedure declaration. The zip_code is specified as an IN parameter. Based on its value, the OUT parameter student_tab is populated via theSELECT statement from the STUDENT table.

The second procedure, display_student_info, accepts one input parameter, student_rec based on the STUDENT table and displays the student’s ZIP code, ID, first name, and last name on the screen.

The package body contains the executable code of the populate_student_tab and display_student_info procedures.

The following modified version of example ch18_13a.sql employs this newly created package. References to packages objects are highlighted in bold.

For Example ch18_13b.sql

DECLARE
TYPE student_cur_typ IS REF CURSOR;
student_cur student_cur_typ;

-- Collection and record variables
student_tab student_adm_pkg.student_tab_type;
student_rec student%ROWTYPE;

BEGIN
-- Populate collection of records
student_adm_pkg.populate_student_tab ('06820', student_tab);

OPEN student_cur
FOR 'SELECT * FROM TABLE(:my_table)' USING student_tab;

LOOP
FETCH student_cur INTO student_rec;
EXIT WHEN student_cur%NOTFOUND;

student_adm_pkg.display_student_info (student_rec);
END LOOP;
CLOSE student_cur;
END;

This version of the script declares a collection of records, student_tab, based on the collection type defined in the STUDENT_ADM_PKG package. The execution section of the script populates the student_tab collection with the records from the STUDENT table for a particular ZIP code. This is accomplished by calling the populate_student_tab procedure defined in the STUDENT_ADM_PKG package. Next, the student records are selected from the newly populated student_tab collection. Note the usage of the built-in TABLE function in the SELECTstatement.