Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
   
 

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter