Dynamic SQL in Oracle

 What Is Dynamic SQL?

Dynamic SQL enables you to write programs that reference SQL statements whose full text is not known until runtime.

Why Use Dynamic SQL?

You should use dynamic SQL in cases where static SQL does not support the operation you want to perform, or in cases where you do not know the exact SQL statements that must be executed by a PL/SQL procedure. These SQL statements may depend on user input, or they may depend on processing work done by the program.

Dynamic SQL makes optimal use of the distribution statistics accumulated by RUNSTATS. Because the values are available when the optimizer determines the access path, it can arrive at a better solution for accessing the data. Static SQL, on the other hand, cannot use these statistics unless all predicate values are hard-coded or REOPT(VARS) is specified.

Additionally, dynamic SQL is becoming more popular as distributed queries are being executed from non-mainframe platforms or at remote sites using distributed DB2 capabilities.

What an Application Program Using Dynamic SQL Must Do ?

    In most general case, in which you do not know in advance about the SQL statements that will execute, the program typically takes these steps:

    1.  Translates the input data, including any parameter markers, into an SQL statement

    2.  Prepares the SQL statement to execute and acquires a description of the result table

    3.  Obtains, for SELECT statements, enough main storage to contain retrieved data

    4.  Executes the statement or fetches the rows of data

    5.  Processes the information returned

    6.  Handles SQL return codes.

There are four classes of dynamic SQL:  Non-SELECT dynamic SQL , EXECUTE IMMEDIATE, Fixed-list SELECT, and Varying-list SELECT.

Dynamic SQL for Non-SELECT Statements (PREPARE AND EXECUTE)

Your program must take the following steps:

    1.  Include an SQLCA.  The requirements for an SQL communications area SQLCA) are the same as for static SQL statements.

    2.  Load the input SQL statement into a data area. If you attempt to execute an SQL statement dynamically that DB2 does not allow, you get an SQL error.

    3.  Execute the statement. 

        *   "Dynamic Execution Using PREPARE and EXECUTE"

    4.  Handle any errors that might result. The requirements are the same as those for static SQL statements. The return code from the most recently executed SQL statement appears in the host variables SQLCODE and SQLSTATE or corresponding fields of the SQLCA.

This class of dynamic SQL uses PREPARE and EXECUTE to issue SQL statements. As its name implies, non-SELECT dynamic SQL cannot issue the SELECT statement.The following pseudo-code listing shows a simple use of non-SELECT dynamic SQL that DELETEs rows from a table.

WORKING-STORAGE SECTION.

        .
        .
        .

    EXEC SQL
        INCLUDE SQLCA
    END-EXEC.

        .
        .
        .

    01  STRING-VARIABLE.
        49  STRING-VAR-LEN     PIC S9(4)   USAGE COMP.
        49  STRING-VAR-TXT     PIC X(100).

        .
        .
        .

PROCEDURE DIVISION.
        .
        .
        .

    MOVE +45 TO STRING-VAR-LEN.
    MOVE "DELETE FROM DSN88310.PROJ WHERE DEPTNO = 'A00'"
        TO STRING-VARIABLE.

    EXEC SQL
        PREPARE STMT1 FROM :STRING-VARIABLE;
    END-EXEC.

    EXEC SQL
        EXECUTE STMT1;
    END-EXEC.

        .
        .
        .

You can replace the DELETE statement in this listing with any of the following supported statements:

      ALTER               COMMENT ON        COMMIT

      CREATE            DELETE                   DROP

      EXPLAIN           GRANT                     INSERT

      LABEL ON         LOCK TABLE          REVOKE

      ROLLBACK       SET                           UPDATE

Dynamic Execution Using PREPARE and EXECUTE

You can think of PREPARE and EXECUTE as an EXECUTE IMMEDIATE done in two steps.  The first step, PREPARE, turns a character string into an SQL

    statement, and then assigns it a name of your choosing.

    For example, let the variable :DSTRING have the value "DELETE FROM

    DSN8410.EMP WHERE EMPNO = ?".  To prepare an SQL statement from that

    string and assign it the name S1, write

      EXEC SQL PREPARE S1 FROM :DSTRING END-EXEC.

    The prepared statement still contains a parameter marker, for which you

    must supply a value when the statement executes.  After the statement is

    prepared, the table name is fixed, but the parameter marker allows you to

    execute the same statement many times with different values of the

    employee number.

    EXECUTE executes a prepared SQL statement, naming a list of one or more

    host variables, or a host structure, that supplies values for all of the

    parameter markers.

    To execute the prepared statement S1 just once, using a parameter value

    contained in the host variable :EMP, write:

      EXEC SQL EXECUTE S1 USING :EMP END-EXEC

Non-SELECT dynamic SQL can use a powerful feature of dynamic SQL called a parameter marker, which is a placeholder for host variables in a dynamic SQL statement. This feature is demonstrated in the following pseudo-code: 

WORKING-STORAGE SECTION.

        .
        .
        .

    EXEC SQL INCLUDE SQLCA END-EXEC.

        .
        .
        .

    01  STRING-VARIABLE.
        49  STRING-VAR-LEN     PIC S9(4)   USAGE COMP.
        49  STRING-VAR-TXT     PIC X(100).

        .
        .
        .

PROCEDURE DIVISION.
        .
        .
        .

    MOVE +40 TO STRING-VAR-LEN.
    MOVE "DELETE FROM DSN8810.PROJ WHERE DEPTNO = ?"
        TO STRING-VARIABLE.

    EXEC SQL
        PREPARE STMT1 FROM :STRING-VARIABLE;
    END-EXEC.

    MOVE 'A00' TO TVAL.

    EXEC SQL
        EXECUTE STMT1 USING :TVAL;
    END-EXEC.

The question mark is used as a parameter marker, replacing the 'A00' in the predicate. When the statement is executed, a value is moved to the host variable (:TVAL) and is coded as a parameter to the CURSOR with the USING clause. When this example is executed, the host variable value replaces the parameter marker.

Non-SELECT dynamic SQL can provide huge performance benefits over EXECUTE IMMEDIATE. Consider a program that executes SQL statements based on an input file. A loop in the program reads a key value from the input file and issues a DELETE, INSERT, or UPDATE for the specified key. The EXECUTE IMMEDIATE class would incur the overhead of a PREPARE for each execution of each SQL statement inside the loop.

Using non-SELECT dynamic SQL, however, you can separate PREPARE and EXECUTE, isolating PREPARE outside the loop. The key value that provides the condition for the execution of the SQL statements can be substituted using a host variable and a parameter marker. If thousands of SQL statements must be executed, you can avoid having thousands of PREPAREs by using this technique. This method greatly reduces overhead and runtime and increases the efficient use of system resources.

A prepared statement can contain more than one parameter marker. Use as many as necessary to ease development.

The EXECUTE IMMEDIATE Statement

EXECUTE IMMEDIATE implicitly prepares and executes complete SQL statements coded in host variables. Only a subset of SQL statements is available when you use the EXECUTE IMMEDIATE class of dynamic SQL. The most important SQL statement that is missing is the SELECT statement. Therefore, EXECUTE IMMEDIATE dynamic SQL cannot retrieve data from tables.

To execute the statements:

      < Read a non-SELECT SQL statement into the host variable DSTRING.>

      EXEC SQL

        EXECUTE IMMEDIATE :DSTRING

      END-EXEC.

    DSTRING is a character-string host variable.  EXECUTE IMMEDIATE causes the

    statement to be prepared and executed immediately.

The following pseudo-code shows a simple use of EXECUTE IMMEDIATE that DELETEs rows from a table; the SQL statement is moved to a string variable and then executed:

WORKING-STORAGE SECTION.

        .
        .
        .

    EXEC SQL
        INCLUDE SQLCA
    END-EXEC.

        .
        .
        .

    01  STRING-VARIABLE.
        49  STRING-VAR-LEN PIC S9(4) USAGE COMP.
        49  STRING-VAR-TXT PIC X(100).

        .
        .
        .

PROCEDURE DIVISION.

        .
        .
        .

    MOVE +45 TO STRING-VAR-LEN.

    MOVE "DELETE FROM DSN8810.PROJ WHERE DEPTNO = 'A00'" TO STRING-VARIABLE.

    EXEC SQL
        EXECUTE IMMEDIATE :STRING-VARIABLE
    END-EXEC.

        .
        .
        .

You can replace the DELETE statement in this listing with any of the following supported statements:

      ALTER               COMMENT ON        COMMIT

      CREATE            DELETE                   DROP

      EXPLAIN           GRANT                     INSERT

      LABEL ON         LOCK TABLE          REVOKE

      ROLLBACK       SET                           UPDATE

In general, you should consider using EXECUTE IMMEDIATE for quick, one-time tasks. For example, the following types of programs are potential candidates:

·               A DBA utility program that issues changeable GRANT and REVOKE statements
 

·               A program that periodically generates DDL based on input parameters
 

      A parameter-driven modification program that corrects common data errors

Fixed-list SELECT Statements

    A fixed-list SELECT statement returns rows containing a known number of

    values of a known type.  When you use one, you know in advance exactly

    what kinds of host variables you need to declare in order to store the

    results.  The contrasting situation, in which you do not know in advance

    what host-variable structure you might need is Varying-list SELECT statement.

The following pseudo-code listing shows a fixed-list SELECT statement:

SQL to execute:

    SELECT  PROJNO, PROJNAME, RESPEMP
    FROM    DSN8810.PROJ
    WHERE   PROJNO   = ?
    AND     PRSTDATE = ?

    Move the "SQL to execute" to STRING-VARIABLE

    EXEC SQL DECLARE CSR2 CURSOR FOR FLSQL;
    EXEC SQL PREPARE FLSQL FROM :STRING-VARIABLE;
    EXEC SQL OPEN CSR2 USING :TVAL1, :TVAL2;

    Loop until no more rows to FETCH

    EXEC SQL
        FETCH CSR2 INTO :PROJNO, :PROJNAME, :RESPEMP;
    EXEC SQL CLOSE CSR2;

This example formulates a SELECT statement in the application program and moves it to a host variable. Next, a cursor is declared and the SELECT statement is prepared. The cursor then is opened and a loop to FETCH rows is invoked. When the program is finished, the cursor is closed. This example is simple because the SQL statement does not change. The benefit of dynamic SQL is its capability to modify the SQL statement. For example, you could move the SQL statement

    SELECT  PROJNO, PROJNAME, RESPEMP
    FROM    DSN8810.PROJ
    WHERE   RESPEMP  = ?
    AND     PRENDATE = ?

to the STRING-VARIABLE without modifying the OPEN or FETCH logic. Note that the second column of the predicate is different from the SQL statement as presented in the listing (PRENDATE instead of PRSTDATE). Because both are the same data type (DATE), however, you can use TVAL2 for both if necessary. The host variables passed as parameters in the OPEN statement must have the same data type and length as the columns in the WHERE clause. If the data type and length of the columns in the WHERE clause change, the OPEN statement must be recoded with new USING parameters.

If parameter markers are not used in the SELECT statements, the markers could be eliminated and values could be substituted in the SQL statement to be executed. No parameters would be passed in the OPEN statement.

You can recode the OPEN statement also to pass parameters using an SQLDA (SQL Descriptor Area). The SQLDA would contain value descriptors and pointers to these values. You can recode the OPEN statement as follows:

    EXEC-SQL
        OPEN CSR2 USING DESCRIPTOR :TVAL3;
    END_EXEC.

DB2 uses the SQLDA to communicate information about dynamic SQL to an application program. The SQLDA sends information such as the type of the SQL statement being executed and the number and data type of columns being returned by a SELECT statement. It can be used by fixed-list SELECT and varying-list SELECT dynamic SQL. The following code illustrates the fields of the SQLDA:

*******************************************************
***    SQLDA: SQL DESCRIPTOR AREA FOR LE COBOL      ***
*******************************************************

01  SQLDA.
    05 SQLDAID               PIC X(8)   VALUE 'SQLDA'.
    05 SQLDABC          COMP PIC S9(8)  VALUE 13216.
    05 SQLN             COMP PIC S9(4)  VALUE 750.
    05 SQLD             COMP PIC S9(4)  VALUE 0.
    05 SQLVAR OCCURS 1 TO 750 TIMES DEPENDING ON SQLN.
        10 SQLTYPE      COMP PIC S9(4).
            88 SQLTYPE-BLOB             VALUE 404 405.
            88 SQLTYPE-CLOB             VALUE 408 409.
            88 SQLTYPE-DBCLOB           VALUE 412 413.
            88 SQLTYPE-FLOAT            VALUE 480 481.
            88 SQLTYPE-DECIMAL          VALUE 484 485.
            88 SQLTYPE-SMALLINT         VALUE 500 501.
            88 SQLTYPE-INTEGER          VALUE 496 497.
            88 SQLTYPE-DATE             VALUE 384 385.
            88 SQLTYPE-TIME             VALUE 388 389.
            88 SQLTYPE-TIMESTAMP        VALUE 392 393.
            88 SQLTYPE-CHAR             VALUE 452 453.
            88 SQLTYPE-VARCHAR          VALUE 448 449.
            88 SQLTYPE-LONG-VARCHAR     VALUE 456 457.
            88 SQLTYPE-VAR-ONUL-CHAR    VALUE 460 461.
            88 SQLTYPE-GRAPHIC          VALUE 468 469.
            88 SQLTYPE-VARGRAPH         VALUE 464 465.
            88 SQLTYPE-LONG-VARGRAPH    VALUE 472 473.
            88 SQLTYPE-ROWID            VALUE 904 905.
            88 SQLTYPE-BLOB-LOC         VALUE 961 962.
            88 SQLTYPE-CLOB-LOC         VALUE 964 965.
            88 SQLTYPE-DBCLOB-LOC       VALUE 968 969.
        10 SQLLEN       COMP PIC S9(4).
        10 SQLDATA           POINTER.
        10 SQLIND            POINTER.
        10 SQLNAME.
            15 SQLNAMEL COMP PIC S9(4).
            15 SQLNAMEC COMP PIC X(30).
 

A description of the contents of the SQLDA fields is in the discussion of the next class of dynamic SQL, which relies heavily on the SQLDA.

The fourth and final class of dynamic SQL is varying-list SELECT. This class of dynamic SQL can be used to explicitly prepare and execute SQL SELECT statements when you do not know in advance which columns will be retrieved by an application program.

What Your Application Program Must Do to execute a fixed-list SELECT statement dynamically ?

your program must:

    1.  Include an SQLCA

    2.  Load the input SQL statement into a data area

    3.  Declare a cursor for the statement name.

        Dynamic SELECT statements cannot use INTO; hence, you must use a cursor to

        put the results into host variables.  In declaring the cursor, use the

        statement name (call it STMT), and give the cursor itself a name (for

        example, C1):

        EXEC SQL DECLARE C1 CURSOR FOR STMT END-EXEC.

    4.  Prepare the statement

        Prepare a statement (STMT) from DSTRING.  Here is one possible PREPARE

        statement:

        EXEC SQL PREPARE STMT FROM :DSTRING END-EXEC.

    5.  Open the cursor.

        The OPEN statement evaluates the SELECT statement named STMT.  For

        example:

        Without parameter markers:  EXEC SQL OPEN C1 END-EXEC.

        If STMT contains parameter markers, then you must use the USING clause of

        OPEN to provide values for all of the parameter markers in STMT.  If there

        are four parameter markers in STMT, you need:

        EXEC SQL OPEN C1 USING :PARM1, :PARM2, :PARM3, :PARM4 END-EXEC.

    6.  Fetch rows from the result table.

        Your program could repeatedly execute a statement such as this:

        EXEC SQL FETCH C1 INTO :NAME, :PHONE END-EXEC.

    7.  Close the cursor.

        This step is the same as for static SQL. 

        EXEC SQL CLOSE C1 END-EXEC.

    8.  Handle any resulting errors.  This step is the same as for static SQL,

        except for the number and types of errors that can result.

         

Varying-list SELECT Statements

Varying-list SELECT provides the most flexibility for dynamic SELECT statements. You can change tables, columns, and predicates "on-the-fly." Keep in mind though, because everything about the query can change during one invocation of the program, the number and type of host variables needed to store the retrieved rows cannot be known beforehand. The lack of knowledge regarding what is being retrieved adds considerable complexity to your application programs.

The code differs from fixed-list SELECT in three ways: The PREPARE statement uses the SQLDA, the FETCH statement uses the SQLDA, and a step is added to store host variable addresses in the SQLDA.

When PREPARE is executed, DB2 returns information about the columns being returned by the SELECT statement. This information is in the SQLVAR group item of the SQLDA. Of particular interest is the SQLTYPE field. For each column to be returned, this field indicates the data type and whether NULLs are permitted. Note that in the SQLDA layout presented previously, all possible values for SQLTYPE are coded as 88-level COBOL structures. They can be us used in the logic of your application program to test for specific data types.

You can also code dynamic SQL without knowing anything about the statement to be executed. An example is a program that must read SQL statements from a terminal and execute them regardless of statement type. You can create this type of program by coding two SQLDAs: one full SQLDA and one minimal SQLDA (containing only the first 16 bytes of the full SQLDA) that PREPAREs the statement and determines whether it is a SELECT. If the statement is not a SELECT, you can simply EXECUTE the non-SELECT statement. If it is a SELECT, PREPARE it a second time with a full SQLDA and follow the steps in the following pseudo-code listing:

EXEC SQL INCLUDE SQLDA
EXEC SQL INCLUDE MINSQLDA

Read "SQL to execute" from external source

Move the "SQL to execute" to STRING-VARIABLE

EXEC SQL DECLARE CSR3 CURSOR FOR VLSQL;

EXEC SQL
    PREPARE VLSQL INTO MINSQLDA FROM :STRING-VARIABLE;

IF SQLD IN MINSQLDA = 0
    EXECUTE IMMEDIATE (SQL statement was not a SELECT)
    FINISHED.

EXEC SQL
    PREPARE VLSQL INTO SQLDA FROM :STRING-VARIABLE;
EXEC SQL OPEN CSR3;

Load storage addresses into the SQLDA

Loop until no more rows to FETCH
    EXEC SQL FETCH CSR3 USING DESCRIPTOR SQLDA;

EXEC SQL CLOSE CSR3;