DBMS_SQL v16
The DBMS_SQL package provides an application interface compatible with Oracle databases to the EDB dynamic SQL functionality. With DBMS_SQL you can construct queries and other commands at runtime rather than when you write the application. EDB Postgres Advanced Server offers native support for dynamic SQL. DBMS_SQL provides a way to use dynamic SQL in a way that's compatible with Oracle databases without modifying your application.
DBMS_SQL assumes the privileges of the current user when executing dynamic SQL statements.
EDB Postgres 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 are supported.
| Function/procedure | Function or procedure | Return type | Description |
|---|---|---|---|
BIND_ARRAY(c IN, name IN, table_variable IN [index1 IN, index2 IN]) | Procedure | n/a | Binds a value or set of values to a variable. |
BIND_VARIABLE(c, name, value [, out_value_size ]) | Procedure | n/a | Bind a value to a variable. |
BIND_VARIABLE_CHAR(c, name, value [, out_value_size ]) | Procedure | n/a | Bind a CHAR value to a variable. |
BIND_VARIABLE_RAW(c, name, value [, out_value_size ]) | Procedure | n/a | Bind a RAW value to a variable. |
CLOSE_CURSOR(c IN OUT) | Procedure | n/a | Close a cursor. |
COLUMN_VALUE(c, position, value OUT [, column_error OUT [, actual_length OUT ]]) | Procedure | n/a | Return a column value into a variable. |
COLUMN_VALUE_CHAR(c, position, value OUT [, column_error OUT [, actual_length OUT ]]) | Procedure | n/a | Return a CHAR column value into a variable. |
COLUMN_VALUE_RAW(c, position, value OUT [, column_error OUT [, actual_length OUT ]]) | Procedure | n/a | Return a RAW column value into a variable. |
COLUMN_VALUE_LONG(c, position, length, offset, value OUT, value_length OUT) | Procedure | n/a | Return a part of the LONG column value into a variable. |
COLUMN_VALUE_ROWID(c, position, value OUT [ column_error OUT [, actual_length OUT ]]) | Procedure | n/a | Return a ROWID column in a cursor. |
DEFINE_ARRAY(c IN, position IN, table_variable IN, cnt IN, lower_bnd IN ) | Procedure | n/a | Define collection for column into which to fetch rows. |
DEFINE_COLUMN(c, position, column [, column_size ]) | Procedure | n/a | Define a column in the SELECT list. |
DEFINE_COLUMN_CHAR(c, position, column, column_size) | Procedure | n/a | Define a CHAR column in the SELECT list. |
DEFINE_COLUMN_RAW(c, position, column, column_size) | Procedure | n/a | Define a RAW column in the SELECT list. |
DEFINE_COLUMN_LONG(c, position) | Procedure | n/a | Define a LONG column in the SELECT list. |
DEFINE_COLUMN_ROWID(c IN, position IN, column IN) | Procedure | n/a | Define a ROWID column in the SELECT list. |
DESCRIBE_COLUMNS(c IN, col_cnt OUT, desc_t OUT, DESC_TAB) | Procedure | n/a | Define columns to hold a cursor result set. |
DESCRIBE_COLUMNS2(c IN, col_cnt OUT, desc_t OUT, DESC_TAB) | Procedure | n/a | Define columns to hold a cursor result set. |
DESCRIBE_COLUMNS3(c IN, col_cnt OUT, desc_t OUT, DESC_TAB) | Procedure | n/a | Define columns to hold a cursor result set. |
EXECUTE(c) | Function | INTEGER | Execute a cursor. |
EXECUTE_AND_FETCH(c [, exact ]) | Function | INTEGER | Execute a cursor and fetch a single row. |
FETCH_ROWS(c) | Function | INTEGER | Fetch rows from the cursor. |
IS_OPEN(c) | Function | BOOLEAN | Check if a cursor is open. |
LAST_ROW_COUNT | Function | INTEGER | Return cumulative number of rows fetched. |
LAST_ERROR_POSITION | Function | INTEGER | Return byte offset in the SQL statement text where the error occurred. |
OPEN_CURSOR | Function | INTEGER | Open a cursor. |
PARSE(c, statement, language_flag) | Procedure | n/a | Parse a statement. |
TO_CURSOR_NUMBER (rc IN OUT ) | Function | INTEGER | Transform a ref cursor into a SQL cursor number. |
TO_REFCURSOR (cursor_number IN OUT) | Function | SYS_REFCURSOR | Transform an open cursor into a REF CURSOR. |
VARIABLE_VALUE | Not supported in EPAS |
The following table lists the public variables available in the DBMS_SQL package.
| Public variables | Data type | Value | Description |
|---|---|---|---|
native | INTEGER | 1 | Provided for compatibility with Oracle syntax. See DBMS_SQL.PARSE for more information. |
V6 | INTEGER | 2 | Provided for compatibility with Oracle syntax. See DBMS_SQL.PARSE for more information. |
V7 | INTEGER | 3 | Provided for compatibility with Oracle syntax. See DBMS_SQL.PARSE for more information |
bind_variable bind_variable_char bind_variable_raw close_cursor column_value column_value_char column_value_raw define_column define_column_char define_column_raw describe_columns execute execute_and_fetch fetch_rows is_open last_row_count open_cursor parse