Oracle7 Server Application Developer's Guide

Execution Flow

The typical flow of procedure calls is shown in Figure 11-1. A general explanation of these procedures follows. Each of these procedures is described in greater detail starting

Figure 10 - 1. DBMS_SQL Execution Flow

OPEN_CURSOR

To process a SQL statement, you must have an open cursor. When you call the OPEN_CURSOR function, you receive a cursor ID number for the data structure representing a valid cursor maintained by Oracle. These cursors are distinct from cursors defined at the precompiler, OCI, or PL/SQL level, and are used only by the DBMS_SQL package.

PARSE

Every SQL statement must be parsed by calling the PARSE procedure. Parsing the statement checks the statement's syntax and associates it with the cursor in your program. A complete explanation of how SQL statements are parsed is included in the Oracle7 Server Tuning manual.

You can parse any data manipulation language or data definition language statements. Data definition language statements are executed on the parse, which performs the implied commit.

Attention: When parsing a data definition language statement to drop a package or a procedure, a deadlock can occur if a procedure in the package is still in use by you. After a call to a procedure, that procedure is considered to be in use until execution has returned to the user side. Any such deadlock will timeout after five minutes.

BIND_VARIABLE

Many data manipulation language statements require that data in your program be input to Oracle. When you define a SQL statement that contains input data to be supplied at runtime, you must use placeholders in the SQL statement to mark where data must be supplied.

For each placeholder in the SQL statement, you must call the BIND_VARIABLE procedure to supply the value of a variable in your program to the placeholder. When the SQL statement is subsequently executed, Oracle uses the data that your program has placed in the output and input, or bind, variables.

DEFINE_COLUMN

The columns of the row being selected in a SELECT statement are identified by their relative positions as they appear in the select list, from left to right. For a query, you must call DEFINE_COLUMN to specify the variables that are to receive the SELECT values, much the way an INTO clause does for a static query.

DEFINE_COLUMN_LONG

You use the DEFINE_COLUMN_LONG procedure to define LONG columns, in the same way that DEFINE_COLUMN is used to define non-LONG columns. You must call DEFINE_COLUMN_LONG before using the COLUMN_VALUE_LONG to fetch from the LONG column.

EXECUTE

Call the EXECUTE function to execute your SQL statement.

FETCH_ROWS

Call FETCH_ROWS to retrieve the rows that satisfy the query. Each successive fetch retrieves another row, until the fetch is unable to retrieve anymore rows. Instead of calling EXECUTE and then FETCH_ROWS, you may find it more efficient to call EXECUTE_AND_FETCH if you are calling EXECUTE for a single iteration.

VARIABLE_VALUE

For queries, call COLUMN_VALUE to determine the value of a column retrieved by the FETCH_ROWS call. For anonymous blocks containing calls to PL/SQL procedures, call VARIABLE_VALUE to retrieve the values assigned to the output variables of the PL/SQL procedures when they were executed.

COLUMN_VALUE_LONG

To fetch just part of a LONG database column (which can be up to two gigabytes in size), you use the COLUMN_VALUE_LONG procedure. You can specify the offset (in bytes) into the column value, and the number of bytes to fetch.

CLOSE_CURSOR

When you no longer need a cursor for a session, close the cursor by calling CLOSE_CURSOR. If you are using an Oracle Open Gateway, you may need to close cursors at other times as well. Consult your Oracle Open Gateway documentation for additional information. If you neglect to close a cursor, the memory used by that cursor remains allocated even though it is no longer needed.