Oracle PL/SQL for DBAs

The EXECUTE IMMEDIATE Statement

Use EXECUTE IMMEDIATE to execute (immediately!) the specified SQL statement. Here is the syntax of this statement:

    EXECUTE IMMEDIATE SQL_string
       [INTO {define_variable[, define_variable]... | record}]
       [USING [IN | OUT | IN OUT] bind_argument
           [, [IN | OUT | IN OUT] bind_argument]...];

where:

SQL_string

String expression containing the SQL statement or PL/SQL block.

define_variable

Variable that receives a column value returned by a query.

record

Record based on a user-defined TYPE or %ROWTYPE that receives an entire row returned by a query.

bind_argument

Expression whose value is passed to the SQL statement or PL/SQL block, or an identifier that serves as an input and/or output variable to the function or procedure that is called in the PL/SQL block.

INTO clause

Used for single-row queries; for each column value returned by the query, you must supply an individual variable or field in a record of a compatible type.

USING clause

Allows you to supply bind arguments for the SQL string. This clause is used for both dynamic SQL and PL/SQL, which is why you can specify a parameter mode. This usage is relevant only for PL/SQL; however, the default is IN, which is the only kind of bind argument you would have for SQL statements.

You can use EXECUTE IMMEDIATE for any SQL statement or PL/SQL block except for multiple-row queries. If SQL_string ends with a semicolon, it will be treated as a PL/SQL block; otherwise, it will be treated as a SELECT, DML (INSERT, UPDATE, ...