By Steven Feuerstein
Most of the SQL statements you will write in PL/SQL are static, which means that they are parsed when you compile the block in which you wrote the SQL statements. But sometimes you don’t have all the information needed at compile time to parse your SQL statements. Perhaps the name of a column or a WHERE clause is constructed only when the program is executed. In this case, you must write dynamic SQL.
In this article, I will explore how to use PL/SQL features to execute dynamic SELECT statements that return multiple rows. It turns out that there are three different ways to do this:
Before diving into the details, however, it is incumbent upon me to offer several warnings regarding dynamic SQL and to urge you to make sure that you use the dynamic SQL features of PL/SQL only when you need them.
There are several good reasons to avoid unnecessary dynamic SQL:
Sometimes the misuse of dynamic SQL is obvious. Consider the following:
FUNCTION name_from_id (id_in IN INTEGER) RETURN VARCHAR2 AUTHID DEFINER IS l_the_name the_table.the_name%TYPE; BEGIN EXECUTE IMMEDIATE 'select the_name from the_table where id = ' || id_in INTO l_the_name; RETURN l_the_name; END;
The developer apparently believed that because the value of the ID can change, it needs to be concatenated to the SQL statement, so it has to be done dynamically. Ha! There’s nothing dynamic about this query. It should be rewritten to the following:
FUNCTION name_from_id (id_in IN INTEGER) RETURN VARCHAR2 AUTHID DEFINER IS l_the_name the_table.the_name%TYPE; BEGIN SELECT the_name INTO l_the_name FROM the_table WHERE id = id_in; RETURN l_the_name; END;
This is, of course, a simplistic example. In the real world, it can be harder to detect scenarios in which dynamic SQL can be made static. A more thorough examination of “false” dynamic SQL will be the subject of my next article.
Once you determine that you must switch to dynamic SQL, you should study SQL injection and protect your database by reducing “attack surfaces.” The most important steps to take are
OK, so you have determined that you need dynamic SQL, and you are going to use it appropriately and safely. Let’s explore options for dynamic multirow querying.
EXECUTE IMMEDIATE with BULK COLLECT
|Oracle’s Live SQL provides developers a free and easy online way to test and share SQL and PL/SQL application development concepts.|
EXECUTE IMMEDIATE is the most popular path to dynamic SQL in PL/SQL. With it, you can execute data definition language (DDL) statements (for example, drop a table), queries, nonquery data manipulation language (DML) statements such as inserts and deletes, and even dynamically constructed PL/SQL blocks.
From the standpoint of queries, EXECUTE IMMEDIATE works much like SELECT-INTO (implicit query). With static SQL and a single row fetch, you would write SELECT-INTO code like this:
BEGIN SELECT t.my_column INTO my_variable FROM my_table t WHERE single_row_condition; END;
If you are fetching more than one row with SELECT-INTO, you must use BULK COLLECT to populate an array:
BEGIN SELECT t.my_column BULK COLLECT INTO my_array FROM my_table t WHERE multi_row_condition; END;
And it is much the same with EXECUTE IMMEDIATE.
Note that in the following dynamic SQL code samples, I will use strings that are not, in fact, dynamic, in order to keep the code as simple as possible. Please heed my earlier dynamic SQL advice, which includes avoiding unnecessary usage of dynamic SQL, studying SQL injection, reducing “attack surfaces,” binding values, never trusting text entered by a user, using DBMS_ASSERT, and so on.
Also note that sample code for this article is available on Oracle’s Live SQL website.
Here is a sample block that uses EXECUTE IMMEDIATE with SELECT-INTO and BULK COLLECT:
DECLARE TYPE name_salary_rt IS RECORD ( name VARCHAR2 (1000), salary NUMBER ); TYPE name_salary_aat IS TABLE OF name_salary_rt INDEX BY PLS_INTEGER; l_employees name_salary_aat; BEGIN EXECUTE IMMEDIATE q'[select first_name || ' ' || last_name, salary from hr.employees order by salary desc]' BULK COLLECT INTO l_employees; FOR indx IN 1 .. l_employees.COUNT LOOP DBMS_OUTPUT.put_line (l_employees (indx).name); END LOOP; END;
Here is an explanation of this block, which features EXECUTE IMMEDIATE:
|2–5||Declare a custom record type for the two column values I will be retrieving.|
|7–8||Declare an associative array type of those record types.|
|10||Declare an associative array that will hold all the rows retrieved by my dynamic query.|
|12–16||Use EXECUTE IMMEDIATE to dynamically parse (if necessary) and execute the query. Note my use of “q” to escape single quotes within my string to keep the string itself more readable.
Because I might be fetching more than one row, I use BULK COLLECT INTO to fill up my array.
|18–21||Iterate through all the elements in the array and take the appropriate action (in this case, simply display the name).|
The OPEN FOR syntax takes advantage of the cursor variable feature of PL/SQL. You do not, in fact, have to use OPEN FOR with dynamic SQL, as in the following:
FUNCTION names_for (department_id_in IN INTEGER, min_salary_in IN NUMBER) RETURN SYS_REFCURSOR IS l_cursor SYS_REFCURSOR; BEGIN IF department_id_in IS NOT NULL THEN OPEN l_cursor FOR SELECT last_name FROM hr.employees WHERE department_id = department_id_in; ELSE OPEN l_cursor FOR SELECT last_name FROM hr.employees WHERE salary >= min_salary_in; END IF; RETURN l_cursor; END;
I take advantage of OPEN FOR in this case to simply allow myself to use conditional logic at runtime to determine which static SELECT statement should be associated with the cursor variable. I can then pass this variable back to the calling program, which is often not a PL/SQL program, such as a Java method.
But OPEN FOR is most commonly used with dynamically constructed queries. Let’s see how I can use OPEN FOR this way by rewriting the previous PL/SQL block that used EXECUTE IMMEDIATE.
DECLARE TYPE name_salary_rt IS RECORD ( name VARCHAR2 (1000), salary NUMBER ); TYPE name_salary_aat IS TABLE OF name_salary_rt INDEX BY PLS_INTEGER; l_employees name_salary_aat; l_cursor SYS_REFCURSOR; BEGIN OPEN l_cursor FOR q'[select first_name || ' ' || last_name, salary from hr.employees order by salary desc]'; FETCH l_cursor BULK COLLECT INTO l_employees; CLOSE l_cursor; FOR indx IN 1 .. l_employees.COUNT LOOP DBMS_OUTPUT.put_line (l_employees (indx).name); END LOOP; END;
Here is an explanation of the block, which features OPEN FOR with BULK COLLECT:
|2–10||This code is unchanged from the EXECUTE IMMEDIATE block: declare a custom record type, an associative array type of those record types, and an associative array that will hold all the rows retrieved by my dynamic query.|
|12||Declare a cursor variable that will hold the pointer to the dynamically constructed and opened dataset.|
|14–17||Instead of EXECUTE IMMEDIATE, use OPEN FOR to associate the dynamically constructed SQL statement with the l_cursor cursor variable. This cursor is also then opened.|
|19||With a single FETCH BULK COLLECT, retrieve all rows identified by the cursor into my array.|
|21||Don’t forget to close the cursor! Actually, PL/SQL will automatically close the cursor when the block terminates, but I suggest you include the explicit CLOSE anyway. It shows you are paying attention—and that you are done with the cursor from this point on in your program.|
|23–26||Use the same loop as with EXECUTE IMMEDIATE to process the contents of the array.|
There’s not much of a reason to use OPEN FOR instead of EXECUTE IMMEDIATE if you are going the BULK COLLECT route. In both cases, you need to declare the array. But with OPEN FOR, you need to follow up your OPEN statement with FETCH and CLOSE. All of that is done for you automatically with EXECUTE IMMEDIATE.
With OPEN FOR, however, you have another option: you do not need to use BULK COLLECT to fetch all rows in a single round-trip to the SQL engine. Instead, you can do something more akin to a cursor FOR loop:
DECLARE TYPE name_salary_rt IS RECORD ( name VARCHAR2 (1000), salary NUMBER ); l_record name_salary_rt; l_cursor SYS_REFCURSOR; BEGIN OPEN l_cursor FOR q'[select first_name || ' ' || last_name, salary from hr.employees order by salary desc]'; LOOP FETCH l_cursor INTO l_record; EXIT WHEN l_cursor%NOTFOUND; DBMS_OUTPUT.put_line (l_record.name); END LOOP; CLOSE l_cursor; END;
|2–9||Declare a record type, a record variable for that type, and a cursor variable to point to the dynamic result set. Note that you no longer need a collection.|
|11–14||Open the cursor for this dynamic SELECT and assign the cursor variable the pointer to the dataset.|
|16–22||Loop through the result set using the same FETCH and EXIT WHEN statements you would use with explicit cursors.|
|24||Close the cursor now that you are done with it.|
DBMS_SQL and Multirow Querying
DBMS_SQL is a package supplied by Oracle Database to perform dynamic SQL operations. Up until Oracle8i, it was the only way to execute dynamic SQL in PL/SQL. When native dynamic SQL commands (EXECUTE IMMEDIATE and OPEN FOR) were added in Oracle8i, DBMS_SQL became a method of last resort for dynamic SQL. This means that it is used now only for the most complex dynamic SQL scenarios, generally categorized as “method 4” dynamic SQL, which is when, at the time of compilation, you don’t know either the number of columns in your SELECT list or the number of bind variables in your statement.
DBMS_SQL is a last-resort option, because you must write lots more code to use it. For simple operations, such as those shown in the earlier examples, DBMS_SQL is overkill. For “method 4” dynamic SQL, it is a perfect fit.
You can use DBMS_SQL to fetch multiple rows either one at a time or in bulk. In this article, I will show you only the bulk approach.
DECLARE l_cursor PLS_INTEGER := DBMS_SQL.open_cursor; l_names DBMS_SQL.varchar2_table; l_salaries DBMS_SQL.number_table; l_fetch_count PLS_INTEGER; BEGIN DBMS_SQL.parse (l_cursor, q'[select first_name || ' ' || last_name, salary from hr.employees order by salary desc]', DBMS_SQL.native); DBMS_SQL.define_array (l_cursor, 1, l_names, 10, 1); DBMS_SQL.define_array (l_cursor, 2, l_salaries, 10, 1); l_fetch_count := DBMS_SQL.execute (l_cursor); LOOP l_fetch_count := DBMS_SQL.fetch_rows (l_cursor); DBMS_SQL.COLUMN_VALUE (l_cursor, 1, l_names); DBMS_SQL.COLUMN_VALUE (l_cursor, 2, l_salaries); EXIT WHEN l_fetch_count != 10; END LOOP; FOR indx IN 1 .. l_names.COUNT LOOP DBMS_OUTPUT.put_line (l_names (indx)); END LOOP; dbms_sql.close_cursor (l_cursor); END;
|2||Declare a DBMS_SQL cursor pointer. This pointer is used in each call to a DBMS_SQL subprogram.|
|3–4||Declare the two collections I will use to hold the names and salaries. I can declare my own collection types or I can use predefined DBMS_SQL types.|
|7–11||Parse the dynamic SELECT statement, associating it with the cursor.|
|13–14||Associate each expression in the SELECT list with an array variable. Note that I have to specify the position of the expression in the list (1 and then 2), the number of rows to retrieve with each fetch (10), and the starting location in the array (1).|
|16||Execute the SELECT statement. This identifies the result set. Now I can fetch the data.|
|19||Call the FETCH_ROWS procedure to fetch the next 10 rows (that’s what I specified in my call to DEFINE_ARRAY).|
|20–21||Use COLUMN_VALUE calls to write those rows of data to the collections.|
|23||Stop fetching if the last fetch retrieved fewer than 10 rows. This must be done!|
|26–29||Display the contents of the collections.|
|31||Close the cursor and release the memory.|
See what I mean about the volume and complexity of code you must write with DBMS_SQL? And this is a much-simplified example, because I did not have to deal with a variable number of expressions in the SELECT list or the number of bind variables.
By the way, one nice advantage of DBMS_SQL with bulk operations is that the elements are appended to the collection. With static SQL BULK COLLECT, the contents of the collection are completely replaced by the rows from the latest fetch.
The PL/SQL language makes it easy to implement dynamic SQL requirements. When it comes to querying multiple rows of data from a dynamic query, you can choose between EXECUTE IMMEDIATE, OPEN FOR, and DBMS_SQL. DBMS_SQL should be used only for the most dynamic situations, such as when you don’t know at compile time how many columns you are selecting. OPEN FOR makes the most sense when you are fetching a small number of rows, because you can avoid the extra coding involved with using arrays. EXECUTE IMMEDIATE is the simplest construct, but you must use arrays and BULK COLLECT if you might fetch more than one row.
LEARN more about dynamic SQL in PL/SQL.
VIEW and RUN this article’s code on the Live SQL website.
Photography by © Hero/Corbis
Steven Feuerstein is Oracle Corporation's Developer Advocate for PL/SQL, and an expert on the Oracle PL/SQL language, having written ten books on PL/SQL, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (all published by O'Reilly Media). Steven has been developing software since 1980, spent five years with Oracle back in the "old days" (1987-1992), and was PL/SQL Evangelist for Quest Software (and then Dell) from January 2001 to February 2014 - at which point he returned joyfully to Oracle Corporation. He was one of the original Oracle ACE Directors and writes regularly for Oracle Magazine, which named him the PL/SQL Developer of the Year in both 2002 and 2006. He is also the first recipient of ODTUG's Lifetime Achievement Award (2009).