Dynamic SQL is a programming methodology for generating and running SQL statements at run time. It is useful when writing general-purpose and flexible programs like ad hoc query systems, when writing programs that must run database definition language (DDL) statements, or when you do not know at compilation time the full text of a SQL statement or the number or data types of its input and output variables.
PL/SQL provides two ways to write dynamic SQL:
Native dynamic SQL, a PL/SQL language (that is, native) feature for building and running dynamic SQL statements
DBMS_SQL package, an API for building, running, and describing dynamic SQL statements
Native dynamic SQL code is easier to read and write than equivalent code that uses the
DBMS_SQL package, and runs noticeably faster (especially when it can be optimized by the compiler). However, to write native dynamic SQL code, you must know at compile time the number and data types of the input and output variables of the dynamic SQL statement. If you do not know this information at compile time, you must use the
Native dynamic SQL processes most dynamic SQL statements with the
IMMEDIATE statement. The "native" in the name refers to the fact that you execute statements native to the PL/SQL language, rather than making calls to the DBMS_SQL supplied package.
If the dynamic SQL statement is a
SELECT statement that returns multiple rows, native dynamic SQL gives you these choices:
IMMEDIATE statement with the
The SQL cursor attributes work the same way after native dynamic SQL
DELETE, and single-row
SELECT statements as they do for their static SQL counterparts.
Here is an example of using native dynamic SQL to create a table:
EXECUTE IMMEDIATE 'create table my_data (n number)';
When you parse (and execute) a DDL statement in PL/SQL via EXECUTE IMMEDIATE or DBMS_SQL.PARSE, the PL/SQL engine performs a commit both before and after the statement is executed.