By Steven Feuerstein
Best-practice tips for using Native Dynamic SQL in Oracle Database 10g
In recent years, dynamic SQL has become a much more common aspect of PL/SQL programming. Prior to the availability of Native Dynamic SQL (NDS) in Oracle8i, dynamic SQL was available in PL/SQL only through the DBMS_SQL package. Although very robust, DBMS_SQL was very difficult to use and relatively slow. Now, in Oracle Database 10g, NDS can handle virtually all of the most common dynamic SQL requirements, and it is a joy to use in comparison to the DBMS_SQL API .
However easy it is to write dynamic SQL with NDS, it is still dynamic (resolved at runtime) and it is still SQL, both of which mean that you need to be conscientious about how you write and run NDS code and handle errors in it. This article offers some best practices for NDS usage to help you get your dynamic applications up and running smoothly.
To use NDS, you need to learn how to write variations on the EXECUTE IMMEDIATE statement. The syntax for this statement is
EXECUTE IMMEDIATE sql_string [INTO variable_list ] [USING bind_variable_list ]
where sql_string is the string that contains the SQL statement or PL/SQL block, variable_list is the optional list of variables that will be receiving data from sql_string (used only if you are executing a query), and bind_variable_list is the optional list of values you can bind into the dynamic string.
There are certainly some tricky aspects to setting up the INTO and USING clauses, but you will encounter your biggest headaches as you construct arbitrarily complex and general SQL statements. Consider the code in Listing 1 . The tabcount function returns the number of rows in a table for a specified, optional WHERE clause. The SELECT statement itself is very general:
'SELECT COUNT(*) FROM ' || tab || ' WHERE ' || NVL (whr, '1=1');
So if I call the tabcount function with the values EMP for tab and DEPTNO = 10 for whr , I will execute the following query:
SELECT COUNT(*) FROM EMP WHERE DEPTNO = 10
The following example calls the tabcount function with these inputs:
SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE (tabcount ('EMP', 'DEPTNO = 10')); 3 END; 4 / 3
If, on the other hand, I pass the invalid value of DEPTNO - 10 , I end up with an error: SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE (tabcount ('EMP', 'DEPTNO - 10')); 3 END; 4 / BEGIN * ERROR at line 1: ORA-00920: invalid relational operator ORA-06512: at "SCOTT.TABCOUNT", line 7 ORA-06512: at line 2
Dynamic SQL refers to DDL, DML, and query statements that are constructed, parsed, and executed at runtime. It is called dynamic because the SQL statement you want to run is not fully known (or static) when you write and compile your code. Most of the time, you need input from the user, such as the columns that person wants to see or some element of the WHERE clause, to complete the SQL statement.
Here is an example of a dynamic DDL statement:
'DROP TABLE ' || l_my_table
Dynamic PL/SQL refers to anonymous PL/SQL blocks that are constructed, compiled, and executed at runtime. Here is an example of a dynamic PL/SQL block:
'BEGIN report_pkg.run_report (' || l_report_id || ');'
Dynamic PL/SQL statements must end in a semi- colon; dynamic SQL statements may not end in a semicolon.
Because I am running this simple script in SQL*Plus, I see the error trace and can quickly go to line 2 and check my string. In a real application, however, the error is likely to be handled somewhere in the nested program calls and this information may not be available. Beyond that, the string is probably not hard-coded but, rather, a variable value passed in via parameters, making it even more difficult to track down the source of the problem.
Given the opaque nature of these dynamic strings, it is extremely important to trap, handle, and record all possible information about errors related to dynamic SQL statements as close as possible to the source of the error.
Listing 2 contains a rewrite of the tabcount function that demonstrates the following techniques you should use to deal with dynamic SQL errors:
Don't construct your dynamic string in the EXECUTE IMMEDIATE statement. Instead, put the string together and assign it to a variable. Then pass that variable to the EXECUTE IMMEDIATE statement. If the dynamic SQL statement fails, you can then display or log that string as part of your error handling.
Include an exception section in the block of code that contains the EXECUTE IMMEDIATE statement. In other words, handle the error as close as possible to where it was raised. You will then have access to as much information as possible about why the failure occurred.
Consolidate all error handling logic in a local procedure to ensure consistent error handling and minimize code redundancy. The code in Listing 2 displays the error stack using DBMS_UTILITY.FORMAT_ERROR_STACK ; the error backtrace using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE (available only in Oracle 10g; it tells you the precise line on which the error was raised); and most important, the dynamic SQL string.
Include specific handlers (within the exception section) for errors that commonly occur, but you should certainly also include a WHEN OTHERS clause. When writing Listing 2, I realized that a common error will be when a user passes an invalid table name. So I declared a local, named exception for the ORA-00942 error and then provided a simple, clear message to help resolve the problem, as shown when I run the tabcount function with an incorrect table name:
SQL> BEGIN 2 DBMS_OUTPUT.put_line (tabcount ('EMPX', 'DEPTNO = 10')); 3 END; 4 / tabcount ERROR: Unable to find a table or view named "EMPX" Dynamic query: SELECT COUNT(*) FROM EMPX WHERE DEPTNO = 10
Whether I've anticipated an error and written a message for it, as in the preceding example, or I've used the WHEN OTHERS clause to display the Oracle error message, as in the following example, I also display the variable string after the "Dynamic query:" label, which allows me to quickly scan the SQL statement and find the problematic section:
SQL> BEGIN 2 DBMS_OUTPUT.put_line (tabcount ('EMP', 'DEPTNO - 10')); 3 END; 4 / tabcount ERROR: ORA-00920: invalid relational operator Dynamic query: SELECT COUNT(*) FROM EMP WHERE DEPTNO - 10
One final note regarding error handling: These examples rely simply on DBMS_OUTPUT to display the error information. In a production application, you are much more likely to write this data out to a table or a file. If, however, you do decide to display this information via DBMS_OUTPUT , you should not call DBMS_OUTPUT.PUT_LINE directly. The SQL string, error stack, and backtrace can each easily exceed 255 characters, in which case calling DBMS_OUTPUT.PUT_LINE directly will raise an exception. You should instead use a program such as the pl procedure, shown in Listing 2, that wraps long lines and avoids this error.
Suppose I am lucky enough to work in a development environment in which code reuse is actively encouraged and an infrastructure has been set up to make it easy to share code. The tabcount function looks useful, so I place it in the library. Rather than have the function declared in multiple schemas across the database, I create it in the CODESHARE schema and then make it available to all users, with these commands:
GRANT EXECUTE ON tabcount TO PUBLIC; CREATE PUBLIC SYNONYM tabcount FOR tabcount;
I proudly send out a memo to my team members, inviting them to use the utility. The next day, I receive an e-mail from an irritated coworker: "Dear Steven, I tried using your function and ended up very frustrated. I created a table, put a row in it, and then called tabcount . Unfortunately, all I got was an error. What's the problem?" She attaches a log of her activities, shown in Listing 3.
I'm puzzled at first but then realize the problem: My function was running in "definer rights" execution mode and was therefore trying to find a table named XYZ in the CODESHARE schema rather than in my coworker's development schema.
"Definer rights" means that when you run a stored program, it runs under the authority of the definer or owner of the program. My function was installed in CODESHARE and relied on the default execution mode: definer rights. Thus, when my coworker called tabcount from her schema, tabcount tried to calculate a row count on a table named XYZ in the CODESHARE schema.
To solve the problem, I add an AUTHID clause to the header of the function, specifying CURRENT_USER or "invoker rights" as the execution mode:
CREATE OR REPLACE FUNCTION tabcount ( tab IN VARCHAR2, whr IN VARCHAR2 := NULL) RETURN PLS_INTEGER AUTHID CURRENT_USER
Specifying invoker rights means that when a user runs a stored program, it runs under the authority of that user or invoker. Now when my coworker uses tabcount , she gets the expected results:
SQL> exec DBMS_OUTPUT.PUT_LINE ( tabcount('xyz'));
As I mentioned previously, just about the hardest part of working with dynamic SQL is putting together that dynamic string properly. Because the SQL statement is dynamic, you will be completing that statement at runtime, through either concatenation or binding of variable values into the string. A general rule of thumb is to bind whenever possible and minimize concatenation. Let's compare these two options in a SQL statement.
Listing 4 offers a very generic procedure— date_range_col_update —that updates the value of a numeric column for all rows within the specified date range. It relies heavily on binding; note the placeholders for the value ( :val ), start date ( :lodate ), and end date ( :hidate ). As a result, even though the SQL is very generic, it is simple and easy to read (and therefore maintain):
'UPDATE ' || tab_in || ' SET ' || valcol_in || ' = :val WHERE ' || datecol_in || ' BETWEEN :lodate AND :hidate';
Now consider the same functionality implemented through concatenation of the variable values, as shown in Listing 5. Now my SQL string is considerably more complicated:
'UPDATE ' || tab_in || ' SET ' || valcol_in || ' = ' || TO_CHAR (val_in) || ' WHERE ' || datecol_in || ' BETWEEN TO_DATE (''' || TO_CHAR (start_in) || ''') AND TO_DATE (''' || TO_CHAR (end_in) || ''')';
When you concatenate, you may have to deal with single quotes inside strings. This means that you will have to stick multiple single quotes together to get them to evaluate properly—unless you are running Oracle Database 10g. In that case, you can take advantage of the new quote escape character.
Before Oracle Database 10g, you wrote:
'TO_DATE (''' || TO_CHAR (start_in) || ''')'
With Oracle Database 10g, you can use different characters (such as [ and ]) to indicate the start and the end of your literal string, after which you can place a single quote inside the literal without having to worry about how many single quotes you need:
q'[ TO_DATE (']' || TO_CHAR (start_in) || q'[')]'
Finally, remember that you cannot bind values into the names of tables or columns or any portion of the SQL statement itself. You can bind values only into variables within the SQL statement.
There are times when it makes sense not to use Native Dynamic SQL and to use the DBMS_SQL package instead. This article closes with some suggestions on when not to use Native Dynamic SQL and choose DBMS_SQL instead. Although NDS is generally easier to use and much simpler to write, there are occasions when you will need or want to use the DBMS_SQL built-in package for your dynamic SQL.
Here are a few of these scenarios:
You need to execute a dynamic SQL string that is longer than 32K characters. EXECUTE IMMEDIATE parses and runs a single string, which means that it is limited to 32K. There may well be cases, such as with very large generated SQL statements and compilation of DDL (such as CREATE OR REPLACE PACKAGE >), where you will exceed that limit. In such cases, use the overloading of DBMS_SQL.PARSE that accepts a collection of strings and then parses the statement formed from the concatenation of all those strings.
You need to use Method 4 Dynamic SQL. Method 4 is the most complex and generic of dynamic SQL forms. It is appropriate when you do not know, when you write your code, either of the following: how many columns you are retrieving in a query or how many variable values you are binding into your string. Although it is possible to implement Method 4 dynamic SQL in NDS, the task is actually more straightforward with DBMS_SQL .
You want to minimize the parsing of your SQL statement. One downside of NDS is that your SQL statement will be parsed each time it is executed, even if the same statement has been parsed just a moment ago. If your statement is large and complex, that overhead can be significant. With DBMS_SQL you can bypass the parse phase and simply reexecute that same statement with different bind variables.
Listing 6 shows one possible use of array-based parsing of dynamic SQL. I need a utility to compile my PL/SQL source code from files, and I need to do it in PL/SQL. These files can be quite long, so I cannot use EXECUTE IMMEDIATE . Instead, I switch to DBMS_SQL for my compile_from_file utility, as shown in Listing 6 .
There are two main steps in this program: load the file contents into an array and then parse that array, which also executes the DDL statement (and issues a commit). To work with the array-oriented overloading of DBMS_SQL.PARSE , you need to use a collection type the program understands. I do so with the declaration of the l_lines array:
Then, after I have populated the array with calls to UTL_FILE.GET_LINE , I can call the parse program as follows:
DBMS_SQL.parse (c =< l_cur ,statement => l_lines ,lb => l_lines.FIRST ,ub => l_lines.LAST ,lfflg => TRUE ,language_flag =>DBMS_SQL.native );
The first parameter is the cursor handle, the second is the array, and the third and fourth parameters specify which rows (lower and upper bounds) in the array I want parsed. In this case, I use all of the lines. The fifth parameter specifies that I want a line feed inserted after each line, which is rather important if I want readable source code in the database. Finally, the language_flag value instructs DBMS_SQL to parse according to the native version.
Considering what we are doing, this is fairly simple code to write and understand. Because it is DBMS_SQL , you must open the cursor explicitly, and you must remember to close it when you are done or if you encounter an error. The same holds true for the file handle.
As dynamic SQL becomes more accessible and efficient, it will become more pervasive in application code bases. Conse-quently, it is very important for developers to use NDS with care and use best practices to make sure the code can be easily debugged and enhanced over time.