C H A P T E R  8

Dynamic SQL

Oracle provides dynamic SQL as a means for generating DML or DDL at runtime. It can be useful when the full text of a SQL statement or query is not known until application runtime. Dynamic SQL can help overcome some of the limitations of static SQL, such as generating a full SQL query based upon some user-provided information or inserting into a specific table depending upon a user action within your application. Simply put, the ability to use dynamic SQL within PL/SQL applications provides a level of flexibility that is not attainable with the use of static SQL alone.

Oracle allows dynamic SQL to be generated in two different ways: native dynamic SQL and through the use of the DBMS_SQL package. Each strategy has its own benefits as well as drawbacks. In comparison, native dynamic SQL is easier to use, it supports user-defined types, and it performs better than DBMS_SQL. On the other hand, DBMS_SQL supports some features that are not currently supported in native dynamic SQL such as the use of the SQL*Plus DESCRIBE command and the reuse of SQL statements. Each of these methodologies will be compared under various use cases within this chapter. By the end of the chapter, you should know what advantages each approach has to offer and which should be used in certain circumstances.