This method lets your program accept or build a dynamic SQL statement, then immediately execute it using the EXECUTE IMMEDIATE command. The SQL statement must not be a query (SELECT statement) and must not contain any placeholders for input host variables. For example, the following host strings qualify:

'DELETE FROM EMP WHERE DEPTNO = 20' 
'GRANT SELECT ON EMP TO scott' 

With Method 1, the SQL statement is parsed every time it is executed.

This method lets your program accept or build a dynamic SQL statement, then process it using the PREPARE and EXECUTE commands. The SQL statement must not be a query. The number of placeholders for input host variables and the datatypes of the input host variables must be known at precompile time. For example, the following host strings fall into this category:

'INSERT INTO EMP (ENAME, JOB) VALUES (:emp_name, :job_title)' 
'DELETE FROM EMP WHERE EMPNO = :emp_number' 

With Method 2, the SQL statement is parsed just once, but can be executed many times with different values for the host variables. SQL data definition statements such as CREATE and GRANT are executed when they are PREPAREd.

This method lets your program accept or build a dynamic query, then process it using the PREPARE command with the DECLARE, OPEN, FETCH, and CLOSE cursor commands. The number of select-list items, the number of placeholders for input host variables, and the datatypes of the input host variables must be known at precompile time. For example, the following host strings qualify:

'SELECT DEPTNO, MIN(SAL), MAX(SAL) FROM EMP GROUP BY DEPTNO' 
'SELECT ENAME, EMPNO FROM EMP WHERE DEPTNO = :dept_number' 

This method lets your program accept or build a dynamic SQL statement, then process it using descriptors. The number of select-list items, the number of placeholders for input host variables, and the datatypes of the input host variables can be unknown until run time. For example, the following host strings fall into this category:

'INSERT INTO EMP (<unknown>) VALUES (<unknown>)' 
'SELECT <unknown> FROM EMP WHERE DEPTNO = 20'
 

Method 4 is required for dynamic SQL statements that contain an unknown number of select-list items or input host variables.

With all four methods, you must store the dynamic SQL statement in a character string, which must be a host variable or quoted literal. When you store the SQL statement in the string, omit the keywords EXEC SQL and the ';' statement terminator.

With Methods 2 and 3, the number of placeholders for input host variables and the datatypes of the input host variables must be known at precompile time.

Each succeeding method imposes fewer constraints on your application, but is more difficult to code. As a rule, use the simplest method you can. However, if a dynamic SQL statement will be executed repeatedly by Method 1, use Method 2 instead to avoid reparsing for each execution.

Method 4 provides maximum flexibility, but requires complex coding and a full understanding of dynamic SQL concepts. In general, use Method 4 only if you cannot use Methods 1, 2, or 3.

The decision logic in Figure 13-1 will help you choose the right method.

When the SQL statement is EXECUTEd, input host variables in the USING clause replace corresponding placeholders in the PREPAREd dynamic SQL statement.

Every placeholder in the PREPAREd dynamic SQL statement must correspond to a different host variable in the USING clause. So, if the same placeholder appears two or more times in the PREPAREd statement, each appearance must correspond to a host variable in the USING clause.

The names of the placeholders need not match the names of the host variables. However, the order of the placeholders in the PREPAREd dynamic SQL statement must match the order of corresponding host variables in the USING clause.

If one of the host variables in the USING clause is an array, all must be arrays.

To specify NULLs, you can associate indicator variables with host variables in the USING clause.

PREPARE parses the dynamic SQL statement and gives it a name. In the following example, PREPARE parses the query stored in the character string select_stmt and gives it the name sql_stmt:

char select_stmt[132] =
     "SELECT MGR, JOB FROM EMP WHERE SAL < :salary"; 
EXEC SQL PREPARE sql_stmt FROM :select_stmt; 

Commonly, the query WHERE clause is input from a terminal at run time or is generated by the application.

The identifier sql_stmt is not a host or program variable, but must be unique. It designates a particular dynamic SQL statement.

The following statement is correct also:

EXEC SQL PREPARE sql_stmt FROM SELECT MGR, JOB FROM EMP WHERE SAL < :salary;

The following prepare statement, which uses the '%' wildcard, is correct also:

EXEC SQL PREPARE S FROM select ename FROM test WHERE ename LIKE 'SMIT%';

DECLARE defines a cursor by giving it a name and associating it with a specific query. Continuing our example, DECLARE defines a cursor named emp_cursor and associates it with sql_stmt, as follows:

EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt; 

The identifiers sql_stmt and emp_cursor are not host or program variables, but must be unique. If you declare two cursors using the same statement name, the precompiler considers the two cursor names synonymous.

We can define a scrollable cursor named emp_cursor and associate it with sql_stmt as follows:

EXEC SQL DECLARE emp_cursor SCROLL CURSOR FOR sql_stmt;

For example, if you execute the statements

EXEC SQL PREPARE sql_stmt FROM :select_stmt; 
EXEC SQL DECLARE emp_cursor FOR sql_stmt; 
EXEC SQL PREPARE sql_stmt FROM :delete_stmt; 
EXEC SQL DECLARE dept_cursor FOR sql_stmt; 

when you OPEN emp_cursor, you will process the dynamic SQL statement stored in delete_stmt, not the one stored in select_stmt.

OPEN allocates an Oracle cursor, binds input host variables, and executes the query, identifying its active set. OPEN also positions the cursor on the first row in the active set and zeroes the rows-processed count kept by the third element of sqlerrd in the SQLCA. Input host variables in the USING clause replace corresponding placeholders in the PREPAREd dynamic SQL statement.

In our example, OPEN allocates emp_cursor and assigns the host variable salary to the WHERE clause, as follows:

EXEC SQL OPEN emp_cursor USING :salary; 

FETCH returns a row from the active set, assigns column values in the select list to corresponding host variables in the INTO clause, and advances the cursor to the next row. If there are no more rows, FETCH returns the "no data found" Oracle error code to sqlca.sqlcode.

In our example, FETCH returns a row from the active set and assigns the values of columns MGR and JOB to host variables mgr_number and job_title, as follows:

EXEC SQL FETCH emp_cursor INTO :mgr_number, :job_title; 

If the cursor is declared in SCROLL mode, you can then use the various FETCH orientation modes to randomly access the result set.

CLOSE disables the cursor. Once you CLOSE a cursor, you can no longer FETCH from it.

In our example, CLOSE disables emp_cursor, as follows:

EXEC SQL CLOSE emp_cursor; 

To process this kind of dynamic query, your program must issue the DESCRIBE SELECT LIST command and declare a data structure called the SQL Descriptor Area (SQLDA). Because it holds descriptions of columns in the query select list, this structure is also called a select descriptor.

Likewise, if a dynamic SQL statement contains an unknown number of placeholders for input host variables, the host-variable list cannot be established at precompile time by the USING clause.

To process the dynamic SQL statement, your program must issue the DESCRIBE BIND VARIABLES command and declare another kind of SQLDA called a bind descriptor to hold descriptions of the placeholders for input host variables. (Input host variables are also called bind variables.)

If your program has more than one active SQL statement (it might have OPENed two or more cursors, for example), each statement must have its own SQLDA(s). However, non-concurrent cursors can reuse SQLDAs. There is no set limit on the number of SQLDAs in a program.

DESCRIBE initializes a descriptor to hold descriptions of select-list items or input host variables.

If you supply a select descriptor, the DESCRIBE SELECT LIST statement examines each select-list item in a PREPAREd dynamic query to determine its name, datatype, constraints, length, scale, and precision. It then stores this information in the select descriptor.

If you supply a bind descriptor, the DESCRIBE BIND VARIABLES statement examines each placeholder in a PREPAREd dynamic SQL statement to determine its name, length, and the datatype of its associated input host variable. It then stores this information in the bind descriptor for your use. For example, you might use placeholder names to prompt the user for the values of input host variables.

A SQLDA is a host-program data structure that holds descriptions of select-list items or input host variables.

SQLDA variables are not defined in the Declare Section.

The select SQLDA contains the following information about a query select list:

• Maximum number of columns that can be DESCRIBEd

• Actual number of columns found by DESCRIBE

• Addresses of buffers to store column values

• Lengths of column values

• Datatypes of column values

• Addresses of indicator-variable values

• Addresses of buffers to store column names

• Sizes of buffers to store column names

• Current lengths of column names

The bind SQLDA contains the following information about the input host variables in a SQL statement:

• Maximum number of placeholders that can be DESCRIBEd

• Actual number of placeholders found by DESCRIBE

• Addresses of input host variables

• Lengths of input host variables

• Datatypes of input host variables

• Addresses of indicator variables

• Addresses of buffers to store placeholder names

• Sizes of buffers to store placeholder names

• Current lengths of placeholder names

• Addresses of buffers to store indicator-variable names

• Sizes of buffers to store indicator-variable names

• Current lengths of indicator-variable names

  See Also:

  Oracle Dynamic SQL: Method 4 for information on the SQLDA structure and variable names.

With Oracle Method 4, you generally use the following sequence of embedded SQL statements:

EXEC SQL PREPARE statement_name 
    FROM { :host_string | string_literal }; 
EXEC SQL DECLARE cursor_name CURSOR FOR statement_name; 
EXEC SQL DESCRIBE BIND VARIABLES FOR statement_name 
    INTO bind_descriptor_name; 
EXEC SQL OPEN cursor_name 
    [USING DESCRIPTOR bind_descriptor_name]; 
EXEC SQL DESCRIBE [SELECT LIST FOR] statement_name 
    INTO select_descriptor_name; 
EXEC SQL FETCH cursor_name 
    USING DESCRIPTOR select_descriptor_name; 
EXEC SQL CLOSE cursor_name; 

However, select and bind descriptors need not work in tandem. So, if the number of columns in a query select list is known, but the number of placeholders for input host variables is unknown, you can use the Method 4 OPEN statement with the following Method 3 FETCH statement:

EXEC SQL FETCH emp_cursor INTO host_variable_list; 

Conversely, if the number of placeholders for input host variables is known, but the number of columns in the select list is unknown, you can use the Method 3 OPEN statement

EXEC SQL OPEN cursor_name [USING host_variable_list]; 

with the Method 4 FETCH statement.

EXECUTE can be used for nonqueries with Method 4.

In Dynamic SQL Method 4, you cannot bind a host array to a PL/SQL procedure with a parameter of type "table."

The use of host arrays in static SQL and dynamic SQL is similar. For example, to use input host arrays with dynamic SQL Method 2, simply use the syntax

EXEC SQL EXECUTE statement_name USING host_array_list; 

where host_array_list contains one or more host arrays.

Similarly, to use input host arrays with Method 3, use the following syntax:

OPEN cursor_name USING host_array_list; 

To use output host arrays with Method 3, use the following syntax:

FETCH cursor_name INTO host_array_list; 

With Method 4, you must use the optional FOR clause to tell Oracle the size of your input or output host array.

If the PL/SQL block contains no host variables, you can use Method 1 to EXECUTE the PL/SQL string in the usual way.

If the PL/SQL block contains a known number of input and output host variables, you can use Method 2 to PREPARE and EXECUTE the PL/SQL string in the usual way.

You must put all host variables in the USING clause. When the PL/SQL string is EXECUTEd, host variables in the USING clause replace corresponding placeholders in the PREPAREd string. Though the precompiler treats all PL/SQL host variables as input host variables, values are assigned correctly. Input (program) values are assigned to input host variables, and output (column) values are assigned to output host variables.

Every placeholder in the PREPAREd PL/SQL string must correspond to a host variable in the USING clause. So, if the same placeholder appears two or more times in the PREPAREd string, each appearance must correspond to a host variable in the USING clause.

Methods 2 and 3 are the same except that Method 3 allows FETCHing. Since you cannot FETCH from a PL/SQL block, just use Method 2 instead.

If the PL/SQL block contains an unknown number of input or output host variables, you must use Method 4.

To use Method 4, you set up one bind descriptor for all the input and output host variables. Executing DESCRIBE BIND VARIABLES stores information about input and output host variables in the bind descriptor. Because the precompiler treats all PL/SQL host variables as input host variables, executing DESCRIBE SELECT LIST has no effect.