299-302 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

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.

Table of Contents