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/procedureFunction or procedureReturn typeDescription
BIND_VARIABLE(c, name, value [, out_value_size ])Proceduren/aBind a value to a variable.
BIND_VARIABLE_CHAR(c, name, value [, out_value_size ])Proceduren/aBind a CHAR value to a variable.
BIND_VARIABLE_RAW(c, name, value [, out_value_size ])Proceduren/aBind a RAW value to a variable.
CLOSE_CURSOR(c IN OUT)Proceduren/aClose a cursor.
COLUMN_VALUE(c, position, value OUT [, column_error OUT [, actual_length OUT ]])Proceduren/aReturn a column value into a variable.
COLUMN_VALUE_CHAR(c, position, value OUT [, column_error OUT [, actual_length OUT ]])Proceduren/aReturn a CHAR column value into a variable.
COLUMN_VALUE_RAW(c, position, value OUT [, column_error OUT [, actual_length OUT ]])Proceduren/aReturn a RAW column value into a variable.
COLUMN_VALUE_LONG(c, position, length, offset, value OUT, value_length OUTProceduren/aReturn a part of the LONG column value into a variable.
DEFINE_COLUMN(c, position, column [, column_size ])Proceduren/aDefine a column in the SELECT list.
DEFINE_COLUMN_CHAR(c, position, column, column_size)Proceduren/aDefine a CHAR column in the SELECT list.
DEFINE_COLUMN_RAW(c, position, column, column_size)Proceduren/aDefine a RAW column in the SELECT list.
DEFINE_COLUMN_LONG(c, position)Proceduren/aDefine a LONG column in the SELECT list.
DESCRIBE_COLUMNSProceduren/aDefine columns to hold a cursor result set.
EXECUTE(c)FunctionINTEGERExecute a cursor.
EXECUTE_AND_FETCH(c [, exact ])FunctionINTEGERExecute a cursor and fetch a single row.
FETCH_ROWS(c)FunctionINTEGERFetch rows from the cursor.
IS_OPEN(c)FunctionBOOLEANCheck if a cursor is open.
LAST_ROW_COUNTFunctionINTEGERReturn cumulative number of rows fetched.
LAST_ERROR_POSITIONFunctionINTEGERReturn byte offset in the SQL statement text where the error occurred.
OPEN_CURSORFunctionINTEGEROpen a cursor.
PARSE(c, statement, language_flag)Proceduren/aParse a statement.

The following table lists the public variables available in the DBMS_SQL package.

Public variablesData typeValueDescription
nativeINTEGER1Provided for compatibility with Oracle syntax. See DBMS_SQL.PARSE for more information.
V6INTEGER2Provided for compatibility with Oracle syntax. See DBMS_SQL.PARSE for more information.
V7INTEGER3Provided 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