O'Reilly logo

Oracle PL/SQL For Dummies by Paul Dorsey, Michael Rosenblum

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 13

Dynamic SQL and PL/SQL

In This Chapter

bullet Understanding native dynamic SQL

bullet Using the EXECUTE IMMEDIATE command

bullet 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required