Previous | Table of Contents | Next |
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:
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:
There are a number of procedures and functions contained within the DBMS_SQL package.
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 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 .
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.
Previous | Table of Contents | Next |