302-307 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

The Define_Column() Procedures

The Define_Column() procedures are used to define the datatype and size of the variables that will receive data from the Fetch_Rows() function:

 PROCEDURE Define_Column (c        IN    integer,                          position IN    integer,                          column   IN    number) PROCEDURE Define_Column (c           IN    integer,                          position    IN    integer,                          column      IN    varchar2,                          column_size IN    integer) PROCEDURE Define_Column (c        IN     integer,                          position IN     integer,                          column   IN     date) PROCEDURE Define_Column (c        IN     integer,                          position IN     integer,                          column   IN     mlslabel) PROCEDURE Define_Column_Char (c           IN     integer,                               position    IN     integer,                               column      IN     char,                               column_size IN     integer) PROCEDURE Define_Column_Raw (c           IN     integer,                              position    IN     integer,                              column      IN     raw,                              column_size IN     integer) PROCEDURE Define_Column_ROWID (c        IN     integer,                                position IN     integer,                                column   IN     ROWID)

The Execute() Function

The Execute() function has two purposes, depending on the type of command being executed. For a simple statement, the function executes the statement and returns the number of rows processed . For a query, the function executes the statement. This call must be followed by a call to the Fetch_Rows() function to retrieve data for an individual row. Following is the definition of the Execute() function:

 FUNCTION Execute (c IN     integer) RETURN integer

The Execute_And_Fetch() Function

The Execute_And_Fetch() function allows developers to combine a call to the Execute() function and the first subsequent call to the Fetch_Rows() function. All rows beyond the first row must still be fetched using the Fetch_Rows() function. Following is the definition of the Execute_And_Fetch() function:

 FUNCTION Execute_And_Fetch (c     IN     integer,                             exact IN     Boolean := FALSE) RETURN integer

The exact parameter instructs Oracle to raise an exception if the query returns more than one row. Even if the exception is raised, the first row of the result set is returned, and the remaining rows can be retrieved normally using the Fetch_Rows() function.

The Fetch_Rows() Function

The Fetch_Rows() function fetches a single row of data into the local buffer. This data can then be stored in local variables by using the Column_Value() procedure. Following is the definition of the Fetch_Rows() function:

 FUNCTION Fetch_Rows (c IN     integer) RETURN integer

The Open_Cursor() Function

The Open_Cursor() function is called to create a cursor that will be used when parsing and executing the dynamic statement. The function has no parameters and returns an integer value that uniquely identifies the cursor.

The Parse() Procedure

The Parse() procedure is called to send a statement to the database server to check for syntax and semantic errors. If necessary, Oracle also determines an execution plan for the statement. Following is the definition of the Parse() procedure:

 PROCEDURE Parse (c             IN     integer,                  statement     IN     varchar2,                  language_flag IN     integer)

The c parameter is the integer value that identifies the cursor opened by the call to the Open_Cursor() function. The statement parameter holds the dynamic command that will be parsed. The language_flag parameter holds an integer value. The valid values for this parameter are shown in Table 9.5.

**Table 9.5** Valid values for the **language_flag** parameter of the **DBMS_SQL.Parse** ( ) procedure.
DBMS_SQL Constant	Integer Value	Description

V6		Oracle6 behavior
V7	2	Oracle7 behavior
NATIVE	1	Behavior appropriate to the current database version

The Variable_Value() Procedures

The Variable_Value() procedures are used to determine the new values for bind variables that are modified by a dynamic SQL statement. The definitions of these procedures are as follows :

 PROCEDURE Variable_Value (c     IN     integer,                           name  IN     varchar2,                           value    OUT number) PROCEDURE Variable_Value (c     IN     integer,                           name  IN     varchar2,                           value    OUT varchar2) PROCEDURE Variable_Value (c     IN     integer,                           name  IN     varchar2,                           value    OUT date) PROCEDURE Variable_Value (c     IN     integer,                           name  IN     varchar2,                           value    OUT mlslabel) PROCEDURE Variable_Value_Char (c     IN     integer,                                name  IN     varchar2,                                value    OUT char) PROCEDURE Variable Value_Raw (c     IN     integer,                               name  IN     varchar2,                               value    OUT raw) PROCEDURE Variable_Value_ROWID (c    IN     integer,                                 name IN     varchar2,                                 value   OUT ROWID)

Table of Contents