Executing Dynamic Query Statements

EXECUTE IMMEDIATE

Figure 1 shows the syntax diagram.

Figure 1 EXECUTE IMMEDIATE dynamic_select_clause::=

Figure 2 shows the syntax diagram for using_clause.

Figure 2 using_clause-1

The above syntax diagram is explained as follows:

• define_variable: specifies variables to store single-line query results.
• USING IN bind_argument: specifies where the variable passed to the dynamic SQL value is stored, that is, in the dynamic placeholder of dynamic_select_string.
• USING OUT bind_argument: specifies where the dynamic SQL returns the value of the variable.
  

  • In query statements, INTO and OUT cannot coexist.
  • A placeholder name starts with a colon (:) followed by digits, characters, or strings, corresponding to bind_argument in the USING clause.
  • bind_argument can only be a value, variable, or expression. It cannot be a database object such as a table name, column name, and data type. That is, bind_argument cannot be used to transfer schema objects for dynamic SQL statements. If a stored procedure needs to transfer database objects through bind_argument to construct dynamic SQL statements (generally, DDL statements), you are advised to use double vertical bars (||) to concatenate dynamic_select_clause with a database object.
  • A dynamic PL/SQL block allows duplicate placeholders. That is, a placeholder can correspond to only one bind_argument in the USING clause.

Example

OPEN FOR

Dynamic query statements can be executed by using OPEN FOR to open dynamic cursors.

For details about the syntax, see Figure 3.

Figure 3 open_for::=

Parameter description:

• cursor_name: specifies the name of the cursor to be opened.
• dynamic_string: specifies the dynamic query statement.
• USING value: applies when a placeholder exists in dynamic_string.

For use of cursors, see Cursors.

Example