 |
|
DBMS_SQL Package
Oracle Tips by
Burleson
|
The DBMS_SQL package allows developers to write stored
PL/SQL code that is capable of generating and executing data-specific
DDL and DML statements without using hard-coded data values. There are
three different types of dynamic SQL that can be built:
-
DDL commands
-
Nonquery DML statements (DELETE,
INSERT, or UPDATE statement)
-
DML queries (SELECT statement)
Each of these operations has separate calls to procedures and
functions contained in the DBMS_SQL package. In the end, the
single steps can be broken down into a generic set of steps:
1. Build a
command by concatenating strings together.
2. Open a
cursor.
3. Parse the
command.
4. Bind any
input variables.
5. Execute the
command.
6. Fetch the
results (in the case of queries).
7. Close the
cursor.
There are a number of procedures and functions contained within the
DBMS_SQL package.
The Bind_Variable() Procedures
The Bind_Variable() group of procedures is used to associate
values with bind variables in the command that is being built. There
are several implementations of this functionality:
PROCEDURE Bind_Variable (c IN integer,
name IN varchar2,
value IN number)
PROCEDURE Bind_Variable (c IN integer,
name IN varchar2,
value IN varchar2)
PROCEDURE Bind_Variable (c IN integer,
name IN varchar2,
value IN varchar2,
out_value_size IN integer)
PROCEDURE Bind_Variable (c IN integer,
name IN varchar2,
value IN date)
PROCEDURE Bind_Variable (c IN integer,
name IN varchar2,
value IN mlslabel)
There are several other implementations of the Bind_Variable()
procedure with slightly different names— Bind_Variable_Char(),
Bind_Variable_Raw(), and Bind_Variable_ROWID :
PROCEDURE Bind_Variable_Char (c IN integer,
name IN varchar2,
value IN char)
PROCEDURE Bind_Variable_Char (c IN integer,
name IN varchar2,
value IN char,
out_value_size IN integer)
PROCEDURE Bind_Variable_Raw (c IN integer,
name IN varchar2,
value IN raw)
PROCEDURE Bind_Variable_Raw (c IN integer,
name IN varchar2,
value IN raw,
out_value_size IN integer)
PROCEDURE Bind_Variable_ROWID (c IN integer,
name IN varchar2,
value IN ROWID)
While each of these procedures has a slightly different name, each
of them accomplishes the same task—namely, storing a value in a bind
variable.
The Close_Cursor() Procedure
The Close_Cursor() procedure is called to free up the
resources used by a cursor. The procedure accepts a single parameter:
PROCEDURE Close_Cursor (c IN OUT integer)
The c parameter is a cursor ID number. The parameter returns
from the procedure as NULL.
The Column_Value() Procedures
Like the Bind_Variable() procedure, there are several
implementations of the Column_Value() procedure:
PROCEDURE Column_Value (c IN integer,
position IN integer,
value OUT number)
PROCEDURE Column_Value (c IN integer,
position IN integer,
value OUT varchar2)
PROCEDURE Column_Value (c IN integer,
position IN integer,
value OUT date)
PROCEDURE Column_Value (c IN integer,
position IN integer,
value OUT mlslabel)
PROCEDURE Column_Value (c IN integer,
position IN integer,
value OUT number,
column_error OUT number,
actual_length OUT number)
PROCEDURE Column_Value (c IN integer,
position IN integer,
value OUT varchar2,
column_error OUT number,
actual_length OUT number)
PROCEDURE Column_Value (c IN integer,
position IN integer,
value OUT mlslabel,
column_error OUT number,
actual_length OUT number)
PROCEDURE Column_Value_Char (c IN integer,
position IN integer,
value OUT char)
PROCEDURE Column_Value_Char (c IN integer,
position IN integer,
value OUT char,
column_error OUT number,
actual_length OUT number)
PROCEDURE Column_Value_Raw (c IN integer,
position IN integer,
value OUT raw)
PROCEDURE Column_Value_Raw (c IN integer,
position IN integer,
value OUT raw,
column_error OUT number,
actual_length OUT number)
PROCEDURE Column_Value_ROWID (c IN integer,
position IN integer,
value OUT ROWID)
PROCEDURE Column_Value_ROWID (c IN integer,
position IN integer,
value OUT ROWID,
column_error OUT number,
actual_length OUT number)
All of these procedures return the value of a column that was
fetched using a call to the Fetch_Rows() function. The column’s
value is stored in the value parameter.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
