Overview

The DBMS_SQL package provides a set of mechanisms to execute dynamic SQL statements, including system commands such as ALTER SESSION, DDL statements, and DML statements such as SELECT.

Note the following points:

• Parameters can only be specified in DML statements.

• For DML statements, only statements that return a result set can be obtained.

• DML statements are executed in the dbms_sql.execute interface, other statements are executed in the dbms_sql.parse interface.

• A cursor created by DBMS_SQL can only be used in the session where it is created and is not accessible to other sessions.

Privileges

DBMS_SQL is owned by the SYS user and is compiled with AUTHID CURRENT_USER. A DBMS_SQL subprogram called from an anonymous PL block runs with the privileges of the current user.

Constants

The following table describes the DBMS_SQL constants that are used with the language_flag parameter of the PARSE stored procedure.

Usage notes

The following operational notes describe how query, update, insert, and delete operations are handled.

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

1. Specify the variables that receive the return value of SELECT statements by calling the DEFINE_COLUMN subprogram.

2. Execute the SELECT statement by calling the EXECUTE function.

3. Retrieve the rows that satisfy your query by calling the FETCH_ROWS function.

4. Determine the value of a column returned by FETCH_ROWS for your query by calling the COLUMN_VALUE subprogram.

If you are using dynamic SQL to process an INSERT, UPDATE, or DELETE statement, the statement must be executed by calling the EXECUTE function.

Execution process

The execution process of DBMS_SQL is:

1. OPEN_CURSOR

2. PARSE

3. BIND_VARIABLE

4. DEFINE_COLUMN, DEFINE_COLUMN_LONG, or DEFINE_ARRAY

5. EXECUTE

6. FETCH_ROWS or EXECUTE_AND_FETCH

7. COLUMN_VALUE

8. CLOSE_CURSOR

Subprograms

The following table describes the DBMS_SQL subprograms supported by the current OceanBase Database version.