Section 16.4.  Dynamic PL/SQL

16.4. Dynamic PL/SQL Dynamic PL/SQL offers some of the most interesting and challenging coding opportunities. Think of it: while a user is running your application, you can take advantage of NDS to do any of the following: Create a program, including a package that contains globally accessible data structures Obtain (and modify) by name the value of global variables Call functions and procedures whose names are not known at compile time I have used this technique to build very flexible code generators, softcoded calculation engines for users, and much more. Dynamic PL/SQL allows you to work at a higher level of generality, which can be both challenging and exhilarating. There are some rules and tips you need to keep in mind when working with dynamic PL/SQL blocks and NDS: The dynamic string must be a valid PL/SQL block. It must start with the DECLARE or BEGIN keyword, and end with an END statement and semicolon. The string will not be considered PL/SQL code unless it ends with a semicolon. In your dynamic block, you can access only PL/SQL code elements that have global scope (standalone functions and procedures, and elements defined in the specification of a package). Dynamic PL/SQL blocks execute outside the scope of the local enclosing block. Errors raised within a dynamic PL/SQL block can be trapped and handled by the local block in which the string was run with the EXECUTE IMMEDIATE statement. Let's explore these rules. First, I will build a little utility to execute dynamic PL/SQL: /* File on web: dynplsql.sp */ CREATE OR REPLACE PROCEDURE dynPLSQL (blk IN VARCHAR2) IS BEGIN EXECUTE IMMEDIATE 'BEGIN ' || RTRIM (blk, ';') || '; END;'; END; This one program encapsulates many of the rules mentioned previously for PL/SQL execution. By enclosing the string within a BEGIN-END anonymous block, I guarantee that whatever I pass in will be executed as a valid PL/SQL block. For instance, I can execute the calc_totals procedure dynamically as simply as this: SQL> exec dynPLSQL ('calc_totals'); Now let's use this program to examine what kind of data structures you can reference within a dynamic PL/SQL block. In the following anonymous block, I want to use dynamic SQL to assign a value of 5 to the local variable num: <<dynamic>> DECLARE num NUMBER; BEGIN dynPLSQL ('num := 5'); END; This string is executed within its own BEGIN-END block, which appears to be a nested block within the anonymous block named "dynamic." Yet when I execute this script, I receive the following error: PLS-00201: identifier 'NUM' must be declared ORA-06512: at "SCOTT.DYNPLSQL", line 4 The PL/SQL engine is unable to resolve the reference to the variable named num. I get the same error even if I qualify the variable name with its block name: <<dynamic>> DECLARE num NUMBER; BEGIN /* Also causes a PLS-00302 error! */ dynPLSQL ('dynamic.num := 5'); END; Now suppose that I define the num variable inside a package called dynamic: CREATE OR REPLACE PACKAGE dynamic IS num NUMBER; END; I can now successfully execute the dynamic assignment to this newly defined variable : BEGIN dynPLSQL ('dynamic.num := 5'); END; What's the difference between these two pieces of data? In my first attempt, the variable num is defined locally in the anonymous PL/SQL block. In my second attempt, num is a public global variable defined in the dynamic package. This distinction makes all the difference with dynamic PL/SQL. It turns out that a dynamically constructed and executed PL/SQL block is not treated as a nested block; instead, it is handled as if it were a procedure or function called from within the current block. So any variables local to the current or enclosing blocks are not recognized in the dynamic PL/SQL block; you can make references only to globally defined programs and data structures. These PL/SQL elements include standalone functions and procedures and any elements defined in the specification of a package. Fortunately, the dynamic block is executed within the context of the calling block. If you have an exception section within the calling block, it will trap exceptions raised in the dynamic block. So if I execute this anonymous block in SQL*Plus: BEGIN dynPLSQL ('undefined.packagevar := ''abc'''); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLCODE); END; I will not get an unhandled exception. The assignment performed in this anonymous block is an example of indirect referencing . I don't reference the variable directly, but instead do so by specifying the name of the variable. The Oracle Forms Builder product (formerly known as SQL*Forms and Oracle Forms) offers an implementation of indirect referencing with the NAME_IN and COPY programs. This feature allows developers to build logic that can be shared across all forms in the application. PL/SQL does not support indirect referencing, but you can implement it with dynamic PL/SQL. See the dynvar.pkg file on the book's web site for an example of such an implementation. The following sections offer a few examples of dynamic PL/SQL to spark your interest and, perhaps, inspire your creativity. 16.4.1. Replace Repetitive Code with Dynamic Block This is a true story, I kid you not. During a consulting stint at an insurance company here in Chicago, I was asked to see what I could do about a particularly vexing program. It was very large and continually increased in sizesoon it would be too large to even compile. Much to my amazement, this is what the program looked like: CREATE OR REPLACE PROCEDURE process_line (line IN INTEGER) IS BEGIN IF line = 1 THEN process_line1; ELSIF line = 2 THEN process_line2; ... ELSIF line = 514 THEN process_line514; ... ELSIF line = 2057 THEN process_line2057; END IF; END; Each one of those line numbers represented fine print in an insurance policy that helped the company achieve its primary objective (minimizing the payment of claims). For each line number, there was a "process_line" program that handled those details. And as the insurance company added more and more exceptions to the policy, the program got bigger and bigger. Not a very scalable approach to programming! To avoid this kind of mess, a programmer should be on the lookout for repetition of code. If you can detect a pattern, you can either create a reusable program to encapsulate that pattern, or you can explore the possibility of expressing that pattern as a dynamic SQL construction. At the time, I fixed the problem using DBMS_SQL, but dynamic SQL would have been a perfect match. Here's the NDS implementation: CREATE OR REPLACE PROCEDURE process_line (line IN INTEGER) IS BEGIN EXECUTE IMMEDIATE 'BEGIN process_line' || line || '; END;'; END; From thousands of lines of code down to one executable statement! Of course, in most cases, identification of the pattern and conversion of that pattern into dynamic SQL will not be so straightforward. Still, the potential gains are enormous. 16.4.2. Implement Method 4 Dynamic SQL in NDS Method 4 dynamic SQL, introduced early in this chapter, means that at compile time, you don't know how many columns you will be querying and/or how many bind variables will need to be set. This level of uncertainty greatly complicates the code you will have to write. This section examines the kind of code you need to write to implement dynamic SQL Method 4 with native dynamic SQL. Later in the chapter, we review how you can implement Method 4 with DBMS_SQL. First, let's take a look at an example that will drive home the complexities involved. Suppose that I want to retrieve a single row by a dynamic WHERE clause and from a table whose name is specified at runtime. My program would, at first glance, look something like this: CREATE OR REPLACE PROCEDURE get_rows ( table_in IN VARCHAR2 , where_in IN VARCHAR2 ) IS BEGIN EXECUTE IMMEDIATE 'SELECT * FROM ' || table_in || ' WHERE ' || where_in INTO l_row; END; No matter what table name I pass to this procedure, "SELECT *" will retrieve all the columns for all the rows. "So what's the problem?" you might be asking. The problem may be found in the INTO clause. I reference a variable named l_row. Yet I must declare that variable before I can compile and run my code. This variable must be a record, whose structure matches that of the table. Yet I cannot declare a variable based on a dynamic record type. OK, so maybe what I should do is avoid hardcoding the record variable and instead select into individual variables, and then just make that list variable, as in: BEGIN EXECUTE IMMEDIATE 'SELECT * FROM ' || table_in || ' WHERE ' || where_in INTO || l_variable_list; END get_rows; Conceptually, that may sound fine, but it simply won't work. The bottom line regarding Method 4 and EXECUTE IMMEDIATE is that the INTO and USING clauses of this statement are static, not dynamic. So is it impossible to implement Method 4 requirements with NDS? No, it is, in fact, possible, but we will have to switch gears, and use dynamic PL/SQL. Consider the following revised implementation of get_rows: CREATE OR REPLACE PROCEDURE get_rows ( table_in IN VARCHAR2 , where_in IN VARCHAR2 ) IS BEGIN EXECUTE IMMEDIATE 'DECLARE l_row ' || table_in || '%ROWTYPE; BEGIN SELECT * INTO l_row FROM ' || table_in || ' WHERE ' || where_in || '; END;'; END get_rows; Now the EXECUTE IMMEDIATE runs a dynamic PL/SQL block. All the static parts of this block are in bold. The highlights of this implementation include the following: Because I have switched to dynamic PL/SQL, I can now declare a local variable, l_row, as a record based on the table with %ROWTYPE. This means that my SELECT statement will fetch a row from the variable table and deposit it successfully into the record. The SELECT statement is no longer dynamic, within the context of the dynamic block. By making the whole block dynamic, I can actually simplify the SQL. I can insert carriage returns inside my literal strings; as long as the resulting code is valid, Oracle won't care, and it will make code easier to read. Shifting from dynamic SQL to dynamic PL/SQL is the key step to implementing Method 4 with NDS. What I have shown you is a very simple example. There are, of course, complexities. If you have to deal with a dynamic number of bind variables, you will have to write a parsing routine to ferret out each appearance of the placeholders and replace them with your variable values. In addition, if you need to pass the data that you have queried from the dynamic block to the outer, static block, you will run into additional complexities. That outer block will not know the structure of the data you have populated with your query. At this point, however, the implementation becomes very much application-specific. For a more complete demonstration of using dynamic PL/SQL to implement Method 4 with NDS, check out the intab9i.sp file available on the book's web site. The intab procedure offers a PL/SQL implementation of "SELECT * FROM table," that is, it queries and displays (via DBMS_OUTPUT) the contents of the specified table. While it is possible to implement Method 4 with NDS, it is an awkward and complicated process. This is one dynamic requirement for which DBMS_SQL might provide a better solution. Finally, if you do choose NDS for Method 4, you may still want to take advantage of the DBMS_SQL.DESCRIBE_COLUMNS procedure. Both of these topics are addressed later in this chapter.