Overview of DBMS_SQL

The DBMS_SQL package provides a mechanism for executing dynamic statements. It supports various types of statements, including system commands (such as ALTER SESSION), Data Definition Language (DDL), and Data Manipulation Language (DML) statements, including SELECT.

Consider the following points when using DBMS_SQL:

• Only DML statements can specify parameters.

• Only DML statements that return result sets can be retrieved.

• DML statements are executed in dbms_sql.execute interfaces, while other statements are executed in dbms_sql.parse interfaces.

• Cursors created by DBMS_SQL are only available in the current session and cannot be accessed by other sessions.

Privilege requirements

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

Constants

The following table lists the constants used by the language_flag parameter of the PARSE stored procedure in DBMS_SQL.

Considerations

The following considerations describe how to handle queries, updates, inserts, and deletes.

If you use dynamic SQL to handle a query, you must perform the following steps:

1. Use the DEFINE_COLUMN subprogram to specify variables for the return values of the SELECT statement.

2. Use the EXECUTE function to run the SELECT statement.

3. Use the FETCH_ROWS function to retrieve rows that meet the query criteria.

4. Use the COLUMN_VALUE subprogram to determine the column values retrieved by the FETCH_ROWS function.

If you use dynamic SQL to handle an INSERT, UPDATE, or DELETE statement, you must use the EXECUTE function to run the statement.

Execution process

The execution process of DBMS_SQL is as follows:

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 lists the DBMS_SQL subprograms supported in the current version of OceanBase Database and their brief descriptions.