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.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
Consider the following usage notes:
Only DML statements can specify parameters.
Only DML statements that return result sets can be fetched.
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 available only in the current session and cannot be accessed by other sessions.
Privilege requirements for DBMS_SQL
The DBMS_SQL package is owned by the SYS user and is 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 for DBMS_SQL
The following table lists the constants used by 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 a query, 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 an INSERT, UPDATE, or DELETE statement, you must call the EXECUTE function to run the statement.
Execution process of DBMS_SQL
The execution process of DBMS_SQL is as follows:
OPEN_CURSORPARSEBIND_VARIABLEDEFINE_COLUMN,DEFINE_COLUMN_LONG, orDEFINE_ARRAYEXECUTEFETCH_ROWSorEXECUTE_AND_FETCHCOLUMN_VALUECLOSE_CURSOR
Subprograms of DBMS_SQL
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 columns 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 all field names and types, etc., of 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 in the OPEN state. |
| 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. |