DBMS_SQL

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.