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

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

Chapter 17. Native Dynamic SQL


In this chapter, you will learn about

Image EXECUTE IMMEDIATE Statements

Image OPEN-FOR, FETCH, and CLOSE Statements


Generally, PL/SQL applications perform a specific task and manipulate a static set of tables. For example, a stored procedure might accept a student ID and return the student’s first and last names. In such a procedure, a SELECT statement is known in advance and is compiled as part of the procedure. Such SELECT statements are called static because they do not change from execution to execution.

Now, consider a different type of PL/SQL application where SQL statements are built on the fly, based on a set of parameters specified at run time. For example, an application might need to build various reports based on SQL statements where the table and column names are not known in advance or the sorting and grouping of data are specified by the user requesting a report. Similarly, another application might need to create or drop tables or other database objects based on the actions specified by a user at run time. Because these SQL statements are generated on the fly and might change from time to time, they are called dynamic.

PL/SQL has a feature called native dynamic SQL (“dynamic SQL” for short) that helps you build applications similar to those described previously. The use of dynamic SQL makes such applications flexible, versatile, and concise because it eliminates the need for complicated programming approaches. Native dynamic SQL is more convenient to use than the Oracle-supplied package DBMS_SQL, which has similar functionality. In this chapter you will learn how to create and use dynamic SQL.

Lab 17.1: EXECUTE IMMEDIATE Statements


After this lab, you will be able to

Image Use the EXECUTE IMMEDIATE Statement

Image Avoid ORA Errors When Using EXECUTE IMMEDIATE Statements


Generally, dynamic SQL statements are built by your program and stored as character strings based on the parameters specified at run time. These strings must contain valid SQL statements or PL/SQL code. Consider the following example of a dynamic SQL statement:

'SELECT first_name, last_name FROM student
WHERE student_id = :student_id'

This SELECT statement returns a student’s first and last names for a given student ID. The value of the student ID is not known in advance and is specified with the help of a bind argument, :student_id. The bind argument acts as a placeholder for an undeclared identifier, and its name must be prefixed by a colon. As a result, PL/SQL does not differentiate between the following statements:

'SELECT first_name, last_name
FROM student WHERE
student_id = :student_id'
'SELECT first_name, last_name
FROM student WHERE student_id = :id'

To process dynamic SQL statements, you use EXECUTE IMMEDIATE or OPEN-FOR, FETCH, and CLOSE statements. EXECUTE IMMEDIATE is used for a single-row SELECT statement, all DML statements, and DDL statements, whereas OPEN-FOR, FETCH, and CLOSE statements are used for multirow SELECT statements and reference cursors.


Did You Know?

To improve performance of dynamic SQL statements, you can also use BULK EXECUTE IMMEDIATE, BULK FETCH, FORALL, and COLLECT INTO statements. However, these statements are outside the scope of this book and are not covered here. You can find detailed explanations and examples of their usage in Oracle’s online help.


Using the EXECUTE IMMEDIATE Statement

The EXECUTE IMMEDIATE statement parses a dynamic statement or a PL/SQL block for immediate execution and has the structure shown here (the reserved words and phrases surrounded by brackets are optional):

EXECUTE IMMEDIATE dynamic_SQL_string
[INTO defined_variable1, defined_variable2, ...]
[USING [IN | OUT | IN OUT] bind_argument1, bind_argument2,
...][{RETURNING | RETURN} field1, field2,
... INTO bind_argument1, bind_argument2, ...]

The dynamic_SQL_string is a string that contains a valid SQL statement or a PL/SQL block. The INTO clause contains the list of predefined variables that hold values returned by the SELECT statement. This clause is used when a dynamic SQL statement returns a single row, similar to a static SELECT INTO statement. Next, the USING clause contains a list of bind arguments whose values are passed to the dynamic SQL statement or PL/SQL block. The IN, OUT, and IN OUT options are modes for bind arguments. If no mode is specified, all bind arguments listed in the USING clause default to the IN mode. Finally, the RETURNING INTO or RETURN clause contains a list of bind arguments that store values returned by the dynamic SQL statement or PL/SQL block. Similar to the USING clause, the RETURNING INTO clause may also contain various argument modes; however, if no mode is specified, all bind arguments default to the OUT mode.


Did You Know?

When an EXECUTE IMMEDIATE statement contains both USING and RETURNING INTO clauses, the USING clause may specify only IN arguments.


The following script contains several examples of dynamic SQL.

For Example

DECLARE
sql_stmt VARCHAR2(100);
plsql_block VARCHAR2(300);
v_zip VARCHAR2(5) := '11106';
v_total_students NUMBER;
v_new_zip VARCHAR2(5);
v_student_id NUMBER := 151;
BEGIN
-- Create table MY_STUDENT
sql_stmt := 'CREATE TABLE my_student '||
'AS SELECT * FROM student WHERE zip = '||v_zip;
EXECUTE IMMEDIATE sql_stmt;

-- Select total number of records from MY_STUDENT table
-- and display results on the screen
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM my_student'
INTO v_total_students;
DBMS_OUTPUT.PUT_LINE ('Students added: '||v_total_students);

-- Select current date and display it on the screen
plsql_block := 'DECLARE ' ||
' v_date DATE; ' ||
'BEGIN ' ||
' SELECT SYSDATE INTO v_date FROM DUAL; ' ||
' DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_date,
''DD-MON-YYYY''));' ||
'END;';
EXECUTE IMMEDIATE plsql_block;

-- Update record in MY_STUDENT table
sql_stmt := 'UPDATE my_student SET zip = 11105 WHERE student_id =
:1 '||
'RETURNING zip INTO :2';
EXECUTE IMMEDIATE sql_stmt USING v_student_id RETURNING INTO
v_new_zip;
DBMS_OUTPUT.PUT_LINE ('New zip code: '||v_new_zip);
END;

First, this script creates the table MY_STUDENT and populates it with records for a specified value of the ZIP code. Here, the variable v_zip is concatenated with the CREATE statement instead of being passed in as a bind argument. This point is illustrated in the next example.

Second, the script selects the total number of students added to the MY_STUDENT table and displays it on the screen. The INTO option is used with the EXECUTE IMMEDIATE statement because the SELECT statement returns a single row.

Third, the script includes a simple PL/SQL block that selects the current date and displays it on the screen. Because the PL/SQL block does not contain any bind arguments, the EXECUTE IMMEDIATE statement is used in its simplest form.

Finally, the script updates the MY_STUDENT table for a given student ID and returns a new ZIP code value via the RETURNING statement.

Thus, this EXECUTE IMMEDIATE command contains both USING and RETURNING INTO options. The USING option allows you to pass a value for the student ID to the UPDATE statement at run time, and the RETURNING INTO option allows you to pass a new ZIP code value from the UPDATE statement into the program.

When run, this example produces the following output:

Students added: 4
22-JUN-2003
New zip code: 11105

PL/SQL procedure successfully completed.

How to Avoid Common ORA Errors When Using EXECUTE IMMEDIATE

Next, consider the simplified yet incorrect version of the preceding example. Changes are shown in bold.

For Example

DECLARE
sql_stmt VARCHAR2(100);
v_zip VARCHAR2(5) := '11106';
v_total_students NUMBER;
BEGIN
-- Drop table MY_STUDENT
EXECUTE IMMEDIATE 'DROP TABLE my_student';
-- Create table MY_STUDENT
sql_stmt := 'CREATE TABLE my_student '||
'AS SELECT * FROM student '||
'WHERE zip = :zip';
EXECUTE IMMEDIATE sql_stmt USING v_zip;

-- Select total number of records from MY_STUDENT table
-- and display results on the screen
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM my_student'
INTO v_total_students;

DBMS_OUTPUT.PUT_LINE ('Students added: '|| v_total_students);
END;

First, this script drops the table MY_STUDENT created in the previous version of the example. Next, it recreates the MY_STUDENT table, but in this case uses a bind argument to pass a value for the ZIP code to the CREATE statement at run time.

When run, this example produces the following error:

DECLARE
*
ERROR at line 1:
ORA-01027: bind variables not allowed for data definition operations
ORA-06512: at line 12


By the Way

A CREATE TABLE statement is a data definition statement. As a result, it cannot accept any bind arguments.


Next, consider another simplified version of the same example that also causes a syntax error. In this version, the table name is passed as a bind argument to the SELECT statement. Changes are shown in bold.

For Example

DECLARE
sql_stmt VARCHAR2(100);
v_zip VARCHAR2(5) := '11106';
v_total_students NUMBER;
BEGIN
-- Create table MY_STUDENT
sql_stmt := 'CREATE TABLE my_student '||
'AS SELECT * FROM student '|| 'WHERE zip ='|| v_zip;
EXECUTE IMMEDIATE sql_stmt;
-- Select total number of records from MY_STUDENT table
-- and display results on the screen
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :my_table'
INTO v_total_students
USING 'my_student';
DBMS_OUTPUT.PUT_LINE ('Students added: '|| v_total_students);
END;

When run, this example causes the following error:

DECLARE
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 13

This example causes an error because you cannot pass names of schema objects to dynamic SQL statements as bind arguments. To provide a table name at the run time, you need to concatenate it with the SELECT statement, as shown here:

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 by the semicolon (;). Similarly, a dynamic PL/SQL block should not be terminated by the forward slash (/). Consider a different version of the same example where the SELECT statement is terminated by the semicolon. Changes are shown in bold. Note that if you have created the MY_STUDENT table based on the earlier corrected version of the script, you need to drop it prior to running the following script. Otherwise, the error message generated by the example will differ from the error message shown here.

For Example

DECLARE
sql_stmt VARCHAR2(100);
v_zip VARCHAR2(5) := '11106';
v_total_students NUMBER;
BEGIN
-- Create table MY_STUDENT
sql_stmt := 'CREATE TABLE my_student '||
'AS SELECT * FROM student '|| 'WHERE zip = '||v_zip;
EXECUTE IMMEDIATE sql_stmt;

-- Select total number of records from MY_STUDENT table
-- and display results on the screen
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM my_student;'
INTO v_total_students;
DBMS_OUTPUT.PUT_LINE ('Students added: '|| v_total_students);
END;

When run, this example produces the following error:

DECLARE
*
ERROR at line 1:
ORA-00903: invalid character
ORA-06512: at line 13

The semicolon added to the SELECT statement is treated as an invalid character when the statement is created dynamically. A somewhat similar error is generated when a PL/SQL block is terminated by a forward slash, as demonstrated in the next example. Changes are shown in bold.

For Example

DECLARE
plsql_block VARCHAR2(300);
BEGIN
-- Select current date and display it on the screen
plsql_block := 'DECLARE ' ||
' v_date DATE; ' ||
'BEGIN ' ||
' SELECT SYSDATE INTO v_date FROM DUAL; ' ||
' DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_date,
''DD-MON-YYYY''));' ||
'END;' ||
'/';
EXECUTE IMMEDIATE plsql_block;
END;

When run, this example produces the following error:

DECLARE
*
ERROR at line 1:
ORA-06550: line 1, column 133:
PLS-00103: Encountered the symbol "/" The symbol "/" was ignored.
ORA-06512: at line 12

Passing NULLs

In some cases you may need to pass a NULL value to a dynamic SQL statement as a value for a bind argument. For example, you need to update the COURSE table so that the PREREQUISITE column is set to NULL. You can accomplish this with the following dynamic SQL and theEXECUTE IMMEDIATE statement.

For Example

DECLARE
sql_stmt VARCHAR2(100);
BEGIN
sql_stmt := 'UPDATE course'||
' SET prerequisite = :some_value';
EXECUTE IMMEDIATE sql_stmt
USING NULL;
END;

When run, this script causes the following error:

USING NULL;
*
ERROR at line 7:
ORA-06550: line 7, column 10:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 6, column 4:
PL/SQL: Statement ignored

This error is generated because the literal NULL in the USING clause is not recognized as one of the SQL types. To pass a NULL value to the dynamic SQL statement, this example should be modified as follows (changes are shown in bold):

For Example

DECLARE
sql_stmt VARCHAR2(100);
v_null VARCHAR2(1);
BEGIN
sql_stmt := 'UPDATE course'||
' SET prerequisite = :some_value';
EXECUTE IMMEDIATE sql_stmt
USING v_null;
END;

Putting It All Together

To correct the script, you add an initialized variable v_null and replace the literal NULL in the USING clause with this variable. Because the variable v_null has not been initialized, its value remains NULL, and it is passed to the dynamic UPDATE statement at run time. As a result, this version of the script completes without any errors.

For Example ch17_1.sql, version 1.0

SET SERVEROUTPUT ON
DECLARE
sql_stmt VARCHAR2(200);
v_student_id NUMBER := &sv_student_id;
v_first_name VARCHAR2(25);
v_last_name VARCHAR2(25);
BEGIN
sql_stmt := 'SELECT first_name, last_name'||
' FROM student' ||
' WHERE student_id = :1';
EXECUTE IMMEDIATE sql_stmt
INTO v_first_name, v_last_name
USING v_student_id;

DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name);
DBMS_OUTPUT.PUT_LINE ('Last Name: '||v_last_name);
END;

In the proceeding example, the declaration section of the script includes a declaration of the string that contains the dynamic SQL statement as well as declarations of three variables to hold the student’s ID, first name, and last name, respectively. The 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 run time. The dynamic SQL statement is executed via the EXECUTE IMMEDIATE statement with two options, INTO and USING. The INTO clause contains two variables, v_first_name and v_last_name. These variables contain results returned by the SELECT statement. The USING clause contains the variable v_student_id, which is used to pass a value to the SELECT statement at run time. Finally, twoDBMS_OUTPUT.PUT_LINE statements are used to display the results of the SELECT statement on the screen.

When run, this script will prompt the user for a value. If, for example, 105 is entered, it produces the following output:

Enter value for sv_student_id: 105

old 3: v_student_id NUMBER := &sv_student_id;
new 3: v_student_id NUMBER := 105;
First Name: Angel
Last Name: Moskowitz

PL/SQL procedure successfully completed

In the next example, the script is modified so that the student’s address (street, city, state, and ZIP code) are displayed on the screen as well.

For Example ch17_1.sql, version 2.0

SET SERVEROUTPUT ON
DECLARE
sql_stmt VARCHAR2(200);
v_student_id NUMBER := &sv_student_id;
v_first_name VARCHAR2(25);
v_last_name VARCHAR2(25);
v_street VARCHAR2(50);
v_city VARCHAR2(25);
v_state VARCHAR2(2);
v_zip VARCHAR2(5);
BEGIN
sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'||
' ,b.city, b.state, b.zip' ||
' FROM student a, zipcode b' ||
' WHERE a.zip = b.zip' ||
' AND student_id = :1';
EXECUTE IMMEDIATE sql_stmt
INTO v_first_name, v_last_name, v_street, v_city, v_state, v_zip
USING v_student_id;
DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name);
DBMS_OUTPUT.PUT_LINE ('Last Name: '||v_last_name);
DBMS_OUTPUT.PUT_LINE ('Street: '||v_street);
DBMS_OUTPUT.PUT_LINE ('City: '||v_city);
DBMS_OUTPUT.PUT_LINE ('State: '||v_state);
DBMS_OUTPUT.PUT_LINE ('Zip Code: '||v_zip);
END;

In this script, four new variables are declared: v_street, v_city, v_state, and v_zip. Next, the dynamic SQL statement is modified so that it can return the student’s address. In turn, the INTO clause is modified by adding the new variables to it. Next,DBMS_OUTPUT.PUT_LINE statements are added to display the student’s address on the screen.

When run, this script produces the following output:

Enter value for sv_student_id: 105
old 3: v_student_id NUMBER := &sv_student_id;
new 3: v_student_id NUMBER := 105;
First Name: Angel
Last Name: Moskowitz
Street: 320 John St.
City: Ft. Lee
State: NJ
Zip Code: 07024

PL/SQL procedure successfully completed.

Note that the order of variables listed in the INTO clause must follow the order of the columns listed in the SELECT statement. In other words, if the INTO clause listed variables so that v_zip and v_state were misplaced while the SELECT statement remains unchanged, the scripts would generate an error. The following example demonstrates this case.

For Example ch17_1.sql, version 3.0

SET SERVEROUTPUT ON
DECLARE
sql_stmt VARCHAR2(200);
v_student_id NUMBER := &sv_student_id;
v_first_name VARCHAR2(25);
v_last_name VARCHAR2(25);
v_street VARCHAR2(50);
v_city VARCHAR2(25);
v_state VARCHAR2(2);
v_zip VARCHAR2(5);
BEGIN
sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'||
' ,b.city,b.state, b.zip' ||
' FROM studenta, zipcode b' ||
' WHERE a.zip = b.zip' ||
' AND student_id = :1';
EXECUTE IMMEDIATE sql_stmt
-- variables v_state and v_zip are misplaced
INTO v_first_name, v_last_name, v_street, v_city, v_zip, v_state
USING v_student_id;
DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name);
DBMS_OUTPUT.PUT_LINE ('Last Name: '||v_last_name);
DBMS_OUTPUT.PUT_LINE ('Street: '||v_street);
DBMS_OUTPUT.PUT_LINE ('City: '||v_city);
DBMS_OUTPUT.PUT_LINE ('State: '||v_state);
DBMS_OUTPUT.PUT_LINE ('Zip Code: '||v_zip);
END;

When run, this script produces the following error:

Enter value for sv_student_id: 105

old 3: v_student_id NUMBER := &sv_student_id;
new 3: v_student_id NUMBER := 105;

DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 16

This error is generated because the variable v_state can hold up to two characters. However, in this example, it is trying to store a ZIP code that contains five characters.

Next, this script is modified so that the SELECT statement can be run against either the STUDENT or INSTRUCTOR table. In other words, the user can specify the table name to be used in the SELECT statement at run time. The changes for this version are highlighted in bold.

For Example ch17_1.sql, version 4.0

SET SERVEROUTPUT ON
DECLARE
sql_stmt VARCHAR2(200);
v_table_name VARCHAR2(20) := '&sv_table_name';
v_id NUMBER := &sv_id;
v_first_name VARCHAR2(25);
v_last_name VARCHAR2(25);
v_street VARCHAR2(50);
v_city VARCHAR2(25);
v_state VARCHAR2(2);
v_zip VARCHAR2(5);
BEGIN
sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'||
,b.city, b.state, b.zip' ||
' FROM '||v_table_name||' a, zipcode b' ||
' WHERE a.zip = b.zip' ||
' AND '||v_table_name||'_id = :1';
EXECUTE IMMEDIATE sql_stmt
-INTO v_first_name, v_last_name, v_street, v_city, v_state, v_zip
USING v_id;

DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name);
DBMS_OUTPUT.PUT_LINE ('Last Name: '||v_last_name);
DBMS_OUTPUT.PUT_LINE ('Street: '||v_street);
DBMS_OUTPUT.PUT_LINE ('City: '||v_city);
DBMS_OUTPUT.PUT_LINE ('State: '||v_state);
DBMS_OUTPUT.PUT_LINE ('Zip Code: '||v_zip);
END;

The declaration portion of the script contains a new variable, v_table_name that holds the name of a table provided at run time by the user. In addition, the variable v_student_id has been replaced by the variable v_id since it is not known in advance which table, STUDENT orINSTRUCTOR, will be accessed at run time.

The executable portion of the script contains a modified dynamic SQL statement. Notice that the statement does not contain any information specific to the STUDENT or INSTRUCTOR tables. In other words, the dynamic SQL statement used by the previous version (ch17_1.sql version 3.0)

sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'||
' ,b.city, b.state, b.zip' ||
' FROM student a, zipcode b' ||
' WHERE a.zip = b.zip' ||
' AND student_id = :1';

has been replaced by

sql_stmt := 'SELECT a.first_name, a.last_name, a.street_address'||
' ,b.city, b.state, b.zip' ||
' FROM '||v_table_name||' a, zipcode b' ||
' WHERE a.zip = b.zip' ||
' AND '||v_table_name||'_id = :1';

The table name (STUDENT) has been replaced by the variable v_table_name in the FROM and WHERE clauses.


Did You Know?

In the last two versions of the script, you have used generic table aliases, a and b, instead of s and z or i and z, which are more descriptive. This technique allows you to create generic SQL statements that are not based on a specific table because you do not always know which table is appropriate in advance.


This version of the script produces the following output. The first run is made against the STUDENT table.

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';
Enter value for sv_id: 105
old 4: v_id NUMBER := &sv_id;
new 4: v_id NUMBER := 105;
First Name: Angel
Last Name: Moskowitz
Street: 320 John St.
City: Ft. Lee
State: NJ
Zip Code: 07024
PL/SQL procedure successfully completed.

The second run is against the INSTRUCTOR table:

Enter value for sv_table_name: instructor
old 3: v_table_name VARCHAR2(20) := '&sv_table_name';
new 3: v_table_name VARCHAR2(20) := 'instructor';
Enter value for sv_id: 105
old 4: v_id NUMBER := &sv_id;
new 4: v_id NUMBER := 105;
First Name: Anita
Last Name: Morris
Street: 34 Maiden Lane
City: New York
State: NY
Zip Code: 10015
PL/SQL procedure successfully completed.

Lab 17.2: OPEN-FOR, FETCH, and CLOSE Statements


After this lab, you will be able to

Image Use OPEN-FOR statements

Image Use FETCH statements

Image Use CLOSE statements


The OPEN-FOR, FETCH, and CLOSE statements are used for multirow queries or cursors. This concept is very similar to the static cursor processing that you encountered in Chapter 11. Just as in the case of static cursors, first you associate a cursor variable with a query. Next, you open the cursor variable so that it points to the first row of the result set. Next, you fetch one row at a time from the result set. Finally, when all rows have been processed, you close the cursor (cursor variable).

Opening Cursor

In the case of a dynamic SQL, the OPEN-FOR statement has an optional USING clause that allows you to pass values to the bind arguments at run time. The general syntax for an OPEN-FOR statement is as follows (the reserved words and phrases surrounded by brackets are optional):

OPEN cursor_variable FOR dynamic_SQL_string
[USING bind_argument1, bind_argument2, ...]

The cursor_variable is a variable of a weak REF CURSOR type, and the dynamic_SQL_string is a string that contains a multirow query.

For Example

DECLARE
TYPE student_cur_type IS REF CURSOR;
student_cur student_cur_type;
v_zip VARCHAR2(5) := '&sv_zip';
v_first_name VARCHAR2(25);
v_last_name VARCHAR2(25);
BEGIN
OPEN student_cur FOR
'SELECT first_name, last_name FROM student '||'WHERE zip = :1'
USING v_zip;
...

This code fragment first defines a weak cursor type, student_cur_type. Next, it defines a cursor variable, student_cur, based on the REF CURSOR type specified in the previous step. At run time, 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.

Fetching from a Cursor

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. If a loop is being processed and there are no more rows to fetch, the EXIT WHEN statement evaluates to TRUE, and control of the execution passes outside the cursor loop. The general syntax for a FETCH statement is as follows:

FETCH cursor_variable
INTO defined_variable1, defined_variable2, ...
EXIT WHEN cursor_variable%NOTFOUND;

Continuing the previous example, you fetch the student’s first and last names into variables specified in the declaration section of the PL/SQL block. Next, you evaluate if there are more records to process via an EXIT WHEN statement. As long as there are more records to process, the student’s first and last names are displayed on the screen. Once the last row is fetched, the cursor loop terminates. The changes necessary for these steps are shown in bold.

For Example

DECLARE
TYPE student_cur_type IS REF CURSOR;
student_cur student_cur_type;
v_zip VARCHAR2(5) := '&sv_zip';
v_first_name VARCHAR2(25);
v_last_name VARCHAR2(25);
BEGIN
OPEN student_cur FOR
'SELECT first_name, last_name FROM student '||'WHERE zip = :1'
USING v_zip;

LOOP
FETCH student_cur INTO v_first_name, v_last_name;
EXIT WHEN student_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name);
DBMS_OUTPUT.PUT_LINE ('Last Name: '||v_last_name);
END LOOP;
...

The number of variables listed in the INTO clause must correspond to the number of columns returned by the cursor. Furthermore, the variables in the INTO clause must be type compatible with the cursor columns.

Closing a Cursor

The CLOSE statement disassociates the cursor variable with the multirow query. As a result, after the CLOSE statement executes, the result set becomes undefined. The general syntax for a CLOSE statement is as follows:

CLOSE cursor_variable;

Now consider the complete version of the example shown previously. Changes are shown in bold.

For Example

DECLARE
TYPE student_cur_type IS REF CURSOR;
student_cur student_cur_type;
v_zip VARCHAR2(5) := '&sv_zip';
v_first_name VARCHAR2(25);
v_last_name VARCHAR2(25);
BEGIN
OPEN student_cur FOR
'SELECT first_name, last_name FROM student '||'WHERE zip = :1'
USING v_zip;
LOOP
FETCH student_cur INTO v_first_name, v_last_name;
EXIT WHEN student_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('First Name: '||v_first_name);
DBMS_OUTPUT.PUT_LINE ('Last Name: '||v_last_name);
END LOOP;
CLOSE student_cur;
EXCEPTION
WHEN OTHERS THEN
IF student_cur%ISOPEN THEN
CLOSE student_cur;
END IF;
DBMS_OUTPUT.PUT_LINE ('ERROR: '|| SUBSTR(SQLERRM, 1, 200));
END;

The IF statement in the exception-handling section evaluates to TRUE if an exception is encountered before the cursor processing is completed. In such a case, it is considered good practice to check whether a cursor is still open and close it, if necessary, so that all resources associated with the cursor will be freed before the program terminates.

When run, this example produces the following output:

Enter value for sv_zip: 11236
old 5: v_zip VARCHAR2(5) := '&sv_zip';
new 5: v_zip VARCHAR2(5) := '11236';
First Name: Derrick
Last Name: Baltazar
First Name: Michael
Last Name: Lefbowitz
First Name: Bridget
Last Name: Hagel

PL/SQL procedure successfully completed.

In the following example, pay close attention to the use of spaces.

For Example ch17_2.sql, version 1.0

SET SERVEROUTPUT ON
DECLARE
TYPE zip_cur_type IS REF CURSOR;
zip_cur zip_cur_type;
sql_stmt VARCHAR2(500);
v_zip VARCHAR2(5);
v_total NUMBER;
v_count NUMBER;
BEGIN
sql_stmt := 'SELECT zip, COUNT(*) total'||
' FROM student ' ||
'GROUP BY zip';
v_count := 0;
OPEN zip_cur FOR sql_stmt;
LOOP
FETCH zip_cur INTO v_zip, v_total;
EXIT WHEN zip_cur%NOTFOUND;
-- Limit the number of lines printed on the
-- screen to 10
v_count := v_count + 1;
IF v_count <= 10 THEN
DBMS_OUTPUT.PUT_LINE ('Zip code: '||v_zip||
' Total: '||v_total);
END IF;
END LOOP;
CLOSE zip_cur;
EXCEPTION
WHEN OTHERS THEN
IF zip_cur%ISOPEN THEN
CLOSE zip_cur;
END IF;
DBMS_OUTPUT.PUT_LINE ('ERROR: '|| SUBSTR(SQLERRM, 1, 200));
END;

Consider the use of spaces in the SQL statements generated dynamically. In the preceding script, the string that holds the dynamic SQL statement consists of three strings concatenated together, where each string is written on a separate line.

sql_stmt := 'SELECT zip, COUNT(*) total'||
' FROM student ' ||
'GROUP BY zip';

This format of the dynamic SELECT statement is very similar to the format of any static SELECT statement that you have seen throughout this book. However, there is a subtle difference. In one instance, extra spaces have been added for formatting reasons. For example, the FROMkeyword is prefixed by two spaces so that it is aligned with the SELECT keyword. Yet, in another instance, a space has been added to separate out a reserved phrase. In this case, a space has been added after the STUDENT table to separate out the GROUP BY clause. This step is necessary because once the strings are concatenated, the resulting SELECT statement looks as follows:

SELECT zip, COUNT(*) total FROM student GROUP BY zip

If no space is added after the STUDENT table, the resulting SELECT statement

SELECT zip, COUNT(*) total FROM studentGROUP BY zip

causes the following error:

ERROR: ORA-00933: SQL command not properly ended

PL/SQL procedure successfully completed.

In the declaration portion of the example script, a weak cursor type is defined as zip_cur_type, and a cursor variable zip_cur of the zip_cur_type type is also defined. Next, a string variable to hold a dynamic SQL statement is defined as well as two variables v_zip andv_total, which hold data returned by the cursor. Finally, a counter variable is defined so that only the first 10 rows returned by the cursor are displayed on the screen.

In the executable portion of the script, a dynamic SQL statement is generated, which is then associated with the cursor variable, zip_cur. The cursor is opened. Then, for each row returned by the cursor, the values of a ZIP code and the total number of students living in that ZIP code area are populated into the variables v_zip and v_total, respectively. Next, the script checks whether there are more rows to fetch from the cursor. If there are more rows to process, the value of the counter variable is incremented by 1. As long as the value of the counter is less than or equal to 10, the row returned by the cursor is displayed on the screen. If there are no more rows to fetch, the cursor is closed.

The exception-handling section of the script checks whether the cursor is open. If it is, the script closes the cursor and displays an error message on the screen before terminating.

When run, the script should produce output similar to that shown here:

Zip code: 01247 Total: 1
Zip code: 02124 Total: 1
Zip code: 02155 Total: 1
Zip code: 02189 Total: 1
Zip code: 02563 Total: 1
Zip code: 06483 Total: 1
Zip code: 06605 Total: 1
Zip code: 06798 Total: 1
Zip code: 06820 Total: 3
Zip code: 06830 Total: 3
PL/SQL procedure successfully completed.

The script ch17_2.sql, version 1.0, from the previous example is now modified so that the SELECT statement can be run against either the STUDENT or INSTRUCTOR table. In other words, the user can specify the table name used in the SELECT statement at run time.

For Example ch17_2.sql, version 2.0

SET SERVEROUTPUT ON
DECLARE
TYPE zip_cur_type IS REF CURSOR;
zip_cur zip_cur_type;
v_table_name VARCHAR2(20) := '&sv_table_name';
sql_stmt VARCHAR2(500);
v_zip VARCHAR2(5);
v_total NUMBER;
v_count NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Totals from '||v_table_name||
' table');

sql_stmt := 'SELECT zip, COUNT(*) total'||
' FROM '||v_table_name||' '||
'GROUP BY zip';

v_count := 0;
OPEN zip_cur FOR sql_stmt;
LOOP
FETCH zip_cur INTO v_zip, v_total;
EXIT WHEN zip_cur%NOTFOUND;
-- Limit the number of lines printed on the
-- screen to 10
v_count := v_count + 1;
IF v_count <= 10 THEN
DBMS_OUTPUT.PUT_LINE ('Zip code: '||v_zip||
' Total: '||v_total);
END IF;
END LOOP;
CLOSE zip_cur;
EXCEPTION
WHEN OTHERS THEN
IF zip_cur%ISOPEN THEN
CLOSE zip_cur;
END IF;
DBMS_OUTPUT.PUT_LINE ('ERROR: '|| SUBSTR(SQLERRM, 1, 200));
END;

In this version of the script, the variable v_table_name has been added to hold the name of a table provided at the run time. A DBMS_OUTPUT.PUT_LINE has been added to display a message indicating the table from which the total numbers are coming. The dynamic SQL statement was also modified as follows:

sql_stmt := 'SELECT zip, COUNT(*) total'||
' FROM '||v_table_name||' '||
'GROUP BY zip';

The variable v_table_name has been inserted in place of the actual table name (STUDENT). Note that a space is concatenated to the variable v_table_name, so that the SELECT statement does not cause any errors.

When run, this script produces the following output. 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
Zip code: 01247 Total: 1
Zip code: 02124 Total: 1
Zip code: 02155 Total: 1
Zip code: 02189 Total: 1
Zip code: 02563 Total: 1
Zip code: 06483 Total: 1
Zip code: 06605 Total: 1
Zip code: 06798 Total: 1
Zip code: 06820 Total: 3
Zip code: 06830 Total: 3

PL/SQL procedure successfully completed.

The second run is based on the INSTRUCTOR table.

Enter value for sv_table_name: instructor
old 5: v_table_name VARCHAR2(20) := '&sv_table_name';
new 5: v_table_name VARCHAR2(20) := 'instructor';
Totals from instructor table
Zip code: 10005 Total: 1
Zip code: 10015 Total: 3
Zip code: 10025 Total: 4
Zip code: 10035 Total: 1

PL/SQL procedure successfully completed.

So far, you have seen that values returned by the dynamic SQL statements are stored in individual variables such as v_last_name or v_first_name. In such cases, you list variables in the order of the corresponding columns returned by the SELECT statement. This approach becomes somewhat cumbersome when a dynamic SQL statement returns more than a few columns. As a result, PL/SQL allows you to store values returned by the dynamic SELECT statements in the variables of the record type.

Consider the modified version of the script used in this lab. In this version, instead of creating separate variables, a user-defined record is created. This record is then used to fetch data from the cursor and display it on the screen. Changes are shown in bold.

For Example ch17_2.sql, version 3.0

SET SERVEROUTPUT ON
DECLARE
TYPE zip_cur_type IS REF CURSOR;
zip_cur zip_cur_type;

TYPE zip_rec_type IS RECORD
(zip VARCHAR2(5),
total NUMBER);
zip_rec zip_rec_type;

v_table_name VARCHAR2(20) := '&sv_table_name';
sql_stmt VARCHAR2(500);
v_count NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE ('Totals from '||v_table_name||
' table');
sql_stmt := 'SELECT zip, COUNT(*) total'||
' FROM '||v_table_name||' '||
'GROUP BY zip';
v_count := 0;
OPEN zip_cur FOR sql_stmt;
LOOP
FETCH zip_cur INTO zip_rec;
EXIT WHEN zip_cur%NOTFOUND;

-- Limit the number of lines printed on the
-- screen to 10
v_count := v_count + 1;
IF v_count <= 10 THEN
DBMS_OUTPUT.PUT_LINE ('Zip code: '||zip_rec.zip||
' Total: '||zip_rec.total);
END IF;
END LOOP;
CLOSE zip_cur;
EXCEPTION
WHEN OTHERS THEN
IF zip_cur%ISOPEN THEN
CLOSE zip_cur;
END IF;
DBMS_OUTPUT.PUT_LINE ('ERROR: '|| SUBSTR(SQLERRM, 1, 200));
END;

When version 3 of this script is run, it produces the following results for the STUDENT table:

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
Zip code: 01247 Total: 1
Zip code: 02124 Total: 1
Zip code: 02155 Total: 1
Zip code: 02189 Total: 1
Zip code: 02563 Total: 1
Zip code: 06483 Total: 1
Zip code: 06605 Total: 1
Zip code: 06798 Total: 1
Zip code: 06820 Total: 3
Zip code: 06830 Total: 3

PL/SQL procedure successfully completed.

The same script produces the following results for the INSTRUCTOR table:

Enter value for sv_table_name: instructor
old 10: v_table_name VARCHAR2(20) := '&sv_table_name';
new 10: v_table_name VARCHAR2(20) := 'instructor';
Totals from instructor table
Zip code: 10005 Total: 1
Zip code: 10015 Total: 3
Zip code: 10025 Total: 4
Zip code: 10035 Total: 1

PL/SQL procedure successfully completed.

Summary

In this chapter, you learned how to build native dynamic SQL statements in PL/SQL; such statements are used when you need to build flexibility into your code. Dynamic SQL allows for varying the SQL statements that are executed at run time, thereby allowing for various elements of the SQL to change, such as the table and the columns. The first method covered in this chapter was the EXECUTE IMMEDIATE statement; you also saw how to avoid various Oracle errors when using this method. The OPEN-FOR, FETCH, and CLOSE statements were then explained in detail; these approaches allow for multirow queries.


By the Way

The companion website provides additional exercises and suggested answers for this chapter, with discussion related to how those answers resulted. The main purpose of these exercises is to help you test the depth of your understanding by utilizing all of the skills that you have acquired throughout this chapter.