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

 

 

   
   
 

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.

  
 

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