Understanding native dynamic SQL
Using the EXECUTE IMMEDIATE command
Using OPEN...FOR — dynamically defined cursors
When writing code in most situations, you know what database information must be accessed and how the information might be manipulated. Using the simple Employee and Department example, you know what tables and columns are being used and/or changed, what criteria are used to select rows, and the column datatypes. However, in some cases, the clauses, commands, variable datatypes, number of variables, and database object references aren’t known prior to compiling the code. In these cases, the code must be created at runtime and will change each time the program is executed. These code statements are dynamic.
Dynamic SQL and PL/SQL allow you to create very flexible applications, where users can select which operations, tables, columns, and so on are involved. This chapter shows how you can build queries, pieces of PL/SQL code, or even whole procedural routines on the fly.
Having many (perhaps hundreds) of repetitive elements in your logic often causes problems. For example, you might ...