Oracle PL/SQL for DBAs

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

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.

Here is an example of using dynamic PLS/SQL. This is a true story, I kid you not. During a consulting stint at an insurance company in Chicago, I was asked to see what I could do about a particularly vexing program. It was very large and continually increased in size—soon it would be too large to even compile. Much to my amazement, ...