The following versions are allowed:

• NATIVE—Native handling for the server
• V6—Handling for Oracle version 6.x
• V7—Handling for Oracle version 7.x
• V8—Handling for Oracle version 8.x

The version can be expressed as DBMS_SQL.NATIVE,DBMS_SQL.V8, and so on.

Binding Variables for Queries
There is no difference between binding variables for queries and binding variables for DDL and DML statements. Binding enables you to bind a specific variable to a placeholder, which is identified by a colon. Because you can bind all types of PL/SQL variables, the overloaded BIND_VARIABLE procedure can handle many datatypes.

The Syntax for Binding Variables
The syntax for the NUMBER datatype is

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN NUMBER);

The two syntaxes for type VARCHAR2 are

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN VARCHAR2);

and

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN VARCHAR2,
                          out_value_size IN INTEGER);

The two syntaxes for type CHAR2 are

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN CHAR2);

and

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN CHAR2,
                          out_value_size IN INTEGER);

The syntax for the DATE datatype is

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN DATE);

The two syntaxes for type RAW are

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN RAW);

and

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN RAW,
                          out_value_size IN INTEGER);

The syntax for ROWID is

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN ROWID);

Finally, there is support for MLSLABEL in Trusted Oracle with

PROCEDURE BIND_VARIABLE(cursor_id IN INTEGER,
                          placeholder_name IN VARCHAR2,
                          value IN MLSLABEL);

As you can see, there is support for almost any conceivable datatype.

cursor_id is the ID returned from OPEN_CURSOR. placeholder_name is the name of the variable preceded by a colon. value is the value to be bound (assigned) to the placeholder variable name. For any of the procedures with out_value_size, this is the output size expressed in bytes for those datatypes.

Defining the Output Variables for Queries
The difference in processing of DML and DDL statements compared to queries occurs at this point. The DML and DDL statements would simply execute, and then the cursor would be closed. However, you are now writing a query with output. You have to define everything to the DBMS_SQL package including output, which uses the DEFINE_COLUMN procedure. You first have to declare variables identical in column datatypes and lengths. You can then call DEFINE_COLUMN to specify the column order and the variable used for output. Because you can define several datatypes, the procedure DEFINE_COLUMN is overloaded.

The Syntax for Defining Output Variables
The syntax for NUMBER is

PROCEDURE DEFINE_COLUMN (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_name IN NUMBER);

The syntax for VARCHAR2 is

PROCEDURE DEFINE_COLUMN (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_name IN VARCHAR2,
                                column_size IN INTEGER);

The syntax for CHAR is

PROCEDURE DEFINE_COLUMN (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_name IN CHAR,
                                column_size IN INTEGER);

The syntax for DATE is

PROCEDURE DEFINE_COLUMN (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_name IN DATE);

The syntax for RAW is

PROCEDURE DEFINE_COLUMN (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_name IN RAW,
                                column_size IN INTEGER);

The syntax for ROWID is

PROCEDURE DEFINE_COLUMN (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_name IN ROWID);

The syntax for MLSLABEL in Trusted Oracle is

PROCEDURE DEFINE_COLUMN (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_name IN MLSLABEL);

cursor_id is the cursor assigned by the OPEN_CURSOR statement. column_order is the order of the columns, starting from position 1. column_name is the variable defined in the declaration that is associated with the column in the table. The optional column_size allows you to specify the size of the column; otherwise, the default is the length of the variable name.

Executing Statements for Queries
The execute procedure is identical to DDL and DML statements. After you have defined the columns, you are ready to execute the query with the function EXECUTE.

The Syntax for the EXECUTE Function

FUNCTION EXECUTE (cursor_id IN INTEGER) RETURN INTEGER;

The cursor_id is the cursor assigned by the OPEN_CURSOR statement. The function also returns an integer that is valid only for DML statements, which returns the number of rows processed. The applicable DML statements are INSERT, UPDATE, and DELETE. This value should be ignored for all other purposes.

Fetching the Rows into the Buffer with Queries
After the query is executed, you need to store the results into the buffer by using FETCH_ROWS. This function returns the number of rows stored into the buffer. You can then error-check or process based upon %FOUND or %NOTFOUND.

The Syntax for the FETCH_ROWS Function

FUNCTION FETCH_ROWS (cursor_id IN INTEGER) RETURN INTEGER;

Using EXECUTE_AND_FETCH to Retrieve the First Set of Rows
Instead of first running EXECUTE and then executing FETCH_ROWS, you can do the initial execution and fetching in one step with the use of the function EXECUTE_AND_FETCH.

The Syntax for the EXECUTE_AND_FETCH Function

FUNCTION EXECUTE_AND_FETCH (cursor_id IN INTEGER,
                                  total_rows IN BOOLEAN DEFAULT FALSE)
    RETURN INTEGER;

The only difference is the added parameter total_rows. The value returns true if more than one row has been fetched. You can still process all these rows with no problem.

Using COLUMN_VALUE to Read the Results into PL/SQL Variables
You now need to read the data from the buffer into variables, which you can then process with the use of the procedure COLUMN_VALUE. Because the procedure is overloaded, it can handle the various datatypes.

The Syntax for the COLUMN_VALUE Procedure
The syntaxes of the two possible calls for type NUMBER are

PROCEDURE COLUMN_VALUE (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_variable OUT NUMBER);

and

PROCEDURE COLUMN_VALUE (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_variable OUT NUMBER,
                                column_error OUT NUMBER,
                                actual_length OUT INTEGER);

The formats for VARCHAR2 are

PROCEDURE COLUMN_VALUE (cursor_id IN INTEGER,
                                column_order IN INTEGER,
                                column_variable OUT VARCHAR2);

and