Oracle PL/SQL by Example- P9

. Use collections of records PL/SQL also lets you define a collection of records (such as an associative array whose element type is a cursor-based record, as shown in the following name_tab(v_counter).first_name); name_tab(v_counter).last_name); name_tab(v_counter).first_name name_tab(v_counter).last_name Notice that to reference each row of the array, you use the counter variable, just like in all previ- However, because each row of this table is a record, you must also reference indi- 16.3.1 Use Collections of Records Instead of using an associative array, use a nested table. name_tab(v_counter).first_name); name_tab(v_counter).last_name); In the preceding script,name_tab is declared as a nested table. name_tab(v_counter).first_name); name_tab(v_counter).last_name); Instead of using a cursor-based record, use a TYPE student_rec_type IS RECORD TYPE name_type IS TABLE OF student_rec_type name_tab(v_counter).enrollments := name_rec.total; name_tab(v_counter).first_name); name_tab(v_counter).last_name); In the declaration portion of the script, the cursor SELECT statement has been modified so that for statement so that only the first four records of the index-by table are displayed on the screen. 1) Create an associative array with the element type of a user-defined record. Instead of using an associative array, use a nested table. Instead of using a nested table, use a varray. . EXECUTE IMMEDIATE statements SELECT statements are called static because they do not change from execution Now, consider a different type of PL/SQL application in which SQL statements Because these SQL statements convenient to use than the Oracle-supplied package DBMS_SQL, which has Statements . Use the EXECUTE IMMEDIATE statement Generally, dynamic SQL statements are built by your program and are stored as character strings These strings must contain valid SQL statements Consider the following dynamic SQL statement: This SELECT statement returns a student’s first and last name for a given student ID. statements: To process dynamic SQL statements, you use EXECUTE IMMEDIATE or OPEN-FOR, FETCH, EXECUTE IMMEDIATE is used for single-row SELECT statements, all To improve the performance of dynamic SQL statements you can also use BULK EXECUTE IMMEDI- ATE, BULK FETCH, FORALL, and COLLECT INTO statements. THE EXECUTE IMMEDIATE STATEMENT The EXECUTE IMMEDIATE statement parses a dynamic statement or a PL/SQL block for imme- dynamic_SQL_string is a string that contains a valid SQL statement or a PL/SQL block. SELECT statement. This clause is used when a dynamic SQL statement returns a single row similar to a static SELECT INTO statement. ments whose values are passed to the dynamic SQL statement or PL/SQL block. of bind arguments that store values returned by the dynamic SQL statement or PL/SQL block. When an EXECUTE IMMEDIATE statement contains both USING and RETURNING INTO clauses, the v_zip VARCHAR2(5) := '11106'; v_total_students NUMBER; v_new_zip VARCHAR2(5); v_student_id NUMBER := 151; -- Create table MY_STUDENT sql_stmt := 'CREATE TABLE my_student '|| 'AS SELECT * FROM student WHERE zip = '||v_zip; -- Select total number of records from MY_STUDENT table INTO v_total_students; DBMS_OUTPUT.PUT_LINE ('Students added: '||v_total_students); -- Update record in MY_STUDENT table sql_stmt := 'UPDATE my_student SET zip = 11105 WHERE student_id = EXECUTE IMMEDIATE sql_stmt USING v_student_id RETURNING INTO DBMS_OUTPUT.PUT_LINE ('New zip code: '||v_new_zip); This script contains several examples of dynamic SQL. First, you create the table MY_STUDENT and populate it with records for a specified value of It is important to note that the variable v_zip is concatenated with the CREATE state- Second, you select the total number of students added to the MY_STUDENT table and display You use the INTO option with the EXECUTE IMMEDIATE statement because the SELECT statement returns a single row. Third, you create a simple PL/SQL block in which you select the current date and display it on Because the PL/SQL block does not contain any bind arguments, the EXECUTE Finally, you update the MY_STUDENT table for a given student ID and return a new value of zip code using the RETURNING statement. student ID to the UPDATE statement at runtime, and the RETURNING INTO option allows you to pass a new value of zip code from the UPDATE statement into your program. EXECUTE IMMEDIATE Statements v_zip VARCHAR2(5) := '11106'; v_total_students NUMBER; EXECUTE IMMEDIATE 'DROP TABLE my_student'; -- Create table MY_STUDENT sql_stmt := 'CREATE TABLE my_student '|| EXECUTE IMMEDIATE sql_stmt USING v_zip; -- Select total number of records from MY_STUDENT table INTO v_total_students; DBMS_OUTPUT.PUT_LINE ('Students added: '|| v_total_students); First, you drop the MY_STUDENT table created in the previous version of the example. you re-create the MY_STUDENT table, but in this case, you use a bind argument to pass a value of zip code to the CREATE statement at runtime. A CREATE TABLE statement is a data definition statement. In this version, you pass the table name as a bind argument to the SELECT statement. v_zip VARCHAR2(5) := '11106'; v_total_students NUMBER; -- Create table MY_STUDENT sql_stmt := 'CREATE TABLE my_student '|| 'AS SELECT * FROM student '|| 'WHERE zip ='|| v_zip; -- Select total number of records from MY_STUDENT table EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :my_table' INTO v_total_students DBMS_OUTPUT.PUT_LINE ('Students added: '|| v_total_students); This example causes an error because you cannot pass names of schema objects to dynamic SQL example with the SELECT statement: EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||my_table INTO v_total_students; As mentioned earlier, a dynamic SQL string can contain any SQL statement or PL/SQL block. However, unlike static SQL statements, a dynamic SQL statement should not be terminated with Consider a different version of the same example in which the SELECT statement is terminated If you created the MY_STUDENT table based on the corrected version of the preceding script, you EXECUTE IMMEDIATE Statements v_zip VARCHAR2(5) := '11106'; v_total_students NUMBER; -- Create table MY_STUDENT sql_stmt := 'CREATE TABLE my_student '|| 'AS SELECT * FROM student '|| 'WHERE zip = '||v_zip; -- Select total number of records from MY_STUDENT table INTO v_total_students; DBMS_OUTPUT.PUT_LINE ('Students added: '|| v_total_students); The semicolon added to the SELECT statement is treated as an invalid character when the state- A somewhat similar error is generated when a PL/SQL block is In some cases you may need to pass a NULL value to a dynamic SQL statement as a value for a SQL and the EXECUTE IMMEDIATE statement: PL/SQL: Statement ignored of the SQL types. To pass a NULL value to the dynamic SQL statement, you should modify this EXECUTE IMMEDIATE Statements Because the variable v_null has not been initialized, its value 17.1.1 Use the EXECUTE IMMEDIATE Statement Create the following PL/SQL script: USING v_student_id; ANSWER: The declaration portion of the script declares the string that contains the dynamic SQL statement, and three variables to hold student’s ID, first name, and last name, respectively. executable portion of the script contains a dynamic SQL statement with one bind argument that is used to pass the value of the student ID to the SELECT statement at runtime. variables contain results returned by the SELECT statement. able v_student_id, which is used to pass a value to the SELECT statement at runtime. two DBMS_OUTPUT.PUT_LINE statements are used to display the results of the SELECT statement new 3: v_student_id NUMBER := 105; B) Modify the script so that the student’s address (street, city, state, and zip code) is displayed on the v_zip VARCHAR2(5); USING v_student_id; DBMS_OUTPUT.PUT_LINE ('Zip Code: '||v_zip); v_zip. Next, you modify the dynamic SQL statement so that it can return the student’s address. DBMS_OUTPUT.PUT_LINE statements to display the student’s address on the screen. new 3: v_student_id NUMBER := 105; so that v_zip and v_state were misplaced while the SELECT statement remained v_zip VARCHAR2(5); -- variables v_state and v_zip are misplaced USING v_student_id; DBMS_OUTPUT.PUT_LINE ('Zip Code: '||v_zip); new 3: v_student_id NUMBER := 105; ORA-06502: PL/SQL: numeric or value error This error is generated because the variable v_state can hold up to two characters. C) Modify the script created in the previous exercise (ch17_1b.sql) so that the SELECT statement can table name used in the SELECT statement at runtime. v_table_name VARCHAR2(20) := '&sv_table_name'; v_zip VARCHAR2(5); ' FROM '||v_table_name||' a, zipcode b' || ' AND '||v_table_name||'_id = :1'; DBMS_OUTPUT.PUT_LINE ('Zip Code: '||v_zip); The declaration portion of the script contains a new variable,v_table_name, which holds the In addition, the variable v_student_id has been replaced by the variable v_id because it is not known in advance which table, STUDENT or The executable portion of the script contains a modified dynamic SQL statement. statement does not contain any information specific to the STUDENT or INSTRUCTOR tables. other words, the dynamic SQL statement used by the previous version (ch17_1b.sql) ' FROM '||v_table_name||' a, zipcode b' || ' AND '||v_table_name||'_id = :1'; The table name (student) has been replaced by the variable v_table_name in the FROM and statements that are not based on a specific table, because you do not always know in advance Enter value for sv_table_name: student old 3: v_table_name VARCHAR2(20) := '&sv_table_name'; new 3: v_table_name VARCHAR2(20) := 'student'; old 3: v_table_name VARCHAR2(20) := '&sv_table_name'; new 3: v_table_name VARCHAR2(20) := 'instructor'; Statements . Use OPEN-FOR, FETCH, and CLOSE statements The OPEN-FOR, FETCH, and CLOSE statements are used for multirow queries or cursors. In the case of dynamic SQL, the OPEN-FOR statement has an optional USING clause that allows OPEN cursor_variable FOR dynamic_SQL_string 'SELECT first_name, last_name FROM student '|| 'WHERE zip = :1' USING v_zip; In this code fragment, you define a weak cursor type, student_cur_type. a cursor variable student_cur based on the REF CURSOR type specified in the preceding At runtime, the student_cur variable is associated with the SELECT statement that returns the first and last names of students for a given value of zip. As mentioned earlier, the FETCH statement returns a single row from the result set into a list of variables defined in a PL/SQL block and moves the cursor to the next row. statement is as follows: Adding the previous example, you fetch the student’s first and last names into variables speci- records to process using the EXIT WHEN statement. 'SELECT first_name, last_name FROM student '|| 'WHERE zip = :1' USING v_zip; The CLOSE statement disassociates the cursor variable with the multirow query. CLOSE statement is as follows: 'SELECT first_name, last_name FROM student '|| 'WHERE zip = :1' USING v_zip; new 5: v_zip VARCHAR2(5) := '11236'; 17.2.1 Use OPEN-FOR, FETCH, and CLOSE Statements Create the following PL/SQL script: v_zip VARCHAR2(5); sql_stmt := 'SELECT zip, COUNT(*) total'|| DBMS_OUTPUT.PUT_LINE ('Zip code: '||v_zip|| Consider the use of spaces in the SQL statements generated dynamically. string that holds the dynamic SQL statement consists of three concatenated strings, where each string is sql_stmt := 'SELECT zip, COUNT(*) total'|| If no space is added after the STUDENT table, the resulting SELECT statement define a string variable to hold a dynamic SQL statement, and two variables,v_zip and v_total, to hold data returned by the cursor. In the executable portion of the script, you generate a dynamic SQL statement, associate it with fetch values of zip code and total number of students into the variables v_zip and v_total, B) Modify the script you just created (ch17_2a.sql) so that the SELECT statement can be run against either the STUDENT or INSTRUCTOR table. in the SELECT statement at runtime. v_table_name VARCHAR2(20) := '&sv_table_name'; v_zip VARCHAR2(5); DBMS_OUTPUT.PUT_LINE ('Totals from '||v_table_name|| sql_stmt := 'SELECT zip, COUNT(*) total'|| ' FROM '||v_table_name||' '|| DBMS_OUTPUT.PUT_LINE ('Zip code: '||v_zip|| In this version of the script, you add a variable,v_table_name, to hold the name of a table You also add a DBMS_OUTPUT.PUT_LINE table to display a message stating Next, you modify the dynamic SQL statement as sql_stmt := 'SELECT zip, COUNT(*) total'|| ' FROM '||v_table_name||' '|| The variable v_table_name has been inserted in place of the actual table name (STUDENT). Note that you concatenate a space to the variable v_table_name so that the SELECT state- The first run is based on the STUDENT table, Enter value for sv_table_name: student old 5: v_table_name VARCHAR2(20) := '&sv_table_name'; new 5: v_table_name VARCHAR2(20) := 'student'; Totals from student table old 5: v_table_name VARCHAR2(20) := '&sv_table_name'; new 5: v_table_name VARCHAR2(20) := 'instructor'; So far you have seen that values returned by the dynamic SQL statements are stored in individual order of the corresponding columns returned by the SELECT statement. somewhat cumbersome when a dynamic SQL statement returns more than a few columns. result, PL/SQL allows you to store values returned by the dynamic SELECT statements in the vari- v_table_name VARCHAR2(20) := '&sv_table_name'; DBMS_OUTPUT.PUT_LINE ('Totals from '||v_table_name|| sql_stmt := 'SELECT zip, COUNT(*) total'|| ' FROM '||v_table_name||' '|| Enter value for sv_table_name: student old 10: v_table_name VARCHAR2(20) := '&sv_table_name'; new 10: v_table_name VARCHAR2(20) := 'student'; Totals from student table old 10: v_table_name VARCHAR2(20) := '&sv_table_name'; new 10: v_table_name VARCHAR2(20) := 'instructor'; the material on native dynamic SQL and puts it to use within the context of stored procedures. . The FORALL statement SQL statements to the SQL engine, which returns results to the PL/SQL engine. statement affects four or more rows, bulk SQL may improve performance signif- Bulk SQL supports batch processing of SQL statements and their results. It consists of two features—the FORALL statement and the BULK COLLECT The FORALL Statement . Use the FORALL statement This INSERT statement is sent from PL/SQL to SQL ten times. If you replace the FOR loop with the FORALL statement, the INSERT state- The FORALL statement has the following structure (the reserved words in brackets are SQL_STATEMENT [SAVE EXCEPTIONS]; The FORALL statement has an implicitly defined loop counter variable associated with it. of a particular collection, which is either a nested table or an associative array. . When the collection referenced by the VALUES OF clause is empty, the FORALL statement Next, SQL_STATEMENT is either a static or dynamic INSERT, UPDATE, or DELETE statement the FORALL statement to continue even when SQL_STATEMENT causes an exception. Consider the following example that illustrates how the FORALL statement may be used. example, as well as other examples in this chapter, uses a test table created specifically for this CREATE TABLE test (row_num NUMBER, row_text VARCHAR2(10)); 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; ('There are '||v_total||' rows in the TEST table'); As mentioned earlier, when SQL statements are used with FORALL statements, they reference So, in this script, you define two collection types as associative arrays and FORALL statement. In this example, you compare the execution times of the INSERT statements 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; The FORALL Statement DBMS_OUTPUT.PUT_LINE ('Duration of the FORALL statement: '|| To calculate execution times of the FOR loop and the FORALL statement, you employ the As mentioned previously, the SAVE EXCEPTIONS option enables the FORALL statement to continue even when the corresponding SQL statement causes an exception. are stored in the cursor attribute called SQL%BULK_EXCEPTIONS. EXCEPTIONS cursor attribute is a collection of records in which each record consists of two iteration of the FORALL statement during which an exception was encountered, and the The number of exceptions that occurred during the execution of the FORALL statement can be Next, consider an example of the FORALL statement with the SAVE EXCEPTIONS option: TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER; TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER; -- Modify 1, 5, and 7 elements of the V_ROW_TEXT collection -- These rows will cause exception in the FORALL statement The FORALL Statement SQL%BULK_EXCEPTIONS.COUNT LOOP SQL%BULK_EXCEPTIONS(i).error_index||' caused error '||i|| ': '||SQL%BULK_EXCEPTIONS(i).ERROR_CODE||' '|| SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)); which, in this case, is the INSERT statement that uses collection elements. the COUNT method on the SQL%BULK_EXCEPTIONS collection. exception information such as which record number caused an exception and the error message To display the number of the record that caused an exception, 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; ('There are '||v_total||' rows in the TEST table'); the FORALL statement iterates seven times, and seven rows are added to the TEST table. The FORALL Statement The VALUES OF option specifies that the values of the loop counter in the FORALL statement a group of indexes that the FORALL statement can loop through. CREATE TABLE TEST_EXC (row_num NUMBER, row_text VARCHAR2(50)); TYPE row_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER; TYPE row_text_type IS TABLE OF VARCHAR2(11) INDEX BY PLS_INTEGER; -- Modify 1, 5, and 7 elements of the V_ROW_TEXT collection -- These rows will cause exception in the FORALL statement -- Populate V_EXC_IND_TAB collection to be used in the VALUES SQL%BULK_EXCEPTIONS.COUNT LOOP exc_ind_tab(i) := SQL%BULK_EXCEPTIONS(i).error_index; -- Insert records that caused exceptions in the TEST_EXC The newly created table is used to store records that cause exceptions when they are inserted into the TEST table. Next, you modify the first, fifth, and seventh elements of the row_text_tab table to cause exceptions in the FORALL statement. exc_ind_tab collection with index values of rows that caused the exceptions. SQL%BULK_EXCEPTION collection. After this script is executed, the TEST and TEST_EXC tables contain the following records: The FORALL Statement 18.1.1 Use the FORALL Statement late this table using the FORALL statement. available for the FORALL statement, such as SAVE EXCEPTIONS, INDICES OF, and VALUES OF. Create the MY_ZIPCODE table as follows: This statement creates an empty MY_ZIPCODE table because the criterion specified in the WHERE clause Create the following PL/SQL script: TYPE string_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER; -- statement zip_tab(v_counter) := rec.zip; state_tab(v_counter) := rec.state; cr_by_tab(v_counter) := rec.created_by; cr_date_tab(v_counter) := rec.created_date; mod_by_tab(v_counter) := rec.modified_by; mod_date_tab(v_counter) := rec.modified_date; -- Check how many records were added to MY_ZIPCODE table (v_total||' records were added to MY_ZIPCODE table'); ANSWER: This script populates the MY_ZIPCODE table with records selected from the ZIPCODE To enable use of the FORALL statement, it employs seven collections. The script uses cursor FOR loop to populate the individual collections and then uses them with the FORALL statement to populate the Finally, it checks how many records were added to the MY_ZIPCODE table 19 records were added to MY_ZIPCODE table B) Modify the previous version of the script as follows: Select data from the ZIPCODE table for a Modify the selected records so that they will cause various exceptions in the FORALL statement. Modify the FORALL statement so that it does not fail when an exception TYPE string_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER; -- statement zip_tab(v_counter) := rec.zip; state_tab(v_counter) := rec.state; cr_by_tab(v_counter) := rec.created_by; cr_date_tab(v_counter) := rec.created_date; mod_by_tab(v_counter) := rec.modified_by; mod_date_tab(v_counter) := rec.modified_date; FORALL i in 1..zip_tab.COUNT SAVE EXCEPTIONS -- Check how many records were added to MY_ZIPCODE table (v_total||' records were added to MY_ZIPCODE table'); SQL%BULK_EXCEPTIONS.COUNT LOOP SQL%BULK_EXCEPTIONS(i).error_index||' caused error '||i|| ': '||SQL%BULK_EXCEPTIONS(i).ERROR_CODE||' '|| SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)); In this script, you declare a user-defined exception and associate an Oracle error number with it loop against the ZIPCODE table, and then you modify them so that they cause exceptions in the FORALL statement. For example, the first record of the zip_tab collection is set to NULL. causes a constraint violation because the ZIP column in the MY_ZIPCODE table has a NOT NULL Then, you add the SAVE EXCEPTIONS clause to the FORALL statement and an exception-handling section to the PL/SQL block. statement in the exception-handling section. This statement is added so that records that are inserted successfully by the FORALL statement are committed when control of the execution is C) Modify the previous version of the script as follows: Do not modify records selected from the ZIPCODE table so that no exceptions are raised. TYPE string_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER; -- statement zip_tab(v_counter) := rec.zip; state_tab(v_counter) := rec.state; cr_by_tab(v_counter) := rec.created_by; cr_date_tab(v_counter) := rec.created_date; mod_by_tab(v_counter) := rec.modified_by; mod_date_tab(v_counter) := rec.modified_date; FORALL i IN INDICES OF zip_tab SAVE EXCEPTIONS -- Check how many records were added to MY_ZIPCODE table (v_total||' records were added to MY_ZIPCODE table'); SQL%BULK_EXCEPTIONS.COUNT LOOP SQL%BULK_EXCEPTIONS(i).error_index||' caused error '||i|| ': '||SQL%BULK_EXCEPTIONS(i).ERROR_CODE||' '|| SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)); 2 records were added to MY_ZIPCODE table D) Modify the second version of the script, ch18_1b.sql, as follows: Insert records that cause excep- ANSWER: The MY_ZIPCODE_EXC table may be created as follows: cause exceptions in the FORALL statements can be inserted into this table. TYPE string_type IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER; -- statement zip_tab(v_counter) := rec.zip; state_tab(v_counter) := rec.state; cr_by_tab(v_counter) := rec.created_by; cr_date_tab(v_counter) := rec.created_date; mod_by_tab(v_counter) := rec.modified_by; mod_date_tab(v_counter) := rec.modified_date; FORALL i in 1..zip_tab.COUNT SAVE EXCEPTIONS -- Check how many records were added to MY_ZIPCODE table (v_total||' records were added to MY_ZIPCODE table'); -- Populate V_EXC_IND_TAB collection to be used in the VALUES SQL%BULK_EXCEPTIONS.COUNT LOOP exc_ind_tab(i) := SQL%BULK_EXCEPTIONS(i).error_index; -- Insert records that caused exceptions in the MY_ZIPCODE_EXC In this version of the script, you modify the exception-handling section so that records causing exceptions in the FORALL statement are inserted into the MY_ZIPCODE_EXC table created earlier. First, you populate the collection EXC_IND_TAB with subscripts of records that caused exceptions in the FORALL statement. After the script is executed, the MY_ZIPCODE_EXC table contains 1.1.1 Use PL/SQL Anonymous Blocks 1.2.2 Use the DBMS_OUTPUT.PUT_LINE Statement LAB 2.1 PL/SQL Programming Fundamentals 2.1.1 Make Use of PL/SQL Language Components 2.1.2 Make Use of PL/SQL Variables 2.1.4 Make Use of Identifiers in PL/SQL LAB 3.1 Making Use of DML in PL/SQL 3.1.2 Use DML in a PL/SQL Block 3.1.3 Make Use of a Sequence in a PL/SQL Block 3.2.1 Make Use of COMMIT, ROLLBACK, and SAVEPOINT in a PL/SQL Block LAB 4.1 IF Statements 4.1.1 Use the IF-THEN Statement 4.1.2 Use the IF-THEN-ELSE Statement LAB 4.2 ELSIF Statements 4.2.1 Use the ELSIF Statement LAB 4.3 Nested IF Statements 4.3.1 Use Nested IF Statements LAB 5.1 CASE Statements 5.1.1 Use the CASE Statement 5.1.2 Use the Searched CASE Statement LAB 7.1 The CONTINUE Statement 7.1.1 Use the CONTINUE Statement 11.1.1 Make Use of Record Types 11.2.1 Use a Cursor FOR Loop 13.2.1 Use Row and Statement Triggers LAB 15.1 PL/SQL Tables 15.1.2 Use Nested Tables LAB 16.1 Record Types 16.1.1 Use Table-Based and Cursor-Based Records LAB 16.3 Collections of Records 16.3.1 Use Collections of Records LAB 17.1 EXECUTE IMMEDIATE Statements 17.1.1 Use the EXECUTE IMMEDIATE Statement LAB 17.2 OPEN-FOR, FETCH, and CLOSE Statements 17.2.1 Use OPEN-FOR, FETCH, and CLOSE Statements LAB 18.1 The FORALL Statement 18.1.1 Use the FORALL Statement 18.2.1 Use the BULK COLLECT Statement 20.1.3 Invoke Functions in SQL Statements LAB 21.2 Cursor Variables 21.2.1 Make Use of Cursor Variables 23.1.2 Use Object Types with Collections LAB 24.1 Making Use of Oracle Supplied Packages to Profile PL/SQL, Access Files, and Schedule Jobs