16.5. Dynamic SQL in Oracle V.6

16.5. Dynamic SQL in Oracle V.6

The SQL statement set described in the SQL / 89 standard is intended to be embedded in a program in a conventional programming language. Therefore, in this set, the operators of the "true" relational query language are mixed (for example, the operator of deleting parts of the table from the table that satisfy the specified value) and operators of working with cursors, which allow to provide line-by-line access to the result table of the query.

It is clear that in the dialogue mode, a set of SQL statements and their syntax should be somewhat different. The whole question is how to implement such an interactive program. The rules for embedding standard SQL into a program in a conventional programming language stipulate that all information relating to SQL statements is known in statics (with the exception of the values ​​of variables used as constants in SQL statements). There are no standard means of compilation followed by the execution of statements that become known only at runtime (for example, they are entered from the terminal). Therefore, relying only on the standard, it is impossible to implement a dialogue monitor of interaction with a database in SQL or another application program in which the text of SQL statements occurs at run time, i.e. in fact, one way or another, the standard needs to be expanded.

One of the possible ways of extension is to use a special group of statements that provide dynamic compilation (during the execution of an application program) of a basic subset of SQL statements and support their correct execution. Some of these statements were part of the SQL dialect implemented in System R, a slightly different set is included in the Oracle V.6 implementation, and finally, the standard version of dynamic SQL appeared in the new SQL / 92 standard.

Since Oracle's dynamic SQL tools have been implemented for a relatively long time, it makes sense to first consider them in order to have a basis for comparison with SQL / 92.

An additional set of statements that support dynamic compilation of basic SQL statements include the following statements: PREPARE, DESCRIBE, and EXECUTE.

16.5.1. Training operator

The PREPARE statement has the syntax:

  <prepare-statement> :: =
    PREPARE <statement-name> FROM <host-string-variable>
 <statement-name> :: = <name> 

During the execution of the PREPARE statement, the character string contained in host-string-variable is passed to the SQL compiler, which processes it in much the same way as it would if received in statics. The code constructed during the execution of the PREPARE statement remains valid until the end of the transaction or until the repeated execution of the given PREPARE statement within the same transaction.

Unlike statically substituted in the program in the usual programming language of SQL statements, in which the connection with variables of the inclusive program is done by name (i.e. according to the standard, the variable names of the inclusive program can be used in the embedded SQL statement), the dynamic nature of statements prepared using the PREPARE operator, forces us to consider these names as the names of formal parameters. The correspondence of these formal parameters to the addresses of variables of the inclusive program is established positionally during the execution of the prepared statement.

16.5.2. Operator get the description of the prepared operator

The DESCRIBE statement is designed to determine the type of a previously prepared statement, find out the number and types of formal parameters (if any) and the number and types of columns of the resulting table, if the prepared statement is a select statement (SELECT).

The action of the DESCRIBE operator is that the information characterizing the previously prepared operator with the specified name is placed in the specified area of ​​the application program memory (the structure of this area is fixed and known to users).

16.5.3. Operator perform the prepared statement

The EXECUTE statement is used to execute a previously prepared SQL statement of the 'N' type (which does not require the use of a cursor) or for the combined preparation and execution of such a statement. EXECUTE statement syntax:

  <execute-statement> :: =
    EXECUTE
  {<statement-name> [USING <host-vars-list>]
  (IMMEDIATE <host-string-variable>} 

To execute a prepared statement, use the first version of the EXECUTE statement. In this case, the <statement-name> must specify the name previously used in the PREPARE statement. If formal parameters are present in the prepared statement, then the list of actual parameters <host-vars-list> should be specified in the EXECUTE statement. The number and types of actual parameters should correspond to the number and types of formal parameters of the prepared operator.

The second variant of the EXECUTE statement is intended in Oracle for the combined preparation and execution of the SQL operator of the 'N' type. In this case, the parameter of the EXECUTE operator is a string that must contain the text of the SQL statement (this string is also allowed to be specified literally). It is prohibited to use in this statement variables of an inclusive program (formal parameters).

16.5.4. Working with dynamic SQL statements through cursors

To use such operators, an extension of the SQL standard cursors mechanism is used. First, when defining a cursor, you can specify not only the literal specification of the cursor, but also the operator name entered using the PREPARE operator (in this case, the PREPARE operator must be textually above the DECLARE operator). Thus, the full syntax of the DECLARE statement becomes the following:

  <declare cursor> :: =
    DECLARE <cursor name> CURSOR
    FOR {<cursor specification> |  <statement-name>} 

Further, since information on the input and output variables of the inclusive program is not known for such a cursor in a static, another form of OPEN and FETCH operators is used.

The full syntax of these statements is as follows:

  <open statement> :: =
    OPEN <cursor name>
   [USING {<host-vars-list> |  DESCRIPTOR <descr-name>}]
 <fetch statement> :: =
    FETCH <cursor name>
  {INTO <fetch target list>
  (USING <host-vars-list>
  (USING DESCRIPTOR <descr-name>} 

As you can see, there are two ways to set the actual input and output parameters: direct with indication of the names of variables of the enabling program in OPEN and / or FETCH, and indirectly, when the number of parameters and their addresses are communicated via an additional descriptor structure.

The first method is proposed to be used to work with sample operators, for which a set of formal input and output parameters is fixed. More specifically, as regards the output parameters, the number and types of elements in the selection list should be fixed.

The second way to work with dynamically compiled operators requiring the use of cursors is to use descriptors of dynamically generated parameter lists. In this case, the entire responsibility for the conformity of the types of actual and formal parameters lies with the programmer. As a result of an error in forming such a list, in particular, the memory of the C program may be corrupted.