DBMS_SQL

The steps to execute a SQL statement using DBMS_SQL are as follows:

1.  Store the SQL in a string.

2.  Open a cursor using OPEN_CURSOR.

3.  Use PARSE to parse the block out of the string.

4.  Bind any variables using BIND_VARIABLE.

5.  For anything other than a query, use EXECUTE and/or VARIABLE_VALUE to execute the statement and CLOSE_CURSOR to close the cursor.

6.  For queries, use DEFINE_COLUMN to set up variables for the output.

7.  Use EXECUTE to execute the query and FETCH_ROWS, COLUMN_VALUE, and VARIABLE_VALUE to retrieve the data.

8.  Use CLOSE_CURSOR.

FUNCTION OPEN_CURSOR

RETURN INTEGER;

Use OPEN_CURSOR to open a new cursor in which to execute the SQL statement and retrieve a cursor ID number for future references to it. Typically we think of cursors as storage for the results of a query, but DBMS_SQL requires a cursor to store SQL statements you plan to issue whether they are meant to return data or not. When you parse a statement it overwrites the contents of the cursor, so you can use the same cursor to execute as many statements as you like. When you are finished, you must close all cursors using CLOSE_CURSOR.

PROCEDURE PARSE(

     c IN INTEGER,

     statement IN VARCHAR2,

     language_flag IN INTEGER);

The PARSE procedure is used to send the SQL statement to the database for parsing. The maximum length of the statement—size_t—is operating system-dependent. For large SQL statements, use the PARSE procedure.

---

Note:  
Parsing happens immediately when the SQL statement is issued, and cannot be deferred until execution time as it can when using the Oracle Call Interface (OCI). Deferred parsing is useful for reducing network traffic between the application and the Oracle server, because only one call is necessary to perform both parsing and execution of statements. With immediate parsing, two calls are needed—one to parse and one to execute. Support for deferred parsing will eventually be included in DBMS_SQL, so applications should not rely on this immediate parsing behavior.

---

PROCEDURE PARSE(

     c IN INTEGER,

     statement IN VARCHAR2S,

     lb IN INTEGER,

     ub IN INTEGER,

     lfflg IN BOOLEAN,

     language_flag IN INTEGER);

This procedure accepts a PL/SQL table of type VARCHAR2S and concatenates its contents into a string before parsing. You can use this to pass statements or blocks that will not fit into a single VARCHAR2. The definition of VARCHAR2S is as follows:

TYPE VARCHAR2S

     IS TABLE OF VARCHAR2(256)

INDEX BY BINARY_INTEGER;



PROCEDURE BIND_VARIABLE(

     c IN INTEGER,

     name IN VARCHAR2,

     value IN <datatype>

     out_value_size IN INTEGER);

<datatype> can be any one of the following types:

NUMBER

DATE

MLSLABEL

VARCHAR2 CHARACTER SET ANY_CS

BLOB

CLOB CHARACTER SET ANY_CS

BFILE

In order to bind CHAR, RAW, and ROWID data, use the following procedures:

BIND_VARIABLE_CHAR

BIND_VARIABLE_RAW

BIND_VARIABLE_ROWID

These procedures take the same variables as BIND_VARIABLE, except that BIND_VARIABLE_ROWID does not support out_value_size.

The BIND_VARIABLE procedure is used to assign values to bind variables in the SQL statement. Precede the name of bind variables with a colon. Each variable will be replaced with the value specified for it as many times as it occurs.

PROCEDURE DEFINE_COLUMN(

     c IN INTEGER,

     position IN INTEGER

     column IN <datatype>

     column_size in INTEGER);

<datatype> can be any one of the following types:

NUMBER

DATE

MLSLABEL

BLOB

CLOB CHARACTER SET ANY_CS

BFILE