Oracle8 Application Developer's Guide Release 8.0 A58241-01 |
Library | Product | Contents | Index

# 14
Using Dynamic SQL

This chapter describes the dynamic SQL package, `DBMS_SQL`. The following topics are described in this chapter:

- the differences between the `DBMS_SQL` package and the Oracle Call Interfaces
- using the `DBMS_SQL` package to execute DDL
- procedures and functions provided in the `DBMS_SQL` package

## Overview of Dynamic SQL

You can 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 permits you to create procedures that are more general purpose. For example, using dynamic SQL allows you to create a procedure that operates on a table whose name is not known until runtime.

Additionally, you can parse any data manipulation language (DML) or data definition language (DDL) statement using the `DBMS_SQL` package. This helps solve the problem of not being able to parse data definition language statements directly using PL/SQL. For example, you might now choose to issue a `DROP` `TABLE` statement from within a stored procedure by using the `PARSE` procedure supplied with the `DBMS_SQL` package.

### Creating the DBMS_SQL Package

To create the `DBMS_SQL` package, submit the `DBMSSQL`.`SQL` and `PRVTSQL`.`PLB` scripts when connected as the user `SYS`. These scripts are run automatically by the `CATPROC`.`SQL` script.

See Also: "Privileges Required to Execute a Procedure" on page 10-40 for information on granting the necessary privileges to users who will be executing this package.

## 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 Oracle Call Interface Programmer's Guide 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 `CANCEL` cursor procedures.
- 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 on page 14-31. 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);
        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;

## Execution Flow

The typical flow of procedure calls is shown in Figure 14-1. A general explanation of these procedures follows.

Each of these procedures is described in greater detail starting on page 14-8.

#### 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 Oracle. 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's syntax and associates it with the cursor in your program.

See Also: Oracle8 Concepts

for an explanation of how `SQL` statements are parsed.

You can parse any data manipulation language or data definition language statements. Data definition language statements are executed on the parse, which performs the implied commit.

Note: When parsing a data definition language statement to drop a package or a procedure, a deadlock can occur if a procedure in the package is still in use by you. After a call to a procedure, that procedure is considered to be in use until execution has returned to the user side. Any such deadlock will timeout after five minutes.

#### Figure 14-1 DBMS_SQL Execution Flow

#### BIND_VARIABLE or BIND_ARRAY

Many data manipulation language statements require that data in your program be input to Oracle. 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 one of the bind procedures, `BIND_VARIABLE` or `BIND_ARRAY`, 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 executed, Oracle uses the data that your program has placed in the output and input, or bind, variables.

`DBMS_SQL` can execute a DML statement multiple times - each time with a different bind variable. The `BIND_ARRAY` procedure allows you to bind an array of scalars, each value of which will be used as an input variable once per `EXECUTE`. This is similar to the array interface supported by the OCI.

#### DEFINE_COLUMN, DEFINE_COLUMN_LONG, 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 one of the define procedures (`DEFINE_COLUMN`, `DEFINE_COLUMN_LONG`, 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.

You use the `DEFINE_COLUMN_LONG` procedure to define `LONG` columns, in the same way that `DEFINE_COLUMN` is used to define non-`LONG` columns. You must call `DEFINE_COLUMN_LONG` before using the `COLUMN_VALUE_LONG` procedure to fetch from the `LONG` column.

You use the `DEFINE_ARRAY` procedure to define a PL/SQL array into which you want to fetch rows in a single `SELECT` statement. You must call `DEFINE_ARRAY` before using the `COLUMN_VALUE` procedure to fetch the rows.

#### EXECUTE

Call the `EXECUTE` function to execute your SQL statement.

#### FETCH_ROWS or EXECUTE_AND_FETCH

Call the `FETCH_ROWS` function to retrieve the rows that satisfy the query. Each successive fetch retrieves another row, until the fetch is unable to retrieve anymore 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 iteration.

#### VARIABLE_VALUE, COLUMN_VALUE, or COLUMN_VALUE_LONG

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, call `VARIABLE_VALUE` to retrieve the values assigned to the output variables of the PL/SQL procedures when they were executed.

To fetch just part of a `LONG` database column (which can be up to two gigabytes in size), you use the `COLUMN_VALUE_LONG` procedure. You can specify the offset (in bytes) into the column value, and the number of bytes to fetch.

#### CLOSE_CURSOR

When you no longer need a cursor for a session, close the cursor by calling `CLOSE_CURSOR`. If you are using an Oracle Open Gateway, you may need to close cursors at other times as well. Consult your Oracle Open Gateway documentation for additional information.

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

## Security for Dynamic SQL

This section describes the security domain for `DBMS_SQL` procedures when you are using the Oracle Server or Trusted Oracle Server.

### For Oracle Server Users

Any `DBMS_SQL` procedures called from an anonymous PL/SQL block are executed using the privileges of the current user. Any `DBMS_SQL` procedures called from a stored procedure are executed using the privileges of the owner of the stored procedure.

Therefore, if a user creates a procedure and grants `EXECUTE` privilege on it to a second user, the second user must also be granted privileges explicitly for any DML operations performed in the body of the stored procedure. (These privileges cannot be granted through a role, because roles are disabled inside stored procedures.) Not granting privileges for the DML operations may result in the error message `ORA-01031`: "insufficient privileges" at runtime.

### For Trusted Oracle Server Users

Any `DBMS_SQL` procedures called from an anonymous PL/SQL block are executed using the privileges of the current user. Any `DBMS_SQL` procedures called from a stored procedure are executed using the discretionary access control (DAC) and system privileges of the owner of the stored procedure and the union of the mandatory access control (MAC) privileges granted to the stored procedure and the current user.

## Procedures and Functions

Table 14-1 provides a brief description of each of the procedures and functions associated with the `DBMS_SQL` package, which are described in detail later in this chapter.

See Also: "Examples of Using DBMS_SQL" on page 14-31 for examples of how these functions can be used.

##### Table 14-1 DBMS_SQL Package Functions and Procedures

Function/Procedure | Description | Refer to
--- | --- | ---
    OPEN_CURSOR
| Return cursor ID number of new cursor. | page 14-9
    PARSE
| Parse given statement. | page 14-10
    BIND_VARIABLE
| Bind a given value to a given variable. | page 14-11
    BIND_ARRAY
| Bind a given value to a given array. | page 14-11
    DEFINE_COLUMN
| Define a column to be selected from the given cursor, used only with SELECT statements. | page 14-16
    DEFINE_ARRAY
| Define an array to be selected from the given cursor, used only with SELECT statements. | page 14-18
    DEFINE_COLUMN_LONG
| Define a LONG column to be selected from the given cursor, used only with SELECT statements. | page 14-20
    EXECUTE
| Execute a given cursor. | page 14-20
    EXECUTE_AND_FETCH
| Execute a given cursor and fetch rows. | page 14-21
    FETCH_ROWS
| Fetch a row from a given cursor. | page 14-21
    COLUMN_VALUE
| Returns value of the cursor element for a given position in a cursor. | page 14-22
    COLUMN_VALUE_LONG
| Returns a selected part of a LONG column, that has been defined using DEFINE_COLUMN_LONG. | page 14-24
    VARIABLE_VALUE
| Returns value of named variable for given cursor. | page 14-25
    IS_OPEN
| Returns TRUE if given cursor is open. | page 14-27
    DESCRIBE_COLUMNS
| Describes the columns for a cursor opened and parsed through DBMS_SQL. | page 14-27
    CLOSE_CURSOR
| Closes given cursor and frees memory. | page 14-29
    LAST_ERROR_POSITION
| Returns byte offset in the SQL statement text where the error occurred. | page 14-30
    LAST_ROW_COUNT
| Returns cumulative count of the number of rows fetched. | page 14-30
    LAST_ROW_ID
| Returns ROWID of last row processed. | page 14-30
    LAST_SQL_
    FUNCTION_CODE
| Returns SQL function code for statement. | page 14-30

### OPEN_CURSOR Function

Call `OPEN_CURSOR` to open a new cursor. When you no longer need this cursor, you must close it explicitly by calling `CLOSE_CURSOR`.

You can use cursors to execute the same SQL statement repeatedly or to execute 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.

#### Syntax of OPEN_CURSOR

The `OPEN_CURSOR` function returns the cursor `ID` number of the new cursor. The syntax for this function is:

    DBMS_SQL.OPEN_CURSOR RETURN INTEGER;

### PARSE Procedure

Call `PARSE` to parse the given statement in the given cursor. All statements are parsed immediately. This may change in future versions; you should not rely on this behavior.

#### Syntax of PARSE

The parameters for the `PARSE` procedure are described in Table 14-2. The syntax for this procedure is:

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

The size limit for parsing SQL statements with the syntax above is size_t, which is the largest possible contiguous allocation on the machine being used. The `PARSE` procedure also supports the following syntax for large SQL statements:

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

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.

`

##### Table 14-2 DBMS_SQL.PARSE Procedure Parameters

Parameter | Description
--- | ---
    c
| Specify the `ID` number of the cursor in which to parse the statement.
    statement
| Provide the SQL statement to be parsed. Your SQL statement should not include a final semicolon.
    lb
| Provide the lower bound for elements in the statement.
    ub
| Provide the upper bound for elements in the statement.
    lfflg
| If `TRUE`, insert a linefeed after each element on concatenation.
    language_
    flag
| This parameter determines how Oracle handles the SQL statement. The following options are recognized for this parameter: V6 - specified Version 6 behavior V7 - specifies Oracle7 behavior `NATIVE` - specifies normal behavior for the database to which the program is connected.

`

#### The VARCHAR2S Datatype for Parsing Large SQL Strings

To parse SQL statements larger than 32 KB, the `DBMS_SQL` package makes use of PL/SQL tables to pass a table of strings to the `PARSE` procedure. These strings are concatenated and then passed on to the Oracle Server.

You can declare a local variable as the `VARCHAR2S` table-item type, and then use the `PARSE` procedure to parse a large SQL statement as `VARCHAR2S`.

The definition of the `VARCHAR2S` datatype is:

    type varchar2s is table of varchar2(256) index by binary_integer;

Call `DBMS_SQL`.`PARSE` to parse a large SQL statement in the given cursor. All statements are parsed immediately.

### BIND_VARIABLE and BIND_ARRAY Procedures

Call `BIND_VARIABLE` or `BIND_ARRAY` to bind a given value or set of values to a given variable in a cursor, based on the name of the variable in the statement. If the variable is an `IN` or `IN`/`OUT` variable or an `IN` array, the given bind value must be valid for the variable or array type. Bind values for `OUT` variables are ignored.

The bind variables or arrays 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 emp_name FROM emp WHERE SAL > :X;

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

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

#### Syntax of BIND_VARIABLE

The parameters for the `BIND_VARIABLE` procedures and functions are described in Table 14-3. The syntax for these procedures and functions is shown below. Notice that `BIND_VARIABLE` is overloaded to accept different datatypes.

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

where <datatype> can be any one of the following types:

    NUMBER
    DATE
    MLSLABEL
    VARCHAR2 CHARACTER SET ANY_CS
    BLOB
    CLOB CHARACTER SET ANY_CS
    BFILE

See Also: Chapter 6, "Large Objects (LOBs)" describes the BLOB, CLOB, and BFILE datatypes.

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 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);

`

##### Table 14-3 DBMS_SQL.BIND_VARIABLE Procedure Parameters

Parameter | Description
--- | ---
    c
| Specify the ID number of the cursor to which you want to bind a value.
    name
| Provide the name of the variable in the statement.
    value
| Provide the value that you want to bind to the variable in the cursor. For IN and IN/OUT variables, the value has the same type as the type of the value being passed in for this parameter.
    out_value_size
| The maximum expected OUT value size, in bytes, for the VARCHAR2, RAW, CHAR OUT or IN/OUT variable. If no size is given, the length of the current value is used.

`

#### 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 allows you to work on arrays 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) and then the table is manipulated from the local `DBMS_SQL` buffers. Therefore, if you change the table after the bind call, that change will not affect the way the execute behaves.

#### Types for Scalar and LOB Arrays

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

    type Number_Table is table of Number index by binary_integer;
    type Varchar2_Table is table of varchar2(2000) index by binary_integer;
    type Date_Table is table of Date index by binary_integer;
    type Blob_Table is table of Blob index by binary_integer;
    type Clob_Table is table of Clob index by binary_integer;
    type Bfile_Table is table of Bfile index by binary_integer;

#### Syntax of BIND_ARRAY

The parameters for the `BIND_ARRAY` procedure to bind an entire array or a range of an array are described in Table 14-4. The syntax for this procedure is shown below. Notice that the `BIND_ARRAY` procedure is overloaded to accept different datatypes.

    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 one of the following matching pairs:

    <num_tab>      Number_Table
    <vchr2_tab>    Varchar2_Table
    <date_tab>     Date_Table
    <blob_tab>     Blob_Table
    <clob_tab>     Clob_Table
    <bfile_tab>    Bfile_Table

`

##### Table 14-4 DBMS_SQL.BIND_ARRAY Procedure Parameters

Parameter | Description
--- | ---
    c
| Specify the ID number of the cursor to which you want to bind a value.
    name
| Provide the name of the array in the statement.
    table_variable
| Specify the local variable that has been declared as <datatype>.
    index1
| Provide the index for the table element that marks the lower bound of the range.
    index2
| Provide the index for the table element that marks the upper bound of the range.

`

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. Index1 must be less than or equal to index2. All elements between tab(index1) and tab(index2) will be 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, 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 will be used for each element of the arrays in expression evaluations (and so forth).

See Also: "Examples 3, 4, and 5: Bulk DML" on page 14-33 for examples of how to bind arrays.

### Processing Queries

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

1. Specify the variables that are to receive the values returned by the `SELECT` statement by calling `DEFINE_COLUMN`, `DEFINE_COLUMN_LONG`, or `DEFINE_ARRAY`.
2. Execute your `SELECT` statement by calling `EXECUTE`.
3. Call `FETCH_ROWS` (or `EXECUTE_AND_FETCH`) to retrieve the rows that satisfied your query.
4. Call `COLUMN_VALUE` or `COLUMN_VALUE_LONG` to determine the value of a column retrieved by the `FETCH_ROWS` call for your query. If you used anonymous blocks containing calls to PL/SQL procedures, you must call `VARIABLE_VALUE` to retrieve the values assigned to the output variables of these procedures.

### DEFINE_COLUMN Procedure

This procedure is only used with `SELECT` cursors. Call `DEFINE_COLUMN` to define a column to be selected from the given cursor. 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 of DEFINE_COLUMN

The parameters for the `DEFINE_COLUMN` procedure are described in Table 14-5. The syntax for this procedure is shown below. Notice that `DEFINE_COLUMN` is overloaded to accept different datatypes.

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

where <datatype> can be any one of the following types:

    NUMBER
    DATE
    MLSLABEL
    BLOB
    CLOB CHARACTER SET ANY_CS
    BFILE

See Also: Chapter 6, "Large Objects (LOBs)" describes BLOB, CLOB, and BFILE datatypes.

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);

`

##### Table 14-5 DBMS_SQL.DEFINE_COLUMN Procedure Parameters

Parameter | Description
--- | ---
    c
| The ID number of the cursor for the row being defined to be selected.
    position
| The relative position of the column in the row being defined. The first column in a statement has position 1.
    column
| The value of the column being defined. The type of this value determines the type for the column being defined.
    column_size
| The maximum expected size of the column value, in bytes, for columns of type VARCHAR2, CHAR, and RAW.

`

### DEFINE_ARRAY Procedure

Call `DEFINE_ARRAY` to define the appropriate table variable into which you want to fetch rows (with a `FETCH_ROWS` call). This procedure allows you to do batch fetching of rows from a single `SELECT` statement. A single fetch call brings over a number of rows into the PL/SQL aggregate object.

When you fetch the rows, they are copied into `DBMS_SQL` buffers until you execute a `COLUMN_VALUE` call, at which time the rows are copied into the table that was passed as an argument to the `COLUMN_VALUE` call.

#### Scalar and LOB Types for Arrays

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 as you can specify for the `BIND_ARRAY` procedure.)

    type Number_Table is table of Number index by binary_integer;
    type Varchar2_Table is table of varchar2(2000) index by binary_integer;
    type Date_Table is table of Date index by binary_integer;
    type Blob_Table is table of Blob index by binary_integer;
    type Clob_Table is table of Clob index by binary_integer;
    type Bfile_Table is table of Bfile index by binary_integer;

#### Syntax of DEFINE_ARRAY

The parameters for the `DEFINE_ARRAY` procedure are described in Table 14-6. The syntax for this procedure is shown below. Notice that the `DEFINE_ARRAY` procedure is overloaded to accept different datatypes.

    DBMS_SQL.DEFINE_ARRAY(
            c                 IN INTEGER,
            position          IN INTEGER,
            <table_variable>  IN <datatype>,
            count             IN INTEGER,
            indx              IN INTEGER);

where the <table_variable> and its corresponding <datatype> can be any one of the following matching pairs:

    <num_tab>      Number_Table
    <vchr2_tab>    Varchar2_Table
    <date_tab>     Date_Table
    <blob_tab>     Blob_Table
    <clob_tab>     Clob_Table
    <bfile_tab>    Bfile_Table

This procedure defines an appropriate "table" for the column at position "position" for cursor "c". The subsequent `FETCH_ROWS` call will fetch "count" rows. When the `COLUMN_VALUE` call is made, these rows will be placed in positions indx, indx+1, indx+2, and so on. While there are still rows coming, the user keeps issuing `FETCH_ROWS`/`COLUMN_VALUE` calls. The rows keep accumulating in the table specified as an argument in the `COLUMN_VALUE` call.

`

##### Table 14-6 DBMS_SQL.DEFINE_ARRAY Procedure Parameters

Parameter | Description
--- | ---
    c
| Specify the ID number of the cursor to which you want to bind an array.
    name
| Provide the name of the define variable in the statement.
    table_variable
| Specify the local variable that was declared as <datatype>.
    position
| The relative position of the column in the array being defined. The first column in a statement has position 1.
    indx
| Provide the starting position for where the rows should be placed.

`

The "count" has to be an integer greater than zero, otherwise an exception is raised. The "indx" can be positive, negative, or zero. A query on which a `DEFINE_ARRAY` call was issued cannot contain array binds.

See Also: "Examples 6 and 7: Defining an Array" on page 14-36 for examples of how to define arrays.

### DEFINE_COLUMN_LONG Procedure

Call this procedure to define a `LONG` column for a `SELECT` cursor. The column being defined is identified by its relative position in the `SELECT` list of the statement for the given cursor. The type of the `COLUMN` value determines the type of the column being defined.

#### Syntax of DEFINE_COLUMN_LONG

The parameters of `DEFINE_COLUMN_LONG` are described in Table 14-7. The syntax is:

    DBMS_SQL.DEFINE_COLUMN_LONG(
            c              IN INTEGER,
            position       IN INTEGER);

`

##### Table 14-7 DBMS_SQL.DEFINE_COLUMN_LONG Procedure Parameters

Parameter | Description
--- | ---
    c
| The ID number of the cursor for the row being defined to be selected.
    position
| The relative position of the column in the row being defined. The first column in a statement has position 1.

`

### EXECUTE Function

Call `EXECUTE` to execute 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 of EXECUTE

The syntax for the `EXECUTE` function is:

    DBMS_SQL.EXECUTE (
            c              IN INTEGER)
    RETURN INTEGER;

### EXECUTE_AND_FETCH Function

Call `EXECUTE_AND_FETCH` to execute the given cursor and fetch rows. This function provides the same functionality as calling `EXECUTE` and then calling `FETCH_ROWS`. Calling `EXECUTE_AND_FETCH` instead, however, may cut down on the number of network round-trips when used against a remote database.

#### Syntax of EXECUTE_AND_FETCH

The `EXECUTE_AND_FETCH` function returns the number of rows actually fetched. The parameters for this procedure are described in Table 14-8, and the syntax is shown below.

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

`

##### Table 14-8 DBMS_SQL.EXECUTE_AND_FETCH Function Parameters

Parameter | Description
--- | ---
    c
| Specify the ID number of the cursor to execute and fetch.
    exact
| Set to TRUE to raise an exception if the number of rows actually matching the query differs from one. Even if an exception is raised, the rows are still fetched and available.

`

### FETCH_ROWS Function

Call `FETCH_ROWS` to fetch a row from a given cursor. You can call `FETCH_ROWS` repeatedly as long as there are rows remaining to be fetched. These rows are retrieved into a buffer, and must be read by calling `COLUMN_VALUE`, for each column, after each call to `FETCH_ROWS`.

#### Syntax of FETCH_ROWS

The `FETCH_ROWS` function accepts the `ID` number of the cursor to fetch, and returns the number of rows actually fetched. The syntax for this function is shown below.

    DBMS_SQL.FETCH_ROWS(
            c              IN INTEGER)
    RETURN INTEGER;

### COLUMN_VALUE Procedure

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

#### Syntax of COLUMN_VALUE

The parameters for the `COLUMN_VALUE` procedure are described in Table 14-9. The syntax for this procedure is shown below. The square brackets [] indicate optional parameters.

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

where <datatype> can be any one of the following types:

    NUMBER
    DATE
    MLSLABEL
    VARCHAR2 CHARACTER SET ANY_CS
    BLOB
    CLOB CHARACTER SET ANY_CS
    BFILE

See Also: Chapter 6, "Large Objects (LOBs)" describes the BLOB, CLOB, and BFILE datatypes.

The following syntax is also supported for the `COLUMN_VALUE` procedure:

    DBMS_SQL.COLUMN_VALUE(
            c                 IN  INTEGER,
            position          IN  INTEGER,
            <table_variable>  IN  <datatype>);

where the <table_variable> and its corresponding <datatype> can be any one of these matching pairs:

    <num_tab>      Number_Table
    <vchr2_tab>    Varchar2_Table
    <date_tab>     Date_Table
    <blob_tab>     Blob_Table
    <clob_tab>     Clob_Table
    <bfile_tab>    Bfile_Table

For columns 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]);

`

##### Table 14-9 DBMS_SQL.COLUMN_VALUE Procedure Parameters

Parameter | Mode | Description
--- | --- | ---
    c
| `IN` | Specify the `ID` number of the cursor from which you are fetching the values.
    position
| `IN` | Specify the relative position of the column in the cursor. The first column in a statement has position 1.
    value
| `OUT` | Returns the value at the specified column and row. If the row number specified is greater than the total number of rows fetched, you receive an error message. Oracle raises exception `ORA-06562`, inconsistent_type, if the type of this output parameter differs from the actual type of the value, as defined by the call to `DEFINE_COLUMN`.
    table_variable
| `IN` | Specify the local variable that has been declared as <datatype>.
    column_
    error
| `OUT` | Returns any error code for the specified column value.
    actual_
    length
| `OUT` | Returns the actual length, before any truncation, of the value in the specified column.

`

### COLUMN_VALUE_LONG Procedure

This procedure returns the value of the cursor element for a given position, offset, and size in a given cursor. This procedure is used to access the data fetched by calling `FETCH_ROWS`.

#### Syntax of COLUMN_VALUE_LONG

The parameters of the `COLUMN_VALUE_LONG` procedure are described in Table 14-10. The syntax of the procedure is:

    DBMS_SQL.COLUMN_VALUE_LONG(
            c               IN  INTEGER,
            position        IN  INTEGER,
            length          IN  INTEGER,
            offset          IN  INTEGER,
            value           OUT VARCHAR2,
            value_length    OUT INTEGER);

``

##### Table 14-10 DBMS_SQL.COLUMN_VALUE_LONG Procedure Parameters

Parameter | Description
--- | ---
    c
| The `ID` number of the cursor for the row being defined to be selected.
    position
| The relative position of the column in the row being defined. The first column in a statement has position 1.
    length
| The length in bytes of the segment of the column value that is to be selected.
    offset
| The byte position in the `LONG` column at which the `SELECT` is to start.
    value
| The value of the column segment to be `SELECT`ed.
    value_length
| The (returned) length of the value that was `SELECT`ed.

### VARIABLE_VALUE Procedure

This procedure returns the value of the named variable for a given cursor. It is also used to return the values of bind variables inside PL/SQL blocks.

#### Syntax of VARIABLE_VALUE

The parameters for the `VARIABLE_VALUE` procedure are described in Table 14-11. The syntax for this procedure is:

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

where <datatype> can be any one of the following types:

    NUMBER
    DATE
    MLSLABEL
    VARCHAR2 CHARACTER SET ANY_CS
    BLOB
    CLOB CHARACTER SET ANY_CS
    BFILE

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);

`

##### Table 14-11 DBMS_SQL.VARIABLE_VALUE Procedure Parameters

Parameter | Mode | Description
--- | --- | ---
    c
| `IN` | Specify the `ID` number of the cursor from which to get the values.
    name
| `IN` | Specify the name of the variable for which you are retrieving the value.
    value
| `OUT` | Returns the value of the variable for the specified position. Oracle raises exception`ORA-06562`, inconsistent_type, if the type of this output parameter differs from the actual type of the value, as defined by the call to `BIND_VARIABLE`.
    position
| `IN` | Specify the relative position of the column in the cursor. The first column in a statement has position 1.

`

### Processing Updates, Inserts and Deletes

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

1. You must first execute your `INSERT`, `UPDATE`, or `DELETE` statement by calling `EXECUTE`. The `EXECUTE` procedure is described on page 14-20.
2. If you used anonymous blocks containing calls to PL/SQL procedures, you must call `VARIABLE_VALUE` to retrieve the values assigned to the output variables of these procedures. The `VARIABLE_VALUE` procedure is described on page 14-25.

### IS_OPEN Function

The `IS_OPEN` function returns `TRUE` if the given cursor is currently open.

#### Syntax of IS_OPEN

The `IS_OPEN` function accepts the `ID` number of a cursor, and returns `TRUE` if the cursor is currently open, or `FALSE` if it is not. The syntax for this function is:

    DBMS_SQL.IS_OPEN(
            c              IN INTEGER)
    RETURN BOOLEAN;

### DESCRIBE_COLUMNS Procedure

Call this procedure to describe the columns for a cursor opened and parsed through `DBMS_SQL`.

#### The DESC_REC Type

The `DBMS_SQL` package declares the `DESC_REC` record type as follows:

    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);

The parameters of DESC_REC are described in Table 14-12.

`

##### Table 14-12 DESC_REC Type Parameters

Parameter | Description
--- | ---
    col_type
| The type of the column being described.
    col_max_len
| The maximum length of the column.
    col_name
| The name of the column.
    col_name_len
| The length of the column name.
    col_schema_name
| The name of the schema the column type was defined in (if an object type).
    col_schema_name_len
| The length of the schema.
    col_precision
| The column precision if a number.
    col_scale
| The column scale if a number.
    col_charsetid
| The column character set identifier.
    col_charsetform
| The column character set form.
    col_null_ok
| True if column can be null.

`

#### The DESC_TAB Type

The `DESC_TAB` type is a `PL`/SQL table of `DESC_REC` records:

    type desc_tab is table of desc_rec index by binary_integer;

You can declare a local variable as the PL/SQL table type `DESC_TAB`, and then call the `DESCRIBE_COLUMNS` procedure to fill in the table with the description of each column. All columns are described; you cannot describe a single column.

#### Syntax of DESCRIBE_COLUMNS

The parameters of DESCRIBE_COLUMNS are described in Table 14-13. The syntax is:

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

`

##### Table 14-13 DBMS_SQL.DESCRIBE_COLUMNS Procedure Parameters

Parameter | Mode | Description
--- | --- | ---
    c
| `IN` | The `ID` number of the cursor for the columns being described.
    col_cnt
| `OUT` |
    desc_t
| `OUT` |

`

See Also: "Example 8: Describe Columns" on page 14-38 for an example of how to use `DESCRIBE_COLUMNS.`

### CLOSE_CURSOR Procedure

Call `CLOSE_CURSOR` to close a given cursor.

#### Syntax of CLOSE_CURSOR

The parameter for the `CLOSE_CURSOR` procedure is described in Table 14-14. The syntax for this procedure is:

    DBMS_SQL.CLOSE_CURSOR(
            c              IN OUT INTEGER);

``

##### Table 14-14 DBMS_SQL.CLOSE_CURSOR Procedure Parameters

Parameter | Mode | Description
--- | --- | ---
    c
| `IN` | Specify the `ID` number of the cursor that you want to close.
| `OUT` | The cursor is set to null. After you call `CLOSE_CURSOR`, the memory allocated to the cursor is released and you can no longer fetch from that cursor.

## 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 executed. In addition, some error-locating functions are only meaningful after certain `DBMS_SQL` calls. For example, you call `LAST_ERROR_POSITION` immediately after a `PARSE`.

### LAST_ERROR_POSITION 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.

    DBMS_SQL.LAST_ERROR_POSITION RETURN INTEGER;

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

### LAST_ROW_COUNT Function

Returns the cumulative count of the number of rows fetched.

    DBMS_SQL.LAST_ROW_COUNT RETURN INTEGER;

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

### LAST_ROW_ID Function

Returns the `ROWID` of the last row processed.

    DBMS_SQL.LAST_ROW_ID RETURN ROWID;

Call this function after a `FETCH_ROWS` or an `EXECUTE_AND_FETCH` call.

### LAST_SQL_FUNCTION_CODE Function

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

    DBMS_SQL.LAST_SQL_FUNCTION_CODE RETURN INTEGER;

You should call this function immediately after the SQL statement is executed; otherwise, the return value is undefined.

## Examples of Using DBMS_SQL

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

##### Example 1

The following sample procedure is passed a SQL statement, which it then parses and executes:

    CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2) AS
        cursor_name INTEGER;
        ret INTEGER;
    BEGIN
       cursor_name := DBMS_SQL.OPEN_CURSOR;

       --DDL statements are executed by the parse call, which
       --performs the implied commit
       DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL);
       ret := DBMS_SQL.EXECUTE(cursor_name);
       DBMS_SQL.CLOSE_CURSOR(cursor_name);
    END;

Creating such a procedure allows you to perform the following operations:

- The SQL statement can be dynamically generated at runtime by the calling program.
- The SQL statement can be a DDL statement.

For example, after creating this procedure, you could make the following call:

    exec('create table acct(c1 integer)');

You could even call this procedure remotely, as shown in the following example. This allows you to perform remote DDL.

    exec@hq.com('CREATE TABLE acct(c1 INTEGER)');

##### 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

    -- This procedure copies rows from a given source table to a
    -- given destination table assuming that both source and
    -- destination tables have the following columns:
    --   - ID of type NUMBER,
    --   - NAME of type VARCHAR2(30),
    --   - BIRTHDATE of type DATE.
      id                 NUMBER;
      name               VARCHAR2(30);
      birthdate          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);
      DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id);
      DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name, 30);
      DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate);
      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, :name, :birthdate)',
                     DBMS_SQL);

      -- 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);
          DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name);
          DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate);

          -- 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', id);
          DBMS_SQL.BIND_VARIABLE(destination_cursor, 'name', name);
          DBMS_SQL.BIND_VARIABLE(destination_cursor, 'birthdate',
                                 birthdate);
          ignore := DBMS_SQL.EXECUTE(destination_cursor);
        ELSE

        -- no more row to copy
          EXIT;
        END IF;
      END LOOP;

      -- commit and close all cursors
      COMMIT;
      DBMS_SQL.CLOSE_CURSOR(source_cursor);
      DBMS_SQL.CLOSE_CURSOR(destination_cursor);

    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 DELETE, INSERT, and UPDATE.

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

    declare
      stmt varchar2(200);
      dept_no_array dbms_sql.Number_Table;
      c number;
      dummy number;
    begin
      dept_no_array(1) := 10; dept_no_array(2) := 20;
      dept_no_array(3) := 30; dept_no_array(4) := 40;
      dept_no_array(5) := 30; dept_no_array(6) := 40;
      stmt := 'delete from emp where deptno = :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, 4);
      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 the example above, only elements 1 through 4 will be used as specified by the bind_array call. Each element of the array will potentially delete a large number of employees from the database.

Here is an example of a bulk INSERT statement:

    declare
      stmt varchar2(200);
      empno_array dbms_sql.Number_Table;
      empname_array dbms_sql.Varchar2_Table;
      c number;
      dummy number;
    begin
      for i in 0..9 loop
        empno_array(i) := 1000 + i;
        empname_array(I) := get_name(i);
      end loop;
      stmt := 'insert into emp values(:num_array, :name_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, ':name_array', empname_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;
    /

When the execute takes place, all 10 of the employees are inserted into the table.

Finally, here is an example of an bulk UPDATE statement.

    declare
      stmt varchar2(200);
      emp_no_array dbms_sql.Number_Table;
      emp_addr_array dbms_sql.Varchar2_Table;
      c number;
      dummy number;
    begin
      for i in 0..9 loop
        emp_no_array(i) := 1000 + i;
        emp_addr_array(I) := get_new_addr(i);
      end loop;
      stmt := 'update emp set ename = :name_array
        where empno = :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, ':name_array', empname_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 when the execute call happens, the addresses of all employees are updated in one shot. The two arrays are always stepped in unison. If the `WHERE` clause returns more than one row, all those employees will get the address the "addr_array" happens to be pointing to at the time.

##### Examples 6 and 7: Defining an Array

The following examples show how to use the `DEFINE_ARRAY` procedure:

    declare
      c       number;
      d       number;
      n_tab   dbms_sql.Number_Table;
      indx    number := -10;
    begin
      c := dbms_sql.open_cursor;
      dbms_sql.parse(c, 'select n from t order by 1', dbms_sql);

      dbms_sql.define_array(c, 1, n_tab, 10, indx);

      d := dbms_sql.execute(c);
      loop
        d := dbms_sql.fetch_rows(c);

        dbms_sql.column_value(c, 1, n_tab);

        exit when d != 10;
      end loop;

      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;
    /

Each time the example above does a `FETCH_ROWS` call, it fetches 10 rows that are kept in `DBMS_SQL` buffers. When the `COLUMN_VALUE` call is executed, those rows move into the PL/SQL table specified (in this case n_tab), at positions -10 to -1, as specified in the `DEFINE` statements. When the second batch is fetched in the loop, the rows go to positions 0 to 9; and so on.

A current index into each array is maintained automatically. This index is initialized to "indx" at `EXECUTE` and keeps getting updated every time a `COLUMN_VALUE` call is made. If you re-execute at any point, the current index for each `DEFINE` is re-initialized to "indx".

In this way the entire result of the query is fetched into the table. When `FETCH_ROWS` cannot fetch 10 rows, it returns the number of rows actually fetched (if no rows could be fetched it returns zero) and exits the loop.

Here is another example of using the `DEFINE_ARRAY` procedure:

Consider a table `MULTI_TAB` defined as:

    create table multi_tab (num number,
                            dat1 date,
                            var varchar2(24),
                            dat2 date)

To select everything from this table and move it into four PL/SQL tables, you could use the following simple program:

    declare
      c       number;
      d       number;
      n_tab  dbms_sql.Number_Table;
      d_tab1 dbms_sql.Date_Table;
      v_tab  dbms_sql.Varchar2_Table;
      d_tab2 dbms_sql.Date_Table;
      indx number := 10;
    begin

      c := dbms_sql.open_cursor;
      dbms_sql.parse(c, 'select * from multi_tab order by 1', dbms_sql);

      dbms_sql.define_array(c, 1, n_tab,  5, indx);
      dbms_sql.define_array(c, 2, d_tab1, 5, indx);
      dbms_sql.define_array(c, 3, v_tab,  5, indx);
      dbms_sql.define_array(c, 4, d_tab2, 5, indx);

      d := dbms_sql.execute(c);

      loop
        d := dbms_sql.fetch_rows(c);

        dbms_sql.column_value(c, 1, n_tab);
        dbms_sql.column_value(c, 2, d_tab1);
        dbms_sql.column_value(c, 3, v_tab);
        dbms_sql.column_value(c, 4, d_tab2);

        exit when d != 5;
      end loop;

      dbms_sql.close_cursor(c);

    /*

Here the four tables can be used for anything at all. One usage might be to use `BIND_ARRAY` to move the rows to another table by using a query such as '`INSERT` into `SOME_T` values(:a, :b, :c, :d);

    */

    exception when others then
        if dbms_sql.is_open(c) then
          dbms_sql.close_cursor(c);
        end if;
        raise;
    end;
    /

##### Example 8: Describe Columns

This can be used as a substitute to the SQL*Plus `DESCRIBE` call by using a `SELECT` * query on the table that you want to describe.

    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 scott.bonus', dbms_sql);

      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 of the 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;
    /

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index