Using Dynamic SQL for Multirow Queries

Explore three dynamic SQL solutions to understand which is best for your program requirements.

By Steven Feuerstein

November/December 2017

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.

It’s very easy to write static SQL in PL/SQL program units, and that’s one of the best things about PL/SQL. It’s also quite easy to implement dynamic SQL requirements in PL/SQL, especially compared to doing the same in nondatabase languages such as Java and JavaScript.

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:

• EXECUTE IMMEDIATE with BULK COLLECT
• OPEN FOR either with BULK COLLECT or with a loop
• DBMS_SQL either using arrays (with its own approach to bulk processing) or with a loop

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:

• Security. Dynamic SQL opens up the door to SQL injection, which can lead to data corruption and the leaking of sensitive data.
• Performance. While the overhead of executing dynamic SQL has gone way down over the years, it is certainly still faster to use static SQL.
• Maintainability. The code you write to support dynamic SQL is more—literally more code—and harder to understand and maintain.

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

• Whenever possible, bind values into your SQL string, rather than using concatenation.
• If you must concatenate, never trust text entered by a user. Check it for inappropriate content, such as JavaScript code or common SQL hacks such as "; DROP TABLE...."). Use the DBMS_ASSERT package to do some of the checking for you.

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

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:

OPEN FOR

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:

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;

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;

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.

Summary

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.

Next Steps

LEARN more about dynamic SQL in PL/SQL.

EXPLORE dynamic SQL scripts on the Live SQL website.

VIEW and RUN this article’s code on the Live SQL website.