Oracle PL/SQL For Dummies

Chapter 13

Dynamic SQL and PL/SQL

In This Chapter

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.

Taking Advantage of Dynamic SQL

Having many (perhaps hundreds) of repetitive elements in your logic often causes problems. For example, you might ...