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.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
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.executeinterface, other statements are executed in thedbms_sql.parseinterface.A cursor created by
DBMS_SQLcan 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.
| Name | Type | Value | Description |
|---|---|---|---|
| NATIVE | INTEGER | 1 | Normal connection from the program to the database. |
Considerations
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:
Specify the variables that receive the return value of
SELECTstatements by calling theDEFINE_COLUMNsubprogram.Execute the
SELECTstatement by calling theEXECUTEfunction.Retrieve the rows that satisfy your query by calling the
FETCH_ROWSfunction.Determine the value of a column returned by
FETCH_ROWSfor your query by calling theCOLUMN_VALUEsubprogram.
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 as follows:
OPEN_CURSORPARSEBIND_VARIABLEDEFINE_COLUMN,DEFINE_COLUMN_LONG, orDEFINE_ARRAYEXECUTEFETCH_ROWSorEXECUTE_AND_FETCHCOLUMN_VALUECLOSE_CURSOR
Subprograms
The following table describes the DBMS_SQL subprograms supported by the current OceanBase Database version.
| Subprogram | Description |
|---|---|
| BIND_VARIABLE | Binds a specified value to a specified variable. |
| COLUMN_VALUE | Returns the value of a specified cursor element. |
| CLOSE_CURSOR | Closes a specified cursor and releases the occupied memory. |
| DEFINE_COLUMN | Defines a column to be selected from a specified cursor. This subprogram is only used with SELECT statements. |
| DEFINE_ARRAY | Defines a set to be selected from a specified cursor. It is applicable only to SELECT statements. |
| DESCRIBE_COLUMNS | Describes all attributes of the cursor, such as the field name and type. |
| EXECUTE | Executes a specified cursor. |
| EXECUTE_AND_FETCH | Executes a specified cursor and fetches rows. |
| FETCH_ROWS | Fetches a row of data from a specified cursor. |
| IS_OPEN | Returns TRUE if the specified cursor is in the OPEN state. |
| LAST_ERROR_POSITION | Returns the location of the syntax error in the last call of DBMS_SQL.PARSE. |
| OPEN_CURSOR | Returns the ID of the new cursor. |
| PARSE | Parses a specified statement. |