Oracle7 Server Application Developer's Guide

Using DBMS_SQL

The ability to use dynamic SQL from within stored procedures generally follows the model of the Oracle Call Interface (OCI). You should refer to the Programmer's Guide to the Oracle Call Interface for additional information on the concepts presented in this chapter.

PL/SQL differs somewhat from other common programming languages, such as C. For example, addresses (also called pointers) are not user visible in PL/SQL. As a result, there are some differences between the Oracle Call Interface and the DBMS_SQL package. These differences include the following:

The OCI uses bind by address, while the DBMS_SQL package uses bind by value.

With DBMS_SQL you must call VARIABLE_VALUE to retrieve the value of an OUT parameter for an anonymous block, and you must call COLUMN_VALUE after fetching rows to actually retrieve the values of the columns in the rows into your program.

The current release of the DBMS_SQL package does not provide DESCRIBE or CANCEL cursor procedures, nor support for the array interface.

Indicator variables are not required because nulls are fully supported as values of a PL/SQL variable.

A sample usage of the DBMS_SQL package is shown below. For users of the Oracle Call Interfaces, this code should seem fairly straightforward. Each of the functions and procedures used in this example is described later in this chapter. A more detailed example, which shows how you can use the DBMS_SQL package to build a query statement dynamically, begins

. This example does not actually require the use of dynamic SQL, because the text of the statement is known at compile time. However, it illustrates the concepts of this package.

/* The DEMO procedure deletes all of the employees from the EMP 
 * table whose salaries are greater than the salary that you 
 * specify when you run DEMO. */

CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS
    cursor_name INTEGER;
    rows_processed INTEGER;
BEGIN
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name, 'DELETE FROM emp WHERE sal > :x',
                   dbms_sql.v7);
    dbms_sql.bind_variable(cursor_name, ':x', salary);
    rows_processed := dbms_sql.execute(cursor_name);
    dbms_sql.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
    dbms_sql.close_cursor(cursor_name);
END;