DBMS_SQL v15
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_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. |
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. |
DESCRIBE_COLUMNS | 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. |
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