The DBMS_SQL package provides an application interface compatible with Oracle databases to the EnterpriseDB dynamic SQL functionality. With
DBMS_SQL you can construct queries and other commands at run time (rather than when you write the application). EnterpriseDB Advanced Server offers native support for dynamic SQL;
DBMS_SQL provides a way to use dynamic SQL in a fashion compatible with Oracle databases without modifying your application.
DBMS_SQL assumes the privileges of the current user when executing dynamic SQL statements.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Bind a CHAR value to a variable.
|
|
|
|
|
Bind a RAW value to a variable.
|
|
|
|
|
|
COLUMN_VALUE(c, position, value OUT [, column_error OUT [, actual_length OUT ]])
|
|
|
|
|
|
|
|
Return a CHAR column value into a variable.
|
|
|
|
|
Return a RAW column value into a variable.
|
|
|
|
|
|
|
|
|
|
Define a CHAR column in the SELECT list.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
PARSE(c, statement, language_flag)
|
|
|
|
Advanced Server's implementation of DBMS_SQL is a partial implementation when compared to Oracle's version. Only those functions and procedures listed in the table above are supported.
The BIND_VARIABLE procedure provides the capability to associate a value with an
IN or
IN OUT bind variable in a
SQL command.
If name is an
IN OUT variable, defines the maximum length of the output value. If not specified, the length of
value is assumed.
The BIND_VARIABLE_CHAR procedure provides the capability to associate a
CHAR value with an
IN or
IN OUT bind variable in a
SQL command.
If name is an
IN OUT variable, defines the maximum length of the output value. If not specified, the length of
value is assumed.
The BIND_VARIABLE_RAW procedure provides the capability to associate a
RAW value with an
IN or
IN OUT bind variable in a
SQL command.
If name is an
IN OUT variable, defines the maximum length of the output value. If not specified, the length of
value is assumed.
The CLOSE_CURSOR procedure closes an open cursor. The resources allocated to the cursor are released and it can no longer be used.
The COLUMN_VALUE procedure defines a variable to receive a value from a cursor.
The COLUMN_VALUE_CHAR procedure defines a variable to receive a
CHAR value from a cursor.
The COLUMN_VALUE_RAW procedure defines a variable to receive a
RAW value from a cursor.
The DEFINE_COLUMN procedure defines a column or expression in the
SELECT list that is to be returned and retrieved in a cursor.
The following shows how the empno,
ename,
hiredate,
sal, and
comm columns of the
emp table are defined with the
DEFINE_COLUMN procedure.
The DEFINE_COLUMN_CHAR procedure defines a
CHAR column or expression in the
SELECT list that is to be returned and retrieved in a cursor.
The DEFINE_COLUMN_RAW procedure defines a
RAW column or expression in the
SELECT list that is to be returned and retrieved in a cursor.
The DESCRIBE_
COLUMNS procedure describes the columns returned by a cursor.
The EXECUTE function executes a parsed
SQL command or
SPL block.
Function EXECUTE_AND_FETCH executes a parsed
SELECT command and fetches one row.
If set to TRUE, an exception is thrown if the number of rows in the result set is not exactly equal to 1. If set to
FALSE, no exception is thrown. The default is
FALSE. A
NO_DATA_FOUND exception is thrown if
exact is
TRUE and there are no rows in the result set. A
TOO_MANY_ROWS exception is thrown if
exact is
TRUE and there is more than one row in the result set.
The following stored procedure uses the EXECUTE_AND_FETCH function to retrieve one employee using the employee’s name. An exception will be thrown if the employee is not found, or there is more than one employee with the same name.
The FETCH_ROWS function retrieves a row from a cursor.
Returns 1 if a row was successfully fetched,
0 if no more rows to fetch.
The IS_OPEN function provides the capability to test if the given cursor is open.
Set to TRUE if the cursor is open, set to
FALSE if the cursor is not open.
The LAST_ROW_COUNT function returns the number of rows that have been currently fetched.
The OPEN_CURSOR function creates a new cursor. A cursor must be used to parse and execute any dynamic
SQL statement. Once a cursor has been opened, it can be re-used with the same or different
SQL statements. The cursor does not have to be closed and re-opened in order to be re-used.
The PARSE procedure parses a
SQL command or
SPL block. If the
SQL command is a
DDL command, it is immediately executed and does not require running the
EXECUTE function.
SQL command or
SPL block to be parsed. A
SQL command must not end with the semi-colon terminator, however an
SPL block does require the semi-colon terminator.
Language flag provided for compatibility with Oracle syntax. Use
DBMS_SQL.V6,
DBMS_SQL.V7 or
DBMS_SQL.native. This flag is ignored, and all syntax is assumed to be in EnterpriseDB Advanced Server form.
The following anonymous block uses the DBMS_SQL package to execute a block containing two
INSERT statements. Note that the end of the block contains a terminating semi-colon, while in the prior example, each individual
INSERT statement does not have a terminating semi-colon.
