 |
|
The Define_Column()
Procedures
Oracle Tips by
Burleson
|
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 |
0 |
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)
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.
|
