Overview of DBMS_SQL

The DBMS_SQL system package provides a mechanism for executing dynamic statements, supporting various types of statements: system commands (such as ALTER SESSION), DDL, and DML (including SELECT).

Consider the following points:

• Only DML statements can have parameters specified.

• Only statements that return a result set can have their results fetched in DML statements.

• DML statements are executed in interfaces such as dbms_sql.execute, while other statements are executed in the dbms_sql.parse interface.

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

DBMS_SQL Privileges

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

DBMS_SQL constants

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

Usage instructions

The following usage instructions describe how to perform queries, updates, inserts, and deletes.

If you use dynamic SQL to process queries, you must perform the following steps:

1. Specify the variables for the return values of the SELECT statement by calling the DEFINE_COLUMN subroutine.

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

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

4. Call the COLUMN_VALUE subprogram to determine the column values retrieved by the query returned by the FETCH_ROWS function.

If you use dynamic SQL to handle INSERT, UPDATE, or DELETE statements, you must execute the INSERT, UPDATE, or DELETE statement by calling the EXECUTE function.

Execution process of DBMS_SQL

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

Overview of DBMS_SQL Subprograms

The following table lists the DBMS_SQL subprograms supported by the current version of OceanBase Database and provides brief descriptions.