The DBMS_SQL package provides a mechanism for executing dynamic statements, supporting various types of statements such as system commands (e.g., ALTER SESSION), DDL, and DML (including SELECT).
Applicability
This content applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition only supports MySQL-compatible mode.
Consider the following usage notes:
Only DML statements can specify parameters.
Only DML statements that return result sets can be retrieved.
DML statements are executed in interfaces such as
dbms_sql.execute, while other statements are executed in thedbms_sql.parseinterface.Cursors created by
DBMS_SQLare only available in the current session and cannot be accessed by other sessions.
Privilege requirements
The DBMS_SQL package is owned by the SYS user and is a system package compiled with AUTHID CURRENT_USER. Any subprogram of DBMS_SQL called from an anonymous PL block runs with the privileges of the current user.
Constants
The following table lists the constants used for the language_flag parameter of the PARSE stored procedure in DBMS_SQL.
| Name | Type | Value | Description |
|---|---|---|---|
| NATIVE | INTEGER | 1 | The program is connected to the database normally. |
Considerations
The following considerations describe how to handle queries, updates, inserts, and deletes.
If you use dynamic SQL to handle queries, you must perform the following steps:
Call the
DEFINE_COLUMNsubprogram to specify variables for the return values of theSELECTstatement.Call the
EXECUTEfunction to run theSELECTstatement.Call the
FETCH_ROWSfunction to retrieve rows that meet the query criteria.Call the
COLUMN_VALUEsubprogram to determine the column values retrieved by theFETCH_ROWSfunction.
If you use dynamic SQL to handle INSERT, UPDATE, or DELETE, you must call the EXECUTE function to run the INSERT, UPDATE, or DELETE statement.
Execution process
The execution process of DBMS_SQL is as follows:
OPEN_CURSORPARSEBIND_VARIABLEDEFINE_COLUMN,DEFINE_COLUMN_LONG, orDEFINE_ARRAYEXECUTEFETCH_ROWSorEXECUTE_AND_FETCHCOLUMN_VALUECLOSE_CURSOR
Subprograms
The following table lists the DBMS_SQL subprograms supported in the current version of OceanBase Database and their brief descriptions.
| Subprogram | Description |
|---|---|
| BIND_VARIABLE | Binds a specified value to a specified variable. |
| COLUMN_VALUE | Returns the value of a specified element in the cursor. |
| CLOSE_CURSOR | Closes a specified cursor and releases memory. |
| DEFINE_COLUMN | Defines a column selected by a specified cursor, applicable only to SELECT statements. |
| DEFINE_ARRAY | Defines a collection to be selected from a specified cursor, applicable only to SELECT statements. |
| DESCRIBE_COLUMNS | Retrieves the field names and types of all columns in the cursor. |
| EXECUTE | Executes a specified cursor. |
| EXECUTE_AND_FETCH | Executes a specified cursor and retrieves rows. |
| FETCH_ROWS | Retrieves a row of data from a specified cursor. |
| IS_OPEN | Returns TRUE if the specified cursor is OPEN. |
| LAST_ERROR_POSITION | Retrieves the position where a syntax error occurred in the last call to DBMS_SQL.PARSE. |
| OPEN_CURSOR | Returns the ID of a new cursor. |
| PARSE | Parses a specified statement. |
| VARIABLE_VALUE | Retrieves the value of an output variable after executing a dynamic SQL statement. |