6 DBMS_SQL

Using DBMS_SQL

• Overview

• Security model

• Constants

• Exceptions

• Operational notes

• Examples

Overview

TimesTen lets you write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. Dynamic SQL statements are not embedded in your source program; rather, they are stored in character strings that are input to, or built by, the program at runtime. This functionality enables you to create more general-purpose procedures. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.

Native dynamic SQL is an alternative to DBMS_SQL that lets you place dynamic SQL statements directly into PL/SQL blocks. In most situations, native dynamic SQL is easier to use and performs better than DBMS_SQL. However, native dynamic SQL itself has certain limitations, such as there being no support for so-called Method 4 (for dynamic SQL statements with an unknown number of inputs or outputs). Also, there are some tasks that can only be performed using DBMS_SQL.

The ability to use dynamic SQL from within stored procedures generally follows the model of the Oracle Call Interface (OCI). See Oracle Call Interface Programmer's Guide for information about OCI.

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 OCI and the DBMS_SQL package, including the following:

• OCI binds by address, while the DBMS_SQL package binds 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 CANCEL cursor procedures.

• Indicator variables are not required, because NULL is fully supported as a value of a PL/SQL variable.

Security model

DBMS_SQL is owned by SYS and compiled with AUTHID CURRENT_USER. Any DBMS_SQL subprogram called from an anonymous PL/SQL block is run using the privileges of the current user.

See "Definer's rights and invoker's rights" in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for information about the AUTHID clause.

Preventing malicious or accidental access of open cursor numbers

An error, ORA-29471, is raised when any DBMS_SQL subprogram is called with a cursor number that does not denote an open cursor. When the error is raised, an alert is issued to the alert log and DBMS_SQL becomes inoperable for the life of the session.

If the actual value for the cursor number in a call to the IS_OPEN function denotes a cursor currently open in the session, the return value is TRUE. If the actual value is NULL, then the return value is FALSE. Otherwise, this raises an ORA-29471 error.

Note that the OPEN_CURSOR function is the only DBMS_SQL subprogram that has no formal parameter for the cursor number. Instead, it returns a cursor number. Therefore it is not within the scope of these rules.

Preventing inappropriate use of a cursor

Cursors are protected from security breaches that subvert known existing cursors.

Checks are made when binding and executing. Optionally, checks may be performed for every single DBMS_SQL subprogram call. The check is as follows:

• The current_user is the same on calling the subprogram as it was on calling the most recent parse.

• The enabled roles on calling the subprogram must be a superset of the enabled roles on calling the most recent parse.

Consistent with the use of definer's rights subprograms, roles do not apply.

If either check fails, an ORA-29470 error is raised.

The mechanism for defining when checks are performed is a new overload for the OPEN_CURSOR subprogram which takes a formal parameter, security_level, with allowed values NULL, 1, and 2.

• When security_level = 1 (or is NULL), the checks are made only when binding and executing.

• When security_level = 2, the checks are always made.

Constants

The constants described in Table 6-1 are used with the language_flag parameter of the PARSE procedure. For TimesTen, use NATIVE.

Operational notes

• Execution flow

• Processing queries

• Processing updates, inserts, and deletes

• Locating errors

Execution flow

1. OPEN_CURSOR

2. PARSE

3. BIND_VARIABLE or BIND_ARRAY

4. DEFINE_COLUMN or DEFINE_ARRAY

5. EXECUTE

6. FETCH_ROWS or EXECUTE_AND_FETCH

7. VARIABLE_VALUE or COLUMN_VALUE

8. CLOSE_CURSOR

OPEN_CURSOR

To process a SQL statement, you must have an open cursor. When you call the OPEN_CURSOR function, you receive a cursor ID number for the data structure representing a valid cursor maintained by TimesTen. These cursors are distinct from cursors defined at the precompiler, OCI, or PL/SQL level, and are used only by the DBMS_SQL package.

PARSE

Every SQL statement must be parsed by calling the PARSE procedure. Parsing the statement checks the statement syntax and associates it with the cursor in your program.

You can parse any DML or DDL statement. DDL statements are run on the parse, which performs the implied commit.

The execution flow of DBMS_SQL is shown in Figure 6-1 that follows.

Figure 6-1 DBMS_SQL execution flow

Description of "Figure 6-1 DBMS_SQL execution flow"

BIND_VARIABLE or BIND_ARRAY

Many DML statements require that data in your program be input to TimesTen. When you define a SQL statement that contains input data to be supplied at runtime, you must use placeholders in the SQL statement to mark where data must be supplied.

For each placeholder in the SQL statement, you must call a bind procedure, either the BIND_ARRAY procedure or the BIND_VARIABLE procedure, to supply the value of a variable in your program (or the values of an array) to the placeholder. When the SQL statement is subsequently run, TimesTen uses the data that your program has placed in the output and input, or bind, variables.

DBMS_SQL can run a DML statement multiple times, each time with a different bind variable. The BIND_ARRAY procedure lets you bind a collection of scalars, each value of which is used as an input variable once for each EXECUTE. This is similar to the array interface supported by the OCI.

DEFINE_COLUMN or DEFINE_ARRAY

The columns of the row being selected in a SELECT statement are identified by their relative positions as they appear in the select list, from left to right. For a query, you must call a define procedure (DEFINE_COLUMN or DEFINE_ARRAY) to specify the variables that are to receive the SELECT values, much the way an INTO clause does for a static query.

Use the DEFINE_ARRAY procedure to define a PL/SQL collection into which rows will be fetched in a single SELECT statement. DEFINE_ARRAY provides an interface to fetch multiple rows at one fetch. You must call DEFINE_ARRAY before using the COLUMN_VALUE procedure to fetch the rows.

EXECUTE

Call the EXECUTE function to run your SQL statement.

FETCH_ROWS or EXECUTE_AND_FETCH

The FETCH_ROWS function retrieves the rows that satisfy the query. Each successive fetch retrieves another set of rows, until the fetch cannot retrieve any more rows. Instead of calling EXECUTE and then FETCH_ROWS, you may find it more efficient to call EXECUTE_AND_FETCH if you are calling EXECUTE for a single execution.

VARIABLE_VALUE or COLUMN_VALUE

For queries, call COLUMN_VALUE to determine the value of a column retrieved by the FETCH_ROWS call. For anonymous blocks containing calls to PL/SQL procedures or DML statements with a RETURNING clause, call VARIABLE_VALUE to retrieve the values assigned to the output variables when statements were run.

CLOSE_CURSOR

When you no longer need a cursor for a session, close the cursor by calling CLOSE_CURSOR.

If you neglect to close a cursor, then the memory used by that cursor remains allocated even though it is no longer needed.

Processing queries

If you are using dynamic SQL to process a query, then you must perform the following steps:

1. Specify the variables that are to receive the values returned by the SELECT statement by calling the DEFINE_COLUMN procedure or the DEFINE_ARRAY procedure.

2. Run your SELECT statement by calling the EXECUTE function.

3. Call the FETCH_ROWS function (or EXECUTE_AND_FETCH) to retrieve the rows that satisfied your query.

4. Call COLUMN_VALUE procedure to determine the value of a column retrieved by FETCH_ROWS for your query. If you used anonymous blocks containing calls to PL/SQL procedures, then you must call the VARIABLE_VALUE procedure to retrieve the values assigned to the output variables of these procedures.

Processing updates, inserts, and deletes

If you are using dynamic SQL to process an INSERT, UPDATE, or DELETE, then you must perform the following steps.

1. You must first run your INSERT, UPDATE, or DELETE statement by calling the EXECUTE function.

2. If statements have the RETURNING clause, then you must call the VARIABLE_VALUE procedure to retrieve the values assigned to the output variables.

Locating errors

There are additional functions in the DBMS_SQL package for obtaining information about the last referenced cursor in the session. The values returned by these functions are only meaningful immediately after a SQL statement is run. In addition, some error-locating functions are only meaningful after certain DBMS_SQL calls. For example, call the LAST_ERROR_POSITION function immediately after a PARSE call.

Exceptions

inconsistent_type EXCEPTION;
  pragma exception_init(inconsistent_type, -6562);

This exception is raised by the COLUMN_VALUE procedure or the VARIABLE_VALUE procedure when the type of the given OUT parameter (for where to put the requested value) is different from the type of the value.

Examples

This section provides example procedures that make use of the DBMS_SQL package.

Example 1

This example does not require the use of dynamic SQL because the text of the statement is known at compile time, but it illustrates the basic concept underlying the package.

The demo procedure deletes all employees from a table myemployees (created from the employees table of the HR schema) whose salaries exceed a specified value.

CREATE OR REPLACE PROCEDURE demo(p_salary IN NUMBER) AS 
   cursor_name INTEGER;
   rows_processed INTEGER;
BEGIN
   cursor_name := dbms_sql.open_cursor;
   DBMS_SQL.PARSE(cursor_name, 'DELETE FROM myemployees WHERE salary > :x',
                  DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', p_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;
/

Create the myemployees table and see how many employees have salaries greater than or equal to $15,000:

Command> create table myemployees as select * from employees;
107 rows inserted.
 
Command> select * from myemployees where salary>=15000;
< 100, Steven, King, SKING, 515.123.4567, 1987-06-17 00:00:00, AD_PRES, 24000, 
<NULL>, <NULL>, 90 >
< 101, Neena, Kochhar, NKOCHHAR, 515.123.4568, 1989-09-21 00:00:00, AD_VP, 17000,
<NULL>, 100, 90 >
< 102, Lex, De Haan, LDEHAAN, 515.123.4569, 1993-01-13 00:00:00, AD_VP, 17000, 
<NULL>, 100, 90 >
3 rows found.

Run demo to delete everyone with a salary greater than $14,999 and confirm the results:

Command> begin
       > demo(14999);
       > end;
       > /
 
PL/SQL procedure successfully completed.
 
Command> select * from myemployees where salary>=15000;
0 rows found.

Example 2

The following sample procedure is passed the names of a source and a destination table, and copies the rows from the source table to the destination table. This sample procedure assumes that both the source and destination tables have the following columns:

id        of type NUMBER
name      of type VARCHAR2(30)
birthdate of type DATE

This procedure does not specifically require the use of dynamic SQL; however, it illustrates the concepts of this package.

CREATE OR REPLACE PROCEDURE copy ( 
     source      IN VARCHAR2, 
     destination IN VARCHAR2) IS 
     id_var             NUMBER; 
     name_var           VARCHAR2(30); 
     birthdate_var      DATE; 
     source_cursor      INTEGER; 
     destination_cursor INTEGER; 
     ignore             INTEGER; 
  BEGIN 
 
  -- Prepare a cursor to select from the source table: 
     source_cursor := dbms_sql.open_cursor; 
     DBMS_SQL.PARSE(source_cursor, 
         'SELECT id, name, birthdate FROM ' || source, 
          DBMS_SQL.NATIVE); 
     DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var); 
     DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name_var, 30); 
     DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate_var); 
     ignore := DBMS_SQL.EXECUTE(source_cursor); 
 
  -- Prepare a cursor to insert into the destination table: 
     destination_cursor := DBMS_SQL.OPEN_CURSOR; 
     DBMS_SQL.PARSE(destination_cursor, 
                  'INSERT INTO ' || destination || 
                  ' VALUES (:id_bind, :name_bind, :birthdate_bind)', 
                   DBMS_SQL.NATIVE); 
 
  -- Fetch a row from the source table and insert it into the destination table: 
     LOOP 
       IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN 
         -- get column values of the row 
         DBMS_SQL.COLUMN_VALUE(source_cursor, 1, id_var); 
         DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name_var); 
         DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate_var); 
 
  -- Bind the row into the cursor that inserts into the destination table. You 
  -- could alter this example to require the use of dynamic SQL by inserting an 
  -- if condition before the bind. 
        DBMS_SQL.BIND_VARIABLE(destination_cursor, ':id_bind', id_var); 
        DBMS_SQL.BIND_VARIABLE(destination_cursor, ':name_bind', name_var); 
        DBMS_SQL.BIND_VARIABLE(destination_cursor, ':birthdate_bind', 
                               birthdate_var); 
        ignore := DBMS_SQL.EXECUTE(destination_cursor); 
      ELSE 
 
  -- No more rows to copy: 
        EXIT; 
      END IF; 
    END LOOP; 
 
  -- Commit (in TimesTen commit closes cursors automatically): 
     COMMIT; 
 
   EXCEPTION 
     WHEN OTHERS THEN 
       IF DBMS_SQL.IS_OPEN(source_cursor) THEN 
         DBMS_SQL.CLOSE_CURSOR(source_cursor); 
       END IF; 
       IF DBMS_SQL.IS_OPEN(destination_cursor) THEN 
         DBMS_SQL.CLOSE_CURSOR(destination_cursor); 
       END IF; 
       RAISE; 
  END; 
/ 

Examples 3, 4, and 5: Bulk DML

This series of examples shows how to use bulk array binds (table items) in the SQL DML statements INSERT, UPDATE, and DELETE.

Here is an example of a bulk INSERT statement that adds three new departments to the departments table in the HR schema:

DECLARE
  stmt VARCHAR2(200);
  departid_array     DBMS_SQL.NUMBER_TABLE;
  deptname_array     DBMS_SQL.VARCHAR2_TABLE;
  mgrid_array        DBMS_SQL.NUMBER_TABLE;
  locid_array        DBMS_SQL.NUMBER_TABLE;
  c                NUMBER;
  dummy            NUMBER;
BEGIN
  departid_array(1):= 280;
  departid_array(2):= 290;
  departid_array(3):= 300;
 
  deptname_array(1) := 'Community Outreach';
  deptname_array(2) := 'Product Management';
  deptname_array(3) := 'Acquisitions';
 
  mgrid_array(1) := 121;
  mgrid_array(2) := 120;
  mgrid_array(3) := 70;
 
  locid_array(1):= 1500;
  locid_array(2):= 1700;
  locid_array(3):= 2700;
 
  stmt := 'INSERT INTO departments VALUES(
     :departid_array, :deptname_array, :mgrid_array, :locid_array)';
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_ARRAY(c, ':departid_array', departid_array);
  DBMS_SQL.BIND_ARRAY(c, ':deptname_array', deptname_array);
  DBMS_SQL.BIND_ARRAY(c, ':mgrid_array', mgrid_array);
  DBMS_SQL.BIND_ARRAY(c, ':locid_array', locid_array);
  dummy := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.CLOSE_CURSOR(c);
  EXCEPTION WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(c) THEN
      DBMS_SQL.CLOSE_CURSOR(c);
    END IF;
    RAISE;
END;
/

Here is output from a SELECT statement, showing the new rows:

Command> select * from departments;
< 10, Administration, 200, 1700 >
...
< 280, Community Outreach, 121, 1500 >
< 290, Product Management, 120, 1700 >
< 300, Acquisitions, 70, 2700 >
30 rows found.

Here is an example of a bulk UPDATE statement that demonstrates updating salaries for four existing employees in the employees table in the HR schema:

DECLARE
  stmt VARCHAR2(200);
  empno_array     DBMS_SQL.NUMBER_TABLE;
  salary_array    DBMS_SQL.NUMBER_TABLE;
  c               NUMBER;
  dummy           NUMBER;

BEGIN
  empno_array(1):= 203;
  empno_array(2):= 204;
  empno_array(3):= 205;
  empno_array(4):= 206;
 
  salary_array(1) := 7000;
  salary_array(2) := 11000;
  salary_array(3) := 13000;
  salary_array(4) := 9000;
 
  stmt := 'update employees set salary = :salary_array
    WHERE employee_id = :num_array';
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_ARRAY(c, ':num_array', empno_array);
  DBMS_SQL.BIND_ARRAY(c, ':salary_array', salary_array);
  dummy := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.CLOSE_CURSOR(c);
 
  EXCEPTION WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(c) THEN
      DBMS_SQL.CLOSE_CURSOR(c);
    END IF;
    RAISE;
END;
/

Assume the following entries for the specified employees before running the example, showing salaries of $6500, $10000, $12000, and $8300:

Command> select * from employees where employee_id>=203 and employee_id<=206;
< 203, Susan, Mavris, SMAVRIS, 515.123.7777, 1994-06-07 00:00:00, HR_REP, 
6500, <NULL>, 101, 40 >
< 204, Hermann, Baer, HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP, 
10000, <NULL>, 101, 70 >
< 205, Shelley, Higgins, SHIGGINS, 515.123.8080, 1994-06-07 00:00:00, AC_MGR,
12000, <NULL>, 101, 110 >
< 206, William, Gietz, WGIETZ, 515.123.8181, 1994-06-07 00:00:00, AC_ACCOUNT,
8300, <NULL>, 205, 110 >
4 rows found.

The following shows the new salaries after running the example:

Command> select * from employees where employee_id>=203 and employee_id<=206;
< 203, Susan, Mavris, SMAVRIS, 515.123.7777, 1994-06-07 00:00:00, HR_REP, 
7000, <NULL>, 101, 40 >
< 204, Hermann, Baer, HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP, 
11000, <NULL>, 101, 70 >
< 205, Shelley, Higgins, SHIGGINS, 515.123.8080, 1994-06-07 00:00:00, AC_MGR,
13000, <NULL>, 101, 110 >
< 206, William, Gietz, WGIETZ, 515.123.8181, 1994-06-07 00:00:00, AC_ACCOUNT,
9000, <NULL>, 205, 110 >
4 rows found.

In a DELETE statement, for example, you could bind in an array in the WHERE clause and have the statement be run for each element in the array, as follows:

DECLARE
  stmt VARCHAR2(200);
  dept_no_array DBMS_SQL.NUMBER_TABLE;
  c NUMBER;
  dummy NUMBER;
BEGIN
  dept_no_array(1) := 60; 
  dept_no_array(2) := 70;
  stmt := 'delete from employees where department_id = :dept_array';
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_ARRAY(c, ':dept_array', dept_no_array, 1, 1);
  dummy := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.CLOSE_CURSOR(c);
 
  EXCEPTION WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(c) THEN
      DBMS_SQL.CLOSE_CURSOR(c);
    END IF;
    RAISE;
END;
/

In this example, only the first element of the array is specified by the BIND_ARRAY call (lower and upper bounds of the array elements are both set to 1), so only employees in department 60 are deleted.

Before running the example, there are five employees in department 60 and one in department 70, where the department number is the last entry in each row:

Command> select * from employees where department_id>=60 and department_id<=70;
< 103, Alexander, Hunold, AHUNOLD, 590.423.4567, 1990-01-03 00:00:00, IT_PROG, 
9000, <NULL>, 102, 60 >
< 104, Bruce, Ernst, BERNST, 590.423.4568, 1991-05-21 00:00:00, IT_PROG, 6000, 
<NULL>, 103, 60 >
< 105, David, Austin, DAUSTIN, 590.423.4569, 1997-06-25 00:00:00, IT_PROG, 4800,
 <NULL>, 103, 60 >
< 106, Valli, Pataballa, VPATABAL, 590.423.4560, 1998-02-05 00:00:00, IT_PROG, 
4800, <NULL>, 103, 60 >
< 107, Diana, Lorentz, DLORENTZ, 590.423.5567, 1999-02-07 00:00:00, IT_PROG, 
4200, <NULL>, 103, 60 >
< 204, Hermann, Baer, HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP, 10000, 
<NULL>, 101, 70 >
6 rows found.

After running the example, only the employee in department 70 remains:

Command> select * from employees where department_id>=60 and department_id<=70;
< 204, Hermann, Baer, HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP, 10000, 
<NULL>, 101, 70 >
1 row found.

Example 6: Defining an array

CREATE OR REPLACE PROCEDURE BULK_PLSQL(deptid NUMBER) IS
    names    DBMS_SQL.VARCHAR2_TABLE;
    sals     DBMS_SQL.NUMBER_TABLE;
    c        NUMBER;
    r        NUMBER;
    sql_stmt VARCHAR2(32767) :=
        'SELECT last_name, salary FROM employees WHERE department_id = :b1';
 
BEGIN
    c := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(c, sql_stmt, dbms_sql.native);
    DBMS_SQL.BIND_VARIABLE(c, 'b1', deptid);
    DBMS_SQL.DEFINE_ARRAY(c, 1, names, 5, 1);
    DBMS_SQL.DEFINE_ARRAY(c, 2, sals, 5, 1);
 
    r := DBMS_SQL.EXECUTE(c);
 
    LOOP
      r := DBMS_SQL.FETCH_ROWS(c);
      DBMS_SQL.COLUMN_VALUE(c, 1, names);
      DBMS_SQL.COLUMN_VALUE(c, 2, sals);
      EXIT WHEN r != 5;
    END LOOP;
 
    DBMS_SQL.CLOSE_CURSOR(c);
 
    -- loop through the names and sals collections
    FOR i IN names.FIRST .. names.LAST  LOOP
      DBMS_OUTPUT.PUT_LINE('Name = ' || names(i) || ', salary = ' || sals(i));
    END LOOP;
END;
/

For example, for department 20 in the employees table, this produces the following output:

Command> begin
       > bulk_plsql(20);
       > end;
       > /
Name = Hartstein, salary = 13000
Name = Fay, salary = 6000
 
PL/SQL procedure successfully completed.

Example 7: Describe columns

This can be used as a substitute for the ttIsql DESCRIBE command by using a SELECT * query on the table to describe. This example describes columns of the employees table.

DECLARE
  c           NUMBER;
  d           NUMBER;
  col_cnt     INTEGER;
  f           BOOLEAN;
  rec_tab     DBMS_SQL.DESC_TAB;
  col_num    NUMBER;
  PROCEDURE print_rec(rec in DBMS_SQL.DESC_REC) IS
  BEGIN
    DBMS_OUTPUT.NEW_LINE;
    DBMS_OUTPUT.PUT_LINE('col_type            =    '
                         || rec.col_type);
    DBMS_OUTPUT.PUT_LINE('col_maxlen          =    '
                         || rec.col_max_len);
    DBMS_OUTPUT.PUT_LINE('col_name            =    '
                         || rec.col_name);
    DBMS_OUTPUT.PUT_LINE('col_name_len        =    '
                         || rec.col_name_len);
    DBMS_OUTPUT.PUT_LINE('col_schema_name     =    '
                         || rec.col_schema_name);
    DBMS_OUTPUT.PUT_LINE('col_schema_name_len =    '
                         || rec.col_schema_name_len);
    DBMS_OUTPUT.PUT_LINE('col_precision       =    '
                         || rec.col_precision);
    DBMS_OUTPUT.PUT_LINE('col_scale           =    '
                         || rec.col_scale);
    DBMS_OUTPUT.PUT('col_null_ok         =    ');
    IF (rec.col_null_ok) THEN
      DBMS_OUTPUT.PUT_LINE('true');
    ELSE
      DBMS_OUTPUT.PUT_LINE('false');
    END IF;
  END;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR;

  DBMS_SQL.PARSE(c, 'SELECT * FROM employees', DBMS_SQL.NATIVE);
 
  d := DBMS_SQL.EXECUTE(c);
 
  DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);

/*
 * Following loop could simply be for j in 1..col_cnt loop.
 * Here we are simply illustrating some PL/SQL table
 * features.
 */
  col_num := rec_tab.first;
  IF (col_num IS NOT NULL) THEN
    LOOP
      print_rec(rec_tab(col_num));
      col_num := rec_tab.next(col_num);
      EXIT WHEN (col_num IS NULL);
    END LOOP;
  END IF;
 
  DBMS_SQL.CLOSE_CURSOR(c);
END;
/

Here is an abbreviated sample of the output, describing columns of the employees table, assuming it was run from the HR schema. Information from only the first two columns is shown here:

col_type            =    2
col_maxlen          =    7
col_name            =    EMPLOYEE_ID
col_name_len        =    11
col_schema_name     =    HR
col_schema_name_len =    8
col_precision       =    6
col_scale           =    0
col_null_ok         =    false
col_type            =    1
col_maxlen          =    20
col_name            =    FIRST_NAME
col_name_len        =    10
col_schema_name     =    HR
col_schema_name_len =    8
col_precision       =    0
col_scale           =    0
col_null_ok         =    true
...

Example 8: RETURNING clause

With this clause, INSERT, UPDATE, and DELETE statements can return values of expressions. These values are returned in bind variables.

DBMS_SQL.BIND_VARIABLE is used to bind these outbinds if a single row is inserted, updated, or deleted. If multiple rows are inserted, updated, or deleted, then DBMS_SQL.BIND_ARRAY is used. DBMS_SQL.VARIABLE_VALUE must be called to get the values in these bind variables.

The examples that follow assume a table tab has been created:

Command> create table tab (c1 number, c2 number);

i) Single row insert.

CREATE OR REPLACE PROCEDURE single_Row_insert
           (c1 NUMBER, c2 NUMBER, r OUT NUMBER) is
      c NUMBER;
      n NUMBER;
      BEGIN
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'INSERT INTO tab VALUES (:bnd1, :bnd2) ' ||
                          'RETURNING c1*c2 INTO :bnd3', DBMS_SQL.NATIVE);
     DBMS_SQL.BIND_VARIABLE(c, 'bnd1', c1);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd2', c2);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd3', r);
        n := DBMS_SQL.EXECUTE(c); 
        DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r); -- get value of outbind variable
        DBMS_SQL.CLOSE_CURSOR(c);
      END;
      /
 

The following runs this example and shows the results. The table was initially empty.

Command> declare r NUMBER;
       > begin
       > single_Row_insert(100,200,r);
       > dbms_output.put_line('Product = ' || r);
       > end;
       > /
Product = 20000
 
PL/SQL procedure successfully completed.
 
Command> select * from tab;
< 100, 200 >
1 row found.

ii) Single row update. Note that rownum is an internal variable for row number.

CREATE OR REPLACE PROCEDURE single_Row_update
           (c1 NUMBER, c2 NUMBER, r out NUMBER) IS
      c NUMBER;
      n NUMBER;

      BEGIN
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'UPDATE tab SET c1 = :bnd1, c2 = :bnd2 ' ||
                          'WHERE rownum = 1 ' || 
                          'RETURNING c1*c2 INTO :bnd3', DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd1', c1);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd2', c2);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd3', r);
        n := DBMS_SQL.EXECUTE(c); 
        DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r);-- get value of outbind variable
        DBMS_SQL.CLOSE_CURSOR(c);
      END;
      /

The following runs this example and shows the results, updating the row that was inserted in the previous example.

Command> declare r NUMBER;
       > begin
       > single_Row_update(200,300,r);
       > dbms_output.put_line('Product = ' || r);
       > end;
       > /
Product = 60000
 
PL/SQL procedure successfully completed.
 
Command> select * from tab;
< 200, 300 >
1 row found.

iii) Multiple row insert.

CREATE OR REPLACE PROCEDURE multi_Row_insert
           (c1 DBMS_SQL.NUMBER_TABLE, c2 DBMS_SQL.NUMBER_TABLE, 
            r OUT DBMS_SQL.NUMBER_TABLE) is
      c NUMBER;
      n NUMBER;
      BEGIN
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'insert into tab VALUES (:bnd1, :bnd2) ' ||
                          'RETURNING c1*c2 INTO :bnd3', DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_ARRAY(c, 'bnd1', c1);
        DBMS_SQL.BIND_ARRAY(c, 'bnd2', c2);
        DBMS_SQL.BIND_ARRAY(c, 'bnd3', r);
        n := DBMS_SQL.EXECUTE(c); 
        DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r);-- get value of outbind variable
        DBMS_SQL.CLOSE_CURSOR(c);
      END;
      /

The following script runs this example:

declare
   c1_array dbms_sql.number_table;
   c2_array dbms_sql.number_table;
   r_array dbms_sql.number_table;
begin
   c1_array(1) := 10;
   c1_array(2) := 20;
   c1_array(3) := 30;
   c2_array(1) := 15;
   c2_array(2) := 25;
   c2_array(3) := 35;
   multi_Row_insert(c1_array,c2_array,r_array);
   dbms_output.put_line('Product for row1 = ' || r_array(1));
   dbms_output.put_line('Product for row2 = ' || r_array(2));
   dbms_output.put_line('Product for row3 = ' || r_array(3));
end;
/

Following are the results. The table was initially empty.

Product for row1 = 150
Product for row2 = 500
Product for row3 = 1050
 
PL/SQL procedure successfully completed.
 
Command> select * from tab;
< 10, 15 >
< 20, 25 >
< 30, 35 >
3 rows found.

iv) Multiple row update.

CREATE OR REPLACE PROCEDURE multi_Row_update
           (c1 NUMBER, c2 NUMBER, r OUT DBMS_SQL.NUMBER_TABLE) IS
        c NUMBER;
        n NUMBER;

     BEGIN
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'UPDATE tab SET c1 = :bnd1 WHERE c2 > :bnd2 ' ||
                          'RETURNING c1*c2 INTO :bnd3', DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd1', c1);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd2', c2);
        DBMS_SQL.BIND_ARRAY(c, 'bnd3', r);
        n := DBMS_SQL.EXECUTE(c); 
        DBMS_OUTPUT.PUT_LINE(n || ' rows updated');
        DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r);-- get value of outbind variable
        DBMS_SQL.CLOSE_CURSOR(c);
      END;
      /

The following script runs the example:

declare
   c1 NUMBER;
   c2 NUMBER;
   r_array dbms_sql.number_table;
begin
   c1 := 100;
   c2 := 0;
   multi_Row_update(c1, c2, r_array);
   dbms_output.put_line('Product for row1 = ' || r_array(1));
   dbms_output.put_line('Product for row2 = ' || r_array(2));
   dbms_output.put_line('Product for row3 = ' || r_array(3));
end;
/

Here are the results, updating the rows that were inserted in the previous example. (The report of the number of rows updated is from the example itself. The products are reported by the test script.)

3 rows updated
Product for row1 = 1500
Product for row2 = 2500
Product for row3 = 3500
 
PL/SQL procedure successfully completed.
 
Command> select * from tab;
< 100, 15 >
< 100, 25 >
< 100, 35 >
3 rows found.
Command>

v) Multiple row delete.

CREATE OR REPLACE PROCEDURE multi_Row_delete
           (c1_test NUMBER,
            r OUT DBMS_SQL.NUMBER_TABLE) is
      c NUMBER;
      n NUMBER;

      BEGIN
        c := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(c, 'DELETE FROM tab WHERE c1 = :bnd1 ' ||
                          'RETURNING c1*c2 INTO :bnd2', DBMS_SQL.NATIVE);
        DBMS_SQL.BIND_VARIABLE(c, 'bnd1', c1_test);
        DBMS_SQL.BIND_ARRAY(c, 'bnd2', r);
        n := DBMS_SQL.EXECUTE(c);
        DBMS_OUTPUT.PUT_LINE(n || ' rows deleted'); 
        DBMS_SQL.VARIABLE_VALUE(c, 'bnd2', r);-- get value of outbind variable
        DBMS_SQL.CLOSE_CURSOR(c);
      END;
      /

The following script runs the example.

declare
   c1_test NUMBER;
   r_array dbms_sql.number_table;
begin
   c1_test := 100;
   multi_Row_delete(c1_test, r_array);
   dbms_output.put_line('Product for row1 = ' || r_array(1));
   dbms_output.put_line('Product for row2 = ' || r_array(2));
   dbms_output.put_line('Product for row3 = ' || r_array(3));
end;
/

Here are the results, deleting the rows that were updated in the previous example. (The report of the number of rows deleted is from the example itself. The products are reported by the test script.)

3 rows deleted
Product for row1 = 1500
Product for row2 = 2500
Product for row3 = 3500
 
PL/SQL procedure successfully completed.
 
Command> select * from tab;
0 rows found.

Data structures

The DBMS_SQL package defines the following record types and table types.

Record types

• DESC_REC record type

• DESC_REC2 record type

• DESC_REC3 record type

Table types

• BINARY_DOUBLE_TABLE table type

• BINARY_FLOAT_TABLE table type

• DATE_TABLE table type

• DESC_TAB table type

• DESC_TAB2 table type

• DESC_TAB3 table type

• INTERVAL_DAY_TO_SECOND_TABLE table type

• INTERVAL_YEAR_TO_MONTH_TABLE table type

• NUMBER_TABLE table type

• TIME_TABLE table type

• TIMESTAMP_TABLE table type

• VARCHAR2_TABLE table type

• VARCHAR2A table type

• VARCHAR2S table type

DESC_REC record type

This record type holds the describe information for a single column in a dynamic query. It is the element type of the DESC_TAB table type and the DESCRIBE_COLUMNS procedure.

Syntax

TYPE desc_rec IS RECORD (
      col_type            BINARY_INTEGER := 0,
      col_max_len         BINARY_INTEGER := 0,
      col_name            VARCHAR2(32)   := '',
      col_name_len        BINARY_INTEGER := 0,
      col_schema_name     VARCHAR2(32)   := '',
      col_schema_name_len BINARY_INTEGER := 0,
      col_precision       BINARY_INTEGER := 0,
      col_scale           BINARY_INTEGER := 0,
      col_charsetid       BINARY_INTEGER := 0,
      col_charsetform     BINARY_INTEGER := 0,
      col_null_ok         BOOLEAN        := TRUE);
TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;

Fields

DESC_REC2 record type

DESC_REC2 is the element type of the DESC_TAB2 table type and the DESCRIBE_COLUMNS2 procedure.

This record type is identical to DESC_REC except for the col_name field, which has been expanded to the maximum possible size for VARCHAR2. It is therefore preferred to DESC_REC, which is deprecated, because column name values can be greater than 32 characters.

Syntax

TYPE desc_rec2 IS RECORD (
   col_type            binary_integer := 0,
   col_max_len         binary_integer := 0,
   col_name            varchar2(32767) := '',
   col_name_len        binary_integer := 0,
   col_schema_name     varchar2(32)   := '',
   col_schema_name_len binary_integer := 0,
   col_precision       binary_integer := 0,
   col_scale           binary_integer := 0,
   col_charsetid       binary_integer := 0,
   col_charsetform     binary_integer := 0,
   col_null_ok         boolean        := TRUE);

Fields

DESC_REC3 record type

DESC_REC3 is the element type of the DESC_TAB3 table type and the DESCRIBE_COLUMNS3 procedure.

DESC_REC3 is identical to DESC_REC2 except for two additional fields to hold the type name (type_name) and type name len (type_name_len) of a column in a dynamic query. The col_type_name and col_type_name_len fields are only populated when the col_type field value is 109 (the Oracle Database type number for user-defined types), which is not currently used.

Syntax

TYPE desc_rec3 IS RECORD (
   col_type               binary_integer := 0,
   col_max_len            binary_integer := 0,
   col_name               varchar2(32767) := '',
   col_name_len           binary_integer := 0,
   col_schema_name        varchar2(32) := '',
   col_schema_name_len    binary_integer := 0,
   col_precision          binary_integer := 0,
   col_scale              binary_integer := 0,
   col_charsetid          binary_integer := 0,
   col_charsetform        binary_integer := 0,
   col_null_ok            boolean := TRUE,
   col_type_name          varchar2(32767)   := '',
   col_type_name_len      binary_integer := 0);

Fields

BINARY_DOUBLE_TABLE table type

This is a table of BINARY_DOUBLE.

Syntax

TYPE binary_double_table IS TABLE OF BINARY_DOUBLE INDEX BY BINARY_INTEGER;

BINARY_FLOAT_TABLE table type

This is a table of BINARY_FLOAT.

Syntax

TYPE binary_float_table IS TABLE OF BINARY_FLOAT INDEX BY BINARY_INTEGER;

DATE_TABLE table type

This is a table of DATE.

Syntax

type date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER; 

DESC_TAB table type

This is a table of DESC_REC record type.

Syntax

TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;

DESC_TAB2 table type

This is a table of DESC_REC2 record type.

Syntax

TYPE desc_tab2 IS TABLE OF desc_rec2 INDEX BY BINARY_INTEGER;

DESC_TAB3 table type

This is a table of DESC_REC3 record type.

Syntax

TYPE desc_tab3 IS TABLE OF desc_rec3 INDEX BY BINARY_INTEGER; 

INTERVAL_DAY_TO_SECOND_TABLE table type

This is a table of DSINTERVAL_UNCONSTRAINED.

Syntax

TYPE interval_day_to_second_Table IS TABLE OF
    DSINTERVAL_UNCONSTRAINED INDEX BY binary_integer;

INTERVAL_YEAR_TO_MONTH_TABLE table type

This is a table of YMINTERVAL_UNCONSTRAINED.

Syntax

TYPE interval_year_to_month_table IS TABLE OF YMINTERVAL_UNCONSTRAINED 
   INDEX BY BINARY_INTEGER;

NUMBER_TABLE table type

This is a table of NUMBER.

Syntax

TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

TIME_TABLE table type

This is a table of TIME_UNCONSTRAINED.

Syntax

TYPE time_table IS TABLE OF TIME_UNCONSTRAINED INDEX BY BINARY_INTEGER;

TIMESTAMP_TABLE table type

This is a table of TIMESTAMP_UNCONSTRAINED.

Syntax

TYPE timestamp_table IS TABLE OF TIMESTAMP_UNCONSTRAINED INDEX BY BINARY_INTEGER;

VARCHAR2_TABLE table type

This is table of VARCHAR2(2000).

Syntax

TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;

VARCHAR2A table type

This is table of VARCHAR2(32767).

Syntax

TYPE varchar2a IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;

VARCHAR2S table type

This is table of VARCHAR2(256).

Syntax

TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;

Summary of DBMS_SQL subprograms

BIND_ARRAY procedure

This procedure binds a given value or set of values to a given variable in a cursor, based on the name of the variable in the statement.

Syntax

DBMS_SQL.BIND_ARRAY ( 
   c                   IN INTEGER, 
   name                IN VARCHAR2, 
   <table_variable>    IN <datatype> 
 [,index1              IN INTEGER, 
   index2              IN INTEGER)] ); 

Where the table_variable and its corresponding datatype can be any of the following matching pairs:

<bflt_tab>     dbms_sql.Binary_Float_Table
<bdbl_tab>     dbms_sql.Binary_Double_Table
<c_tab>        dbms_sql.Varchar2_Table
<d_tab>        dbms_sql.Date_Table
<ids_tab>      dbms_sql.Interval_Day_to_Second_Table
<iym_tab>      dbms_sql.Interval_Year_to_Month_Table
<n_tab>        dbms_sql.Number_Table
<tm_tab>       dbms_sql.Time_Table
<tms_tab>      dbms_sql.Timestamp_Table

Notice that the BIND_ARRAY procedure is overloaded to accept different data types.

Parameters

Usage notes

The length of the bind variable name should be less than or equal to 30 bytes.

For binding a range, the table must contain the elements that specify the range—tab(index1) and tab(index2)—but the range does not have to be dense. The index1 value must be less than or equal to index2. All elements between tab(index1) and tab(index2) are used in the bind.

If you do not specify indexes in the bind call, and two different binds in a statement specify tables that contain a different number of elements, then the number of elements actually used is the minimum number between all tables. This is also the case if you specify indexes. The minimum range is selected between the two indexes for all tables.

Not all bind variables in a query have to be array binds. Some can be regular binds and the same value are used for each element of the collections in expression evaluations (and so forth).

Bulk array binds

Bulk selects, inserts, updates, and deletes can enhance the performance of applications by bundling many calls into one. The DBMS_SQL package lets you work on collections of data using the PL/SQL table type.

Table items are unbounded homogeneous collections. In persistent storage, they are like other relational tables and have no intrinsic ordering. But when a table item is brought into the workspace (either by querying or by navigational access of persistent data), or when it is created as the value of a PL/SQL variable or parameter, its elements are given subscripts that can be used with array-style syntax to get and set the values of elements.

The subscripts of these elements need not be dense, and can be any number including negative numbers. For example, a table item can contain elements at locations -10, 2, and 7 only.

When a table item is moved from transient workspace to persistent storage, the subscripts are not stored. The table item is unordered in persistent storage.

At bind time the table is copied out from the PL/SQL buffers into local DBMS_SQL buffers (the same as for all scalar types), then the table is manipulated from the local DBMS_SQL buffers. Therefore, if you change the table after the bind call, then that change does not affect the way the execute acts.

Types for scalar collections

You can declare a local variable as one of the following table-item types, which are defined as public types in DBMS_SQL.

TYPE binary_double_table 
                    IS TABLE OF BINARY_DOUBLE  INDEX BY BINARY_INTEGER;
TYPE binary_float_table 
                    IS TABLE OF BINARY_FLOAT   INDEX BY BINARY_INTEGER;
TYPE date_table     IS TABLE OF DATE           INDEX BY BINARY_INTEGER;
TYPE interval_day_to_second_table 
                    IS TABLE OF dsinterval_unconstrained 
                                               INDEX BY BINARY_INTEGER;
TYPE interval_year_to_month_table 
                    IS TABLE OF yminterval_unconstrained 
                                               INDEX BY BINARY_INTEGER;
TYPE number_table   IS TABLE OF NUMBER         INDEX BY BINARY_INTEGER;
TYPE time_table     IS TABLE OF time_unconstrained           
                                               INDEX BY BINARY_INTEGER;
TYPE timestamp_table 
                    IS TABLE OF timestamp_unconstrained   
                                               INDEX BY BINARY_INTEGER;
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;

<tm_tab>   Time_Table
<tms_tab>  Timestamp_Table
<ids_tab>  Interval_Day_To_Second_Table
<iym_tab>  Interval_Year_To_Month_Table

Examples

See "Examples".

BIND_VARIABLE procedure

This procedures binds a given value or set of values to a given variable in a cursor, based on the name of the variable in the statement.

Syntax

DBMS_SQL.BIND_VARIABLE (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN <datatype>);

Where datatype can be any of the following types:

BINARY_DOUBLE
BINARY_FLOAT
DATE
INTERVAL DAY TO SECOND(9,9) (DSINTERVAL_UNCONSTRAINED)
NUMBER
TIME(9) (TIME_UNCONSTRAINED)
TIMESTAMP(9) (TIMESTAMP_UNCONSTRAINED)
VARCHAR2 CHARACTER SET ANY_CS
INTERVAL YEAR TO  MONTH(9) (YMINTERVAL_UNCONSTRAINED)
VARRAY
Nested table

Notice that BIND_VARIABLE is overloaded to accept different data types.

The following syntax is also supported for BIND_VARIABLE. The square brackets [] indicate an optional parameter for the BIND_VARIABLE function.

DBMS_SQL.BIND_VARIABLE (
   c            IN INTEGER,
   name         IN VARCHAR2,
   value        IN VARCHAR2 CHARACTER SET ANY_CS [,out_value_size IN INTEGER]);

To bind CHAR, RAW, and ROWID data, you can use the following variations on the following syntax:

DBMS_SQL.BIND_VARIABLE_CHAR (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN CHAR CHARACTER SET ANY_CS [,out_value_size IN INTEGER]);

DBMS_SQL.BIND_VARIABLE_RAW (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN RAW [,out_value_size IN INTEGER]);

DBMS_SQL.BIND_VARIABLE_ROWID (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN ROWID);

Parameters

Usage notes

If the variable is an IN or IN OUT variable or an IN collection, then the given bind value must be valid for the variable or array type. Bind values for OUT variables are ignored.

The bind variables or collections of a SQL statement are identified by their names. When binding a value to a bind variable or bind array, the string identifying it in the statement must contain a leading colon, as shown in the following example:

SELECT last_name FROM employees WHERE salary > :X;

For this example, the corresponding bind call would look similar to the following:

BIND_VARIABLE(cursor_name, ':X', 3500); 

Or:

BIND_VARIABLE (cursor_name, 'X', 3500);

The length of the bind variable name should be less than or equal to 30 bytes.

Examples

See "Examples".

CLOSE_CURSOR procedure

This procedure closes a given cursor.

Syntax

DBMS_SQL.CLOSE_CURSOR (
   c    IN OUT INTEGER);

Parameters

COLUMN_VALUE procedure

This procedure is used to access the data fetched by calling the FETCH_ROWS function. It returns the value of the cursor element for a given position in a given cursor.

Syntax

DBMS_SQL.COLUMN_VALUE (
   c                 IN  INTEGER,
   position          IN  INTEGER,
   value             OUT <datatype> 
 [,column_error      OUT NUMBER] 
 [,actual_length     OUT INTEGER]);

Where square brackets [] indicate optional parameters and datatype can be any of the following types:

BINARY_DOUBLE
BINARY_FLOAT
DATE
INTERVAL DAY TO SECOND(9,9) (DSINTERVAL_UNCONSTRAINED)
NUMBER
TIME(9) (TIME_UNCONSTRAINED)
TIMESTAMP(9) (TIMESTAMP_UNCONSTRAINED)
VARCHAR2 CHARACTER SET ANY_CS
INTERVAL YEAR TO  MONTH(9) (YMINTERVAL_UNCONSTRAINED)
VARRAY
Nested table

For variables containing CHAR, RAW, and ROWID data, you can use the following variations on the syntax:

DBMS_SQL.COLUMN_VALUE_CHAR (
   c               IN  INTEGER,
   position        IN  INTEGER,
   value           OUT CHAR CHARACTER SET ANY_CS
 [,column_error    OUT NUMBER]
 [,actual_length   OUT INTEGER]);
 
DBMS_SQL.COLUMN_VALUE_RAW (
   c               IN  INTEGER,
   position        IN  INTEGER,
   value           OUT RAW
 [,column_error    OUT NUMBER]
 [,actual_length   OUT INTEGER]);

DBMS_SQL.COLUMN_VALUE_ROWID (
   c               IN  INTEGER,
   position        IN  INTEGER,
   value           OUT ROWID
 [,column_error    OUT NUMBER]
 [,actual_length   OUT INTEGER]);

The following syntax enables the COLUMN_VALUE procedure to accommodate bulk operations:

DBMS_SQL.COLUMN_VALUE( 
   c                 IN             INTEGER, 
   position          IN             INTEGER, 
   <param_name>      IN OUT NOCOPY  <table_type>);  

Where the param_name and its corresponding table_type can be any of these matching pairs:

<bdbl_tab>     dbms_sql.Binary_Double_Table
<bflt_tab>     dbms_sql.Binary_Float_Table
<c_tab>        dbms_sql.Varchar2_Table
<d_tab>        dbms_sql.Date_Table
<ids_tab>      dbms_sql.Interval_Day_To_Second_Table
<iym_tab>      dbms_sql.Interval_Year_To_Month_Table
<n_tab>        dbms_sql.Number_Table
<tm_tab>       dbms_sql.Time_Table
<tms_tab>      dbms_sql.Timestamp_Table

Parameters

Exceptions

ORA-06562: Type of out argument must match type of column or bind variable

This exception is raised if the type of the given OUT parameter value is different from the actual type of the value. This type was the given type when the column was defined by calling DEFINE_COLUMN.

Examples

See "Examples".

DEFINE_ARRAY procedure

This procedure defines the collection into which the row values are fetched, with a FETCH_ROWS function call, for a given column. This procedure lets you do batch fetching of rows from a single SELECT statement. A single fetch brings several rows into the PL/SQL aggregate object.

Scalar types for collections

You can declare a local variable as one of the following table-item types, and then fetch any number of rows into it using DBMS_SQL. These are the same types you can specify for the BIND_ARRAY procedure.

TYPE binary_double_table 
                    IS TABLE OF BINARY_DOUBLE  INDEX BY BINARY_INTEGER;
TYPE binary_float_table 
                    IS TABLE OF BINARY_FLOAT   INDEX BY BINARY_INTEGER;
TYPE date_table     IS TABLE OF DATE           INDEX BY BINARY_INTEGER;
TYPE interval_day_to_second_table 
                    IS TABLE OF dsinterval_unconstrained 
                                               INDEX BY BINARY_INTEGER;
TYPE interval_year_to_month_table 
                    IS TABLE OF yminterval_unconstrained 
                                               INDEX BY BINARY_INTEGER;
TYPE number_table   IS TABLE OF NUMBER         INDEX BY BINARY_INTEGER;
TYPE time_table     IS TABLE OF time_unconstrained           
                                               INDEX BY BINARY_INTEGER;
TYPE timestamp_table 
                    IS TABLE OF timestamp_unconstrained   
                                               INDEX BY BINARY_INTEGER;
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;

Syntax

DBMS_SQL.DEFINE_ARRAY (
   c           IN INTEGER, 
   position    IN INTEGER,
   <table_variable>    IN <datatype> 
   cnt         IN INTEGER, 
   lower_bnd   IN INTEGER);

Where table_variable and its corresponding datatype can be any of the following matching pairs:

<bflt_tab>     dbms_sql.Binary_Float_Table
<bdbl_tab>     dbms_sql.Binary_Double_Table
<c_tab>        dbms_sql.Varchar2_Table
<d_tab>        dbms_sql.Date_Table
<n_tab>        dbms_sql.Number_Table
<tm_tab>       dbms_sql.Time_Table
<tms_tab>      dbms_sql.Timestamp_Table
<ids_tab>      dbms_sql.Interval_Day_To_Second_Table
<iym_tab>      dbms_sql.Interval_Year_To_Month_Table

Note that DEFINE_ARRAY is overloaded to accept different data types.

Parameters

Usage notes

The count (cnt) must be an integer greater than zero. The lower_bnd can be positive, negative, or zero. A query on which a DEFINE_ARRAY call was issued cannot contain array binds.

Exceptions

ORA-29253: Invalid count argument passed to procedure dbms_sql.define_array

This exception is raised if the count (cnt) is less than or equal to zero.

Examples

See "Examples".

DEFINE_COLUMN procedure

This procedure defines a column to be selected from the given cursor. This procedure is only used with SELECT cursors.

The column being defined is identified by its relative position in the SELECT list of the statement in the given cursor. The type of the COLUMN value determines the type of the column being defined.

Syntax

DBMS_SQL.DEFINE_COLUMN (
   c              IN INTEGER,
   position       IN INTEGER,
   column         IN <datatype>);

Where datatype can be any of the following types:

BINARY_DOUBLE
BINARY_FLOAT
DATE
INTERVAL DAY TO SECOND(9,9) (DSINTERVAL_UNCONSTRAINED)
NUMBER
TIME(9) (TIME_UNCONSTRAINED)
TIMESTAMP(9) (TIMESTAMP_UNCONSTRAINED)
INTERVAL YEAR TO  MONTH(9) (YMINTERVAL_UNCONSTRAINED)
VARRAY
Nested table

Note that DEFINE_COLUMN is overloaded to accept different data types.

The following syntax is also supported for the DEFINE_COLUMN procedure:

DBMS_SQL.DEFINE_COLUMN (
   c              IN INTEGER,
   position       IN INTEGER,
   column         IN VARCHAR2 CHARACTER SET ANY_CS,
   column_size    IN INTEGER);

To define columns with CHAR, RAW, and ROWID data, you can use the following variations on the procedure syntax:

DBMS_SQL.DEFINE_COLUMN_CHAR (
   c              IN INTEGER,
   position       IN INTEGER,
   column         IN CHAR CHARACTER SET ANY_CS,
   column_size    IN INTEGER);

DBMS_SQL.DEFINE_COLUMN_RAW (
   c              IN INTEGER,
   position       IN INTEGER,
   column         IN RAW,
   column_size    IN INTEGER);

DBMS_SQL.DEFINE_COLUMN_ROWID (
   c              IN INTEGER,
   position       IN INTEGER,
   column         IN ROWID); 

Parameters

Examples

See "Examples".

DESCRIBE_COLUMNS procedure

This procedure describes the columns for a cursor opened and parsed through DBMS_SQL.

Syntax

DBMS_SQL.DESCRIBE_COLUMNS ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DBMS_SQL.DESC_TAB);

DBMS_SQL.DESCRIBE_COLUMNS ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DBMS_SQL.DESC_REC);

Parameters

Examples

See "Examples".

DESCRIBE_COLUMNS2 procedure

This function describes the specified column. This is an alternative to DESCRIBE_COLUMNS procedure.

Syntax

DBMS_SQL.DESCRIBE_COLUMNS2 ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DBMS_SQL.DESC_TAB2);

DBMS_SQL.DESCRIBE_COLUMNS2 ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DBMS_SQL.DESC_REC2);

Parameters

DESCRIBE_COLUMNS3 procedure

This function describes the specified column. This is an alternative to DESCRIBE_COLUMNS procedure.

Syntax

DBMS_SQL.DESCRIBE_COLUMNS3 ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DBMS_SQL.DESC_TAB3);

DBMS_SQL.DESCRIBE_COLUMNS3 ( 
   c              IN  INTEGER, 
   col_cnt        OUT INTEGER, 
   desc_t         OUT DBMS_SQL.DESC_REC3);

Parameters

Usage notes

The cursor passed in by the cursor ID has to be opened and parsed, otherwise an error is raised for an invalid cursor ID.

EXECUTE function

This function executes a given cursor. This function accepts the ID number of the cursor and returns the number of rows processed. The return value is only valid for INSERT, UPDATE, and DELETE statements. For other types of statements, including DDL, the return value is undefined and should be ignored.

Syntax

DBMS_SQL.EXECUTE (
   c   IN INTEGER)
  RETURN INTEGER;

Parameters

Return value

An INTEGER value that indicates the number of rows processed.

EXECUTE_AND_FETCH function

This function executes the given cursor and fetches rows. It provides the same functionality as calling EXECUTE and then calling FETCH_ROWS; however, calling EXECUTE_AND_FETCH may reduce the number of network round trips when used against a remote database.

The EXECUTE_AND_FETCH function returns the number of rows actually fetched.

Syntax

DBMS_SQL.EXECUTE_AND_FETCH (
   c              IN INTEGER,
   exact          IN BOOLEAN DEFAULT FALSE)
  RETURN INTEGER;

Parameters

Return value

An INTEGER value indicating the number of rows that were fetched.

Exceptions

ORA-01422: Exact fetch returns more than requested number of rows

This exception is raised if the number of rows matching the query is not 1.

FETCH_ROWS function

This function fetches a row from a given cursor. A DEFINE_ARRAY procedure call defines the collection into which the row values are fetched.

A FETCH_ROWS call fetches the specified number of rows, according to the cnt parameter of the DEFINE_ARRAY call. When you fetch the rows, they are copied into DBMS_SQL buffers until you execute a COLUMN_VALUE procedure call, for each column, at which time the rows are copied into the table that was passed as an argument to COLUMN_VALUE. The rows are placed in positions lower_bnd, lower_bnd+1, lower_bnd+2, and so on, according to the lower_bnd setting in the DEFINE_ARRAY call. While there are still rows coming in, the user keeps issuing FETCH_ROWS and COLUMN_VALUE calls. You can call FETCH_ROWS repeatedly as long as there are rows remaining to be fetched.

The FETCH_ROWS function accepts the ID number of the cursor to fetch and returns the number of rows actually fetched.

Syntax

DBMS_SQL.FETCH_ROWS (
   c              IN INTEGER)
  RETURN INTEGER;

Parameters

Return value

An INTEGER value indicating the number of rows that were fetched.

Examples

See "Examples".

IS_OPEN function

This function checks to see if the given cursor is currently open.

Syntax

DBMS_SQL.IS_OPEN (
   c              IN INTEGER)
  RETURN BOOLEAN;

Parameters

Return value

Returns TRUE for any cursor number that has been opened but not closed, and FALSE for a NULL cursor number. Note that the CLOSE_CURSOR procedure nulls out the cursor variable passed to it.

Exceptions

ORA-29471 DBMS_SQL access denied

This is raised if an invalid cursor ID number is detected. Once a session has encountered and reported this error, every subsequent DBMS_SQL call in the same session will raise this error, meaning that DBMS_SQL is non-operational for this session.

LAST_ERROR_POSITION function

This function returns the byte offset in the SQL statement text where the error occurred. The first character in the SQL statement is at position 0.

Syntax

DBMS_SQL.LAST_ERROR_POSITION 
   RETURN INTEGER;

Return value

An INTEGER value indicating the byte offset in the SQL statement text where the error occurred.

Usage notes

Call this function after a PARSE call, before any other DBMS_SQL procedures or functions are called.

LAST_ROW_COUNT function

This function returns the cumulative count of the number of rows fetched.

Syntax

DBMS_SQL.LAST_ROW_COUNT 
   RETURN INTEGER;

Return value

An INTEGER value indicating the cumulative count of the number of rows that were fetched.

Usage notes

Call this function after a FETCH_ROWS or an EXECUTE_AND_FETCH call. If called after an EXECUTE call, the value returned is zero.

LAST_ROW_ID function

This function returns the rowid of the last row processed.

Because TimesTen does not support rowid of the last row operated on by a DML statement, this function returns NULL.

Syntax

DBMS_SQL.LAST_ROW_ID 
   RETURN ROWID;

Return value

Returns NULL for TimesTen.

LAST_SQL_FUNCTION_CODE function

This function returns the SQL function code for the statement. These codes are listed in the Oracle Call Interface Programmer's Guide.

Syntax

DBMS_SQL.LAST_SQL_FUNCTION_CODE 
   RETURN INTEGER;

Return value

An INTEGER value indicating the SQL function code for the statement.

Usage notes

Call this function immediately after the SQL statement is run. Otherwise, the return value is undefined.

OPEN_CURSOR function

This procedure opens a new cursor. The second overload takes a security_level parameter to apply fine-grained control to the security of the opened cursor.

When you no longer need this cursor, you must close it explicitly by calling the CLOSE_CURSOR procedure.

Syntax

DBMS_SQL.OPEN_CURSOR 
  RETURN INTEGER;

DBMS_SQL.OPEN_CURSOR (
   security_level   IN   INTEGER) 
  RETURN INTEGER;

Parameters

Return value

The cursor ID of the new cursor.

Usage notes

You can use cursors to run the same SQL statement repeatedly or to run a new SQL statement. When a cursor is reused, the contents of the corresponding cursor data area are reset when the new SQL statement is parsed. It is never necessary to close and reopen a cursor before reusing it.

PARSE procedure

This procedure parses the given statement in the given cursor. All statements are parsed immediately. In addition, DDL statements are run immediately when parsed.

There are multiple versions of the PARSE procedure:

• Taking a VARCHAR2 statement as an argument

• Taking VARCHAR2A, table of VARCHAR2(32767), as an argument. The VARCHAR2A overload version of the procedure concatenates elements of a PL/SQL table statement and parses the resulting string. You can use this procedure to parse a statement that is longer than the limit for a single VARCHAR2 variable by splitting up the statement.

Syntax

DBMS_SQL.PARSE (
   c                  IN   INTEGER,
   statement          IN   VARCHAR2,
   language_flag      IN   INTEGER);

DBMS_SQL.PARSE (
   c                  IN   INTEGER, 
   statement          IN   DBMS_SQL.VARCHAR2A,
   lb                 IN   INTEGER, 
   ub                 IN   INTEGER,
   lfflg              IN   BOOLEAN, 
   language_flag      IN   INTEGER);

DBMS_SQL.PARSE (
   c                  IN   INTEGER, 
   statement          IN   DBMS_SQL.VARCHAR2S,
   lb                 IN   INTEGER, 
   ub                 IN   INTEGER,
   lfflg              IN   BOOLEAN, 
   language_flag      IN   INTEGER);

Parameters

DBMS_SQL.PARSE(cursor1, 'BEGIN proc; END;', 2);
DBMS_SQL.PARSE(cursor1, 'INSERT INTO tab VALUES(1)', 2);

Usage notes

• Because client-side code cannot reference remote package variables or constants, you must explicitly use the values of the constants.

  For example, the following code does not compile on the client:

  DBMS_SQL.PARSE(cur_hdl, stmt_str, DBMS_SQL.NATIVE); 
  -- uses constant DBMS_SQL.NATIVE
  

  The following code works on the client, because the argument is explicitly provided:

  DBMS_SQL.PARSE(cur_hdl, stmt_str, 1); -- compiles on the client
  

• The VARCHAR2S type is supported only for backward compatibility. You are advised to use VARCHAR2A instead.

Exceptions

ORA-24344: Success with compilation error

If you create a type, procedure, function, or package that has compilation warnings, this exception is raised but the object is still created.

Examples

See "Examples".

TO_CURSOR_NUMBER function

This function takes an opened strongly or weakly-typed REF CURSOR and transforms it into a DBMS_SQL cursor number.

Syntax

DBMS_SQL.TO_CURSOR_NUMBER(
   rc IN OUT SYS_REFCURSOR)
  RETURN INTEGER;

Parameters

Return value

Returns a DBMS_SQL manageable cursor number transformed from a REF CURSOR.

Usage notes

• The REF CURSOR passed in has to be opened (OPEN_CURSOR).

• Once the REF CURSOR is transformed into a DBMS_SQL cursor number, the REF CURSOR is no longer accessible by any native dynamic SQL operations.

• Toggling between a REF CURSOR and DBMS_SQL cursor number after a fetch has started is not allowed.

Examples

CREATE OR REPLACE PROCEDURE DO_QUERY1(sql_stmt VARCHAR2) IS
  TYPE CurType IS REF CURSOR;
  src_cur         CurType;
  curid           NUMBER;
  desctab         DBMS_SQL.DESC_TAB;
  colcnt          NUMBER;
  namevar         VARCHAR2(50);
  numvar          NUMBER;
  datevar         DATE;
 
BEGIN
    -- sql_stmt := 'select * from employees';
    OPEN src_cur FOR sql_stmt;
 
    -- Switch from native dynamic SQL to DBMS_SQL
    curid := DBMS_SQL.TO_CURSOR_NUMBER(src_cur);
 
    DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
 
    -- Define columns
    FOR i IN 1 .. colcnt LOOP
         IF desctab(i).col_type = 2 THEN
           DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
         ELSIF desctab(i).col_type = 12 THEN
            DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
         ELSE
            DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 25);
         END IF;
    END LOOP;
 
  -- Fetch Rows
    WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
        FOR i IN 1 .. colcnt LOOP
          IF (desctab(i).col_type = 1) THEN
            DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
          ELSIF (desctab(i).col_type = 2) THEN
            DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
          ELSIF (desctab(i).col_type = 12) THEN
            DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
          END IF;
        END LOOP;
    END LOOP;
 
    DBMS_SQL.CLOSE_CURSOR(curid);
END;
/ 

You could execute this procedure as follows:

Command> begin
       > do_query1('select * from employees');
       > end;
       > /
 
PL/SQL procedure successfully completed.

TO_REFCURSOR function

This function takes an opened (OPEN_CURSOR), parsed (PARSE), and executed (EXECUTE) cursor and transforms or migrates it into a PL/SQL-manageable REF CURSOR (a weakly-typed cursor) that can be consumed by PL/SQL native dynamic SQL. This subprogram is only used with SELECT cursors.

Syntax

DBMS_SQL.TO_REFCURSOR(
   cursor_number IN OUT  INTEGER)
  RETURN SYS_REFCURSOR;

Parameters

Return value

A PL/SQL REF CURSOR transformed from a DBMS_SQL cursor number.

Usage notes

• The cursor passed in by the cursor_number has to be opened, parsed, and executed. Otherwise an error is raised.

• Once the cursor_number is transformed into a REF CURSOR, it is no longer accessible by any DBMS_SQL operations.

• After a cursor_number is transformed into a REF CURSOR, using DBMS_SQL.IS_OPEN results in an error.

• Toggling between REF CURSOR and DBMS_SQL cursor number after starting to fetch is not allowed. An error is raised.

Examples

CREATE OR REPLACE PROCEDURE DO_QUERY2(mgr_id NUMBER) IS
  TYPE CurType IS REF CURSOR;
  src_cur         CurType;
  curid           NUMBER;
  sql_stmt        VARCHAR2(200);
  ret             INTEGER;
  empnos          DBMS_SQL.Number_Table;
  depts           DBMS_SQL.Number_Table;
 
BEGIN
 
  -- DBMS_SQL.OPEN_CURSOR
  curid := DBMS_SQL.OPEN_CURSOR;
 
  sql_stmt :=
    'SELECT EMPLOYEE_ID, DEPARTMENT_ID from employees where MANAGER_ID = :b1';
 
  DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(curid, 'b1', mgr_id);
  ret := DBMS_SQL.EXECUTE(curid);
 
  -- Switch from DBMS_SQL to native dynamic SQL
  src_cur := DBMS_SQL.TO_REFCURSOR(curid);
 
  -- Fetch with native dynamic SQL
  FETCH src_cur BULK COLLECT INTO empnos, depts;
 
  IF empnos.COUNT > 0 THEN
    DBMS_OUTPUT.PUT_LINE('EMPNO DEPTNO');
    DBMS_OUTPUT.PUT_LINE('----- ------');
    -- Loop through the empnos and depts collections
    FOR i IN 1 .. empnos.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(empnos(i) || '   ' || depts(i));
    END LOOP;
  END IF;
 
  -- Close cursor
  CLOSE src_cur;
END;
/ 

The following example executes this procedure for a manager ID of 103.

Command> begin
       > do_query2(103);
       > end;
       > /
EMPNO DEPTNO
----- ------
104   60
105   60
106   60
107   60
 
PL/SQL procedure successfully completed.

VARIABLE_VALUE procedure

This procedure returns the value of the named variable for a given cursor. It is used to return the values of bind variables inside PL/SQL blocks or of DML statements with a RETURNING clause.

Syntax

DBMS_SQL.VARIABLE_VALUE (
   c               IN  INTEGER,
   name            IN  VARCHAR2,
   value           OUT NOCOPY <datatype>);
 

Where datatype can be any of the following types:

BINARY_DOUBLE
BINARY_FLOAT
DATE
INTERVAL DAY TO SECOND(9,9) (DSINTERVAL_UNCONSTRAINED)
NUMBER
TIME(9) (TIME_UNCONSTRAINED)
TIMESTAMP(9) (TIMESTAMP_UNCONSTRAINED)
VARCHAR2 CHARACTER SET ANY_CS
INTERVAL YEAR TO  MONTH(9) (YMINTERVAL_UNCONSTRAINED)
VARRAY
Nested table

For variables containing CHAR, RAW, and ROWID data, you can use the following variations on the syntax:

DBMS_SQL.VARIABLE_VALUE_CHAR (
   c               IN  INTEGER,
   name            IN  VARCHAR2,
   value           OUT CHAR CHARACTER SET ANY_CS);

DBMS_SQL.VARIABLE_VALUE_RAW (
   c               IN  INTEGER,
   name            IN  VARCHAR2,
   value           OUT RAW);

DBMS_SQL.VARIABLE_VALUE_ROWID (
   c               IN  INTEGER,
   name            IN  VARCHAR2,
   value           OUT ROWID);

The following syntax enables the VARIABLE_VALUE procedure to accommodate bulk operations:

DBMS_SQL.VARIABLE_VALUE ( 
   c                 IN   INTEGER, 
   name              IN   VARCHAR2,
   value             OUT NOCOPY <table_type>); 

For bulk operations, table_type can be any of the following:

dbms_sql.Binary_Double_Table
dbms_sql.Binary_Float_Table
dbms_sql.Date_Table
dbms_sql.Interval_Day_To_Second_Table
dbms_sql.Interval_Year_To_Month_Table
dbms_sql.Number_Table
dbms_sql.Time_Table
dbms_sql.Timestamp_Table
dbms_sql.Varchar2_Table

Parameters

Exceptions

ORA-06562: Type of out argument must match type of column or bind variable

This is raised if the type of the output parameter differs from the type of the value as defined by the BIND_VARIABLE call.

Examples

See "Examples".