Table of Contents Previous Next


3 Built-In Packages : 3.16 DBMS_SQL

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_VARIABLE(c, name, value [, out_value_size ])
BIND_VARIABLE_CHAR(c, name, value [, out_value_size ])
Bind a CHAR value to a variable.
BIND_VARIABLE_RAW(c, name, value [, out_value_size ])
Bind a RAW value to a variable.
CLOSE_CURSOR(c IN OUT)
COLUMN_VALUE(c, position, value OUT [, column_error OUT [, actual_length OUT ]])
COLUMN_VALUE_CHAR(c, position, value OUT [, column_error OUT [, actual_length OUT ]])
Return a CHAR column value into a variable.
COLUMN_VALUE_RAW(c, position, value OUT [, column_error OUT [, actual_length OUT ]])
Return a RAW column value into a variable.
DEFINE_COLUMN(c, position, column [, column_size ])
DEFINE_COLUMN_CHAR(c, position, column, column_size)
Define a CHAR column in the SELECT list.
DEFINE_COLUMN_RAW(c, position, column, column_size)
Define a RAW column in the SELECT list.
EXECUTE_AND_FETCH(c [, exact ])
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.
BIND_VARIABLE(c INTEGER, name VARCHAR2,
value { BLOB | CLOB | DATE | FLOAT | INTEGER | NUMBER |
[, out_value_size INTEGER ])
Cursor ID of the cursor for the SQL command with bind variables.
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.
BIND_VARIABLE_CHAR(c INTEGER, name VARCHAR2, value CHAR
[, out_value_size INTEGER ])
Cursor ID of the cursor for the SQL command with bind variables.
Value of type CHAR to be assigned.
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.
BIND_VARIABLE_RAW(c INTEGER, name VARCHAR2, value RAW
[, out_value_size INTEGER ])
Cursor ID of the cursor for the SQL command with bind variables.
Value of type RAW to be assigned.
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.
CLOSE_CURSOR(c IN OUT INTEGER)
The COLUMN_VALUE procedure defines a variable to receive a value from a cursor.
COLUMN_VALUE(c INTEGER, position INTEGER, value OUT { BLOB |
[, column_error OUT NUMBER [, actual_length OUT INTEGER ]])
The COLUMN_VALUE_CHAR procedure defines a variable to receive a CHAR value from a cursor.
COLUMN_VALUE_CHAR(c INTEGER, position INTEGER, value OUT CHAR
[, column_error OUT NUMBER [, actual_length OUT INTEGER ]])
Variable of data type CHAR receiving the data returned in the cursor by a prior fetch call.
The COLUMN_VALUE_RAW procedure defines a variable to receive a RAW value from a cursor.
COLUMN_VALUE_RAW(c INTEGER, position INTEGER, value OUT RAW
[, column_error OUT NUMBER [, actual_length OUT INTEGER ]])
Variable of data type RAW receiving the data returned in the cursor by a prior fetch call.
The DEFINE_COLUMN procedure defines a column or expression in the SELECT list that is to be returned and retrieved in a cursor.
DEFINE_COLUMN(c INTEGER, position INTEGER, column { BLOB |
[, column_size INTEGER ])
Position of the column or expression in the SELECT list that is being defined.
The maximum length of the returned data. column_size must be specified only if column is VARCHAR2. Returned data exceeding column_size is truncated to column_size characters.
The following shows how the empno, ename, hiredate, sal, and comm columns of the emp table are defined with the DEFINE_COLUMN procedure.
The following shows an alternative to the prior example that produces the exact same results. Note that the lengths of the data types are irrelevant – the empno, sal, and comm columns will still return data equivalent to NUMBER(4) and NUMBER(7,2), respectively, even though v_num is defined as NUMBER(1) (assuming the declarations in the COLUMN_VALUE procedure are of the appropriate maximum sizes). The ename column will return data up to ten characters in length as defined by the length parameter in the DEFINE_COLUMN call, not by the data type declaration, VARCHAR2(1) declared for v_varchar. The actual size of the returned data is dictated by the COLUMN_VALUE 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.
DEFINE_COLUMN_CHAR(c INTEGER, position INTEGER, column CHAR, column_size INTEGER)
Position of the column or expression in the SELECT list that is being defined.
A CHAR variable.
The maximum length of the returned data. Returned data exceeding column_size is truncated to column_size characters.
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.
DEFINE_COLUMN_RAW(c INTEGER, position INTEGER, column RAW,
column_size INTEGER)
Position of the column or expression in the SELECT list that is being defined.
A RAW variable.
The maximum length of the returned data. Returned data exceeding column_size is truncated to column_size characters.
The DESCRIBE_COLUMNS procedure describes the columns returned by a cursor.
3.16.12 EXECUTE
The EXECUTE function executes a parsed SQL command or SPL block.
status INTEGER EXECUTE(c INTEGER)
Cursor ID of the parsed SQL command or SPL block to be executed.
Number of rows processed if the SQL command was DELETE, INSERT, or UPDATE. status is meaningless for all other commands.
Function EXECUTE_AND_FETCH executes a parsed SELECT command and fetches one row.
status INTEGER EXECUTE_AND_FETCH(c INTEGER
[, exact BOOLEAN ])
Cursor id of the cursor for the SELECT command to be executed.
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.
3.16.14 FETCH_ROWS
The FETCH_ROWS function retrieves a row from a cursor.
status INTEGER FETCH_ROWS(c INTEGER)
Returns 1 if a row was successfully fetched, 0 if no more rows to fetch.
The following examples fetches the rows from the emp table and displays the results.
3.16.15 IS_OPEN
The IS_OPEN function provides the capability to test if the given cursor is open.
status BOOLEAN IS_OPEN(c INTEGER)
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.
rowcnt INTEGER LAST_ROW_COUNT
The following example uses the LAST_ROW_COUNT function to display the total number of rows fetched in the query.
3.16.17 OPEN_CURSOR
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.
c INTEGER OPEN_CURSOR
3.16.18 PARSE
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.
PARSE(c INTEGER, statement VARCHAR2, language_flag INTEGER)
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 creates a table named, job. Note that DDL statements are executed immediately by the PARSE procedure and do not require a separate EXECUTE step.
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.

3 Built-In Packages : 3.16 DBMS_SQL

Table of Contents Previous Next