The DBMS_SQL package supports dynamic SQL queries in PolarDB for PostgreSQL(Compatible with Oracle). You can build queries during runtime of the application.

PolarDB for PostgreSQL(Compatible with Oracle) offers native support for dynamic SQL statements and provides a method of using dynamic SQL statements that is compatible with Oracle databases.

Table 1. DBMS_SQL functions and stored procedures
Function or stored procedureTypeReturn value typeDescription
BIND_VARIABLE(c, name, value [, out_value_size ])Stored procedureN/ABinds a value to a variable.
BIND_VARIABLE_CHAR(c, name, value [, out_value_size ])Stored procedureN/ABinds a CHAR value to a variable.
BIND_VARIABLE_RAW(c, name, value [, out_value_size ])Stored procedureN/ABinds a RAW value to a variable.
CLOSE_CURSOR(c IN OUT)Stored procedureN/ADisables a cursor.
COLUMN_VALUE(c, position, value OUT [, column_error OUT [, actual_length OUT ]])Stored procedureN/AReturns a column value into a variable.
COLUMN_VALUE_CHAR(c, position, value OUT [, column_error OUT [, actual_length OUT ]])Stored procedureN/AReturns a CHAR column value into a variable.
COLUMN_VALUE_RAW(c, position, value OUT [, column_error OUT [, actual_length OUT ]])Stored procedureN/AReturns a RAW column value into a variable.
DEFINE_COLUMN(c, position, column [, column_size ])Stored procedureN/ADefines a column in the SELECT list.
DEFINE_COLUMN_CHAR(c, position, column, column_size)Stored procedureN/ADefines a CHAR column in the SELECT list.
DEFINE_COLUMN_RAW(c, position, column, column_size)Stored procedureN/ADefines a RAW column in the SELECT list.
DEFINE_ARRAY(c,position,table_variable,cnt, lower_bnd)Stored procedureN/ADefines the columns into which you want to fetch rows as arrays.
DESCRIBE_COLUMNSStored procedureN/ADefines columns to hold a cursor result set.
EXECUTE(c)FunctionINTEGERExecutes a cursor.
EXECUTE_AND_FETCH(c [, exact ])FunctionINTEGERExecutes a cursor and fetches a single row.
FETCH_ROWS(c)FunctionINTEGERFetches rows from the cursor.
IS_OPEN(c)FunctionBOOLEANChecks whether a cursor is enabled.
LAST_ROW_COUNTFunctionINTEGERReturns the cumulative number of rows fetched.
OPEN_CURSORFunctionINTEGEREnables a cursor
PARSE(c, statement, language_flag)Stored procedureN/AParses a statement.

The DBMS_SQL package in PolarDB is partially implemented when compared to Oracle DBMS_SQL package. PolarDB only supports the functions and stored procedures that are listed in the preceding table.

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

Table 2. DBMS_SQL public variables
Public variableData typeValueDescription
nativeINTEGER1Used for compatibility with Oracle syntax. For more information, see DBMS_SQL.PARSE.
V6INTEGER2Used for compatibility with Oracle syntax. For more information, see DBMS_SQL.PARSE.
V7INTEGER3Used for compatibility with Oracle syntax. For more information, see DBMS_SQL.PARSE.

BIND_VARIABLE

The BIND_VARIABLE stored procedure is used to bind a value to an IN or IN OUT bind variable in an SQL statement.

BIND_VARIABLE(c INTEGER, name VARCHAR2,
  value { BLOB | CLOB | DATE | FLOAT | INTEGER | NUMBER |
          TIMESTAMP | VARCHAR2 }
  [, out_value_size INTEGER ])

Parameters

ParameterDescription
cThe ID of the cursor for the SQL statement which contains bind variables.
nameThe name of the bind variable in the SQL statement.
valueThe value to be assigned.
out_value_sizeIf name is an IN OUT variable, this parameter defines the maximum length of the output value. If this parameter is not specified, the length of the current value is the maximum length by default.

Examples

The following anonymous block uses bind variables to insert a row into the emp table.

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(150) := 'INSERT INTO emp VALUES ' ||
                        '(:p_empno, :p_ename, :p_job, :p_mgr, ' ||
                        ':p_hiredate, :p_sal, :p_comm, :p_deptno)';
    v_empno         emp.empno%TYPE;
    v_ename         emp.ename%TYPE;
    v_job           emp.job%TYPE;
    v_mgr           emp.mgr%TYPE;
    v_hiredate      emp.hiredate%TYPE;
    v_sal           emp.sal%TYPE;
    v_comm          emp.comm%TYPE;
    v_deptno        emp.deptno%TYPE;
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    v_empno    := 9001;
    v_ename    := 'JONES';
    v_job      := 'SALESMAN';
    v_mgr      := 7369;
    v_hiredate := TO_DATE('13-DEC-07','DD-MON-YY');
    v_sal      := 8500.00;
    v_comm     := 1500.00;
    v_deptno   := 40;
    DBMS_SQL.BIND_VARIABLE(curid,':p_empno',v_empno);
    DBMS_SQL.BIND_VARIABLE(curid,':p_ename',v_ename);
    DBMS_SQL.BIND_VARIABLE(curid,':p_job',v_job);
    DBMS_SQL.BIND_VARIABLE(curid,':p_mgr',v_mgr);
    DBMS_SQL.BIND_VARIABLE(curid,':p_hiredate',v_hiredate);
    DBMS_SQL.BIND_VARIABLE(curid,':p_sal',v_sal);
    DBMS_SQL.BIND_VARIABLE(curid,':p_comm',v_comm);
    DBMS_SQL.BIND_VARIABLE(curid,':p_deptno',v_deptno);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;
A similar output is displayed:
Number of rows processed: 1

BIND_VARIABLE_CHAR

The BIND_VARIABLE_CHAR stored procedure is used to bind a CHAR value to an IN or IN OUT bind variable in an SQL statement.

BIND_VARIABLE_CHAR(c INTEGER, name VARCHAR2, value CHAR
  [, out_value_size INTEGER ])

Parameters

ParameterDescription
cThe ID of the cursor for the SQL statement which contains bind variables.
nameThe name of the bind variable in the SQL statement.
valueThe value of type CHAR to be assigned.
out_value_sizeIf name is an IN OUT variable, this parameter defines the maximum length of the output value. If this parameter is not specified, the length of the current value is the maximum length by default.

BIND_VARIABLE_RAW

The BIND_VARIABLE_RAW stored procedure is used to bind a RAW value to an IN or IN OUT bind variable in an SQL statement.

BIND_VARIABLE_RAW(c INTEGER, name VARCHAR2, value RAW
  [, out_value_size INTEGER ])

Parameters

ParameterDescription
cThe ID of the cursor for the SQL statement which contains bind variables.
nameThe name of the bind variable in the SQL statement.
valueThe value of type CHAR to be assigned.
out_value_sizeIf name is an IN OUT variable, this parameter defines the maximum length of the output value. If this parameter is not specified, the length of the current value is the maximum length by default.

CLOSE_CURSOR

The CLOSE_CURSOR stored procedure is used to disable a cursor. When the cursor is disabled, resources allocated to the cursor are released and the cursor can no longer be used.

CLOSE_CURSOR(c IN OUT INTEGER)
            

Parameters

ParameterDescription
cThe ID of the cursor.

Examples

The following example shows how to disable a cursor.
DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
            .
            .
            .
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

COLUMN_VALUE

The COLUMN_VALUE stored procedure is used to define a variable to receive a value from a cursor.

COLUMN_VALUE(c INTEGER, position INTEGER, value OUT { BLOB |
  CLOB | DATE | FLOAT | INTEGER | NUMBER | TIMESTAMP | VARCHAR2 }
  [, column_error OUT NUMBER [, actual_length OUT INTEGER ]])

Parameters

ParameterDescription
cThe ID of the cursor that returns data to the variable being defined.
positionThe position of the returned data within the cursor. The first value in the cursor is position 1.
valueThe variable that receives the data returned in the cursor by a prior FETCH call.
column_errorIf an error occurs, this parameter indicates the error code associated with the column.
actual_lengthThe actual length of the data before truncation.

Examples

The following example shows the portion of an anonymous block that receives the values from a cursor by using the COLUMN_VALUE stored procedure.

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
            .
            .
            .
    LOOP
        v_status := DBMS_SQL.FETCH_ROWS(curid);
        EXIT WHEN v_status = 0;
        DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
        DBMS_SQL.COLUMN_VALUE(curid,2,v_ename);
        DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,5,v_comm);
        DBMS_OUTPUT.PUT_LINE(v_empno || '   ' || RPAD(v_ename,10) || '  ' ||
            TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' ||
            TO_CHAR(v_sal,'9,999.99') || ' ' ||
            TO_CHAR(NVL(v_comm,0),'9,999.99'));
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

COLUMN_VALUE_CHAR

The COLUMN_VALUE_CHAR stored procedure is used to define 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 ]])

Parameters

ParameterDescription
cThe ID of the cursor that returns data to the variable being defined.
positionThe position of the returned data within the cursor. The first value in the cursor is position 1.
valueThe variable of data type CHAR that receives the data returned in the cursor by a prior FETCH call.
column_errorIf an error occurs, this parameter indicates the error code associated with the column.
actual_lengthThe actual length of the data before truncation.

COLUMN_VALUE_RAW

The COLUMN_VALUE_RAW stored procedure is used to define 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 ]])

Parameters

ParameterDescription
cThe ID of the cursor that returns data to the variable being defined.
positionThe position of the returned data within the cursor. The first value in the cursor is position 1.
valueThe variable of data type RAW that receives the data returned in the cursor by a prior FETCH call.
column_errorIf an error occurs, this parameter indicates the error code associated with the column.
actual_lengthThe actual length of the data before truncation.

DEFINE_COLUMN

The DEFINE_COLUMN stored procedure is used to define a column or expression in the SELECT list that is to be returned and fetched in a cursor.

DEFINE_COLUMN(c INTEGER, position INTEGER, column { BLOB |
  CLOB | DATE | FLOAT | INTEGER | NUMBER | TIMESTAMP | VARCHAR2 }
  [, column_size INTEGER ])

Parameters

ParameterDescription
cThe ID of the cursor associated with the SELECT statement.
positionThe position of the column or expression in the SELECT list that is being defined.
columnA variable that matches the data type of the column or expression in the specified position of the SELECT result set.
column_sizeThe maximum length of the returned data. The column_size parameter must be specified if the data type of the column is VARCHAR2. Returned data exceeding column_size is truncated to the maximum length specified by the column_size parameter.

Examples

The following example shows how to use the DEFINE_COLUMN stored procedure to define the empno, ename, hiredate, sal, and comm columns of the emp table.

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm);
            .
            .
            .
END;

The following example shows an alternative to the preceding 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), even though v_num is defined as NUMBER(1). The ename column will return data up to ten characters in length as defined by the length parameter in the DEFINE_COLUMN call. The length that is indicated by the data type VARCHAR2(1) declared for v_varchar is ignored. The actual size of the returned data is determined by the DEFINE_COLUMN stored procedure.

DECLARE
    curid           INTEGER;
    v_num           NUMBER(1);
    v_varchar       VARCHAR2(1);
    v_date          DATE;
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_num);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_varchar,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_date);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_num);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_num);
            .
            .
            .
END;

DEFINE_COLUMN_CHAR

The DEFINE_COLUMN_CHAR stored procedure is used to define a CHAR column or expression in the SELECT list that is to be returned and fetched in a cursor.

DEFINE_COLUMN_CHAR(c INTEGER, position INTEGER, column CHAR, column_size INTEGER)

Parameters

ParameterDescription
cThe ID of the cursor associated with the SELECT statement.
positionThe position of the column or expression in the SELECT list that is being defined.
columnA CHAR variable.
column_sizeThe maximum length of the returned data. Returned data exceeding column_size is truncated to column_size characters.

DEFINE_COLUMN_RAW

The DEFINE_COLUMN_RAW stored procedure is used to define a RAW column or expression in the SELECT list that is to be returned and fetched in a cursor.

DEFINE_COLUMN_RAW(c INTEGER, position INTEGER, column RAW,
  column_size INTEGER)

Parameters

ParameterDescription
cThe ID of the cursor associated with the SELECT statement.
positionThe position of the column or expression in the SELECT list that is being defined.
columnA RAW variable.
column_sizeThe maximum length of the returned data. Returned data exceeding column_size is truncated to column_size characters.

DEFINE_ARRAY

The DEFINE_ARRAY stored procedure defines the columns into which you want to fetch rows as arrays. Syntax:
DEFINE_ARRAY (
   c           IN INTEGER,
   position    IN INTEGER,
   <table_variable>    IN <datatype>,
   cnt         IN INTEGER,
   lower_bnd   IN INTEGER);
Parameters
ParameterDescription
cThe ID of the cursor to be bound to the array.
positionThe relative position of the column in the array.
table_variableThe variable that is declared as <datatype>. Valid values of <datatype>:
  • varchar2_table
  • clob_table
  • binary_float_table
  • binary_double_table
  • blob_table
  • date_table
  • number_table
  • timestamp_table
cntThe number of rows fetched. It must be an integer that is greater than 0.
lower_bndResults are copied into the array, starting at this lower bound index.

Examples

The following anonymous block creates a table named t and fetches two rows of data from the t table.
create table t as select i as a,2 * i as b,3 * i as c from generate_series(1,3) i;

DECLARE
  c      INTEGER;
  d      NUMBER;
  n_tab  dbms_sql.varchar2_Table;
  n_tab1  dbms_sql.varchar2_Table;
BEGIN
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,
                 'select * from t',
                 dbms_sql.native);
  dbms_sql.define_array(c,1,n_tab,2,1);
  d := dbms_sql.execute(c);
  d := dbms_sql.fetch_rows(c);
  dbms_output.put_line('fetch rows is ' || d);
  dbms_sql.column_value(c,
                          1,
                          n_tab1);
    FOR i IN 1 .. d LOOP
      dbms_output.put_line(n_tab1(i));
    END LOOP;
  dbms_sql.close_cursor(c);
END;
A similar output is displayed:
fetch rows is 2
1
2

DESCRIBE_COLUMNS

The DESCRIBE_COLUMNS stored procedure is used to describe the columns returned by a cursor.

DESCRIBE_COLUMNS(c INTEGER, col_cnt OUT INTEGER, desc_t OUT
  DESC_TAB);

Parameters

ParameterDescription
cThe ID of the cursor.
col_cntThe number of columns in the cursor result set.
desc_tabThe table that contains a description of each column returned by the cursor. The descriptions are of type DESC_REC, and contain the values in the following table.
Column nameType
col_typeINTEGER
col_max_lenINTEGER
col_nameVARCHAR2(128)
col_name_lenINTEGER
col_schema_nameVARCHAR2(128)
col_schema_name_lenINTEGER
col_precisionINTEGER
col_scaleINTEGER
col_charsetidINTEGER
col_charsetformINTEGER
col_null_okBOOLEAN

EXECUTE

The EXECUTE function is used to execute a parsed SQL statement or SPL block.

status INTEGER EXECUTE(c INTEGER)
            

Parameters

ParameterDescription
cThe cursor ID of the parsed SQL statement or SPL block.
statusIf the SQL statement is DELETE, INSERT, or UPDATE, this parameter indicates the number of records processed. This parameter is meaningless for other statements.

Examples

The following anonymous block inserts a row into the dept table.

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(50);
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    v_sql := 'INSERT INTO dept VALUES (50, ''HR'', ''LOS ANGELES'')';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

EXECUTE_AND_FETCH

The EXECUTE_AND_FETCH function is used to execute a parsed SELECT statement and fetch one row.

status INTEGER EXECUTE_AND_FETCH(c INTEGER
  [, exact BOOLEAN ])

Parameters

ParameterDescription
cThe ID of the cursor for the SELECT statement.
exact
  • If this parameter is set to FALSE, no exception occurs. Default value: FALSE.
  • If this parameter is set to TRUE and the number of rows in the result set is not equal to 1, an exception occurs.
  • If this parameter is set to TRUE and the result set contains no records, a NO_DATE_FOUND exception will occur.
  • If this parameter is set to TRUE and the result set contains multiple records, a TOO_MANY_ROWS exception will occur.
status
  • If a row is fetched, 1 is returned.
  • If no rows are fetched, 0 is returned.
  • If an exception occurs, no value is returned.

Examples

The following stored procedure uses the EXECUTE_AND_FETCH function to fetch one employee by using the employee's name. If the employee is not found, or more than one employees with the same name are found, an exception will occur.

CREATE OR REPLACE PROCEDURE select_by_name(
    p_ename         emp.ename%TYPE
)
IS
    curid           INTEGER;
    v_empno         emp.empno%TYPE;
    v_hiredate      emp.hiredate%TYPE;
    v_sal           emp.sal%TYPE;
    v_comm          emp.comm%TYPE;
    v_dname         dept.dname%TYPE;
    v_disp_date     VARCHAR2(10);
    v_sql           VARCHAR2(120) := 'SELECT empno, hiredate, sal, ' ||
                                     'NVL(comm, 0), dname ' ||
                                     'FROM emp e, dept d ' ||
                                     'WHERE ename = :p_ename ' ||
                                     'AND e.deptno = d.deptno';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.BIND_VARIABLE(curid,':p_ename',UPPER(p_ename));
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_comm);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_dname,14);
    v_status := DBMS_SQL.EXECUTE_AND_FETCH(curid,TRUE);
    DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
    DBMS_SQL.COLUMN_VALUE(curid,2,v_hiredate);
    DBMS_SQL.COLUMN_VALUE(curid,3,v_sal);
    DBMS_SQL.COLUMN_VALUE(curid,4,v_comm);
    DBMS_SQL.COLUMN_VALUE(curid,5,v_dname);
    v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY');
    DBMS_OUTPUT.PUT_LINE('Number    : ' || v_empno);
    DBMS_OUTPUT.PUT_LINE('Name      : ' || UPPER(p_ename));
    DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date);
    DBMS_OUTPUT.PUT_LINE('Salary    : ' || v_sal);
    DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm);
    DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname);
    DBMS_SQL.CLOSE_CURSOR(curid);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || p_ename || ' not found');
        DBMS_SQL.CLOSE_CURSOR(curid);
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Too many employees named, ' ||
            p_ename || ', found');
        DBMS_SQL.CLOSE_CURSOR(curid);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:');
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
        DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:');
        DBMS_OUTPUT.PUT_LINE(SQLCODE);
        DBMS_SQL.CLOSE_CURSOR(curid);
END;
            
A similar output is displayed:
EXEC select_by_name('MARTIN')

Number    : 7654
Name      : MARTIN
Hire Date : 09/28/1981
Salary    : 1250
Commission: 1400
Department: SALES

FETCH_ROWS

The FETCH_ROWS function is used to fetch a row from a cursor.

status INTEGER FETCH_ROWS(c INTEGER)         

Parameters

ParameterDescription
cThe ID of the cursor used to fetch a row.
statusIf a row is fetched, 1 is returned. If no rows are fetched, 0 is returned.

Examples

The following example fetches the rows from the emp table and displays the results.

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm);

    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('EMPNO  ENAME       HIREDATE    SAL       COMM');
    DBMS_OUTPUT.PUT_LINE('-----  ----------  ----------  --------  ' ||
        '--------');
    LOOP
        v_status := DBMS_SQL.FETCH_ROWS(curid);
        EXIT WHEN v_status = 0;
        DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
        DBMS_SQL.COLUMN_VALUE(curid,2,v_ename);
        DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,5,v_comm);
        DBMS_OUTPUT.PUT_LINE(v_empno || '   ' || RPAD(v_ename,10) || '  ' ||
            TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' ||
            TO_CHAR(v_sal,'9,999.99') || ' ' ||
            TO_CHAR(NVL(v_comm,0),'9,999.99'));
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(curid);
END;
A similar output is displayed:
EMPNO  ENAME       HIREDATE    SAL       COMM
-----  ----------  ----------  --------  --------
7369   SMITH       1980-12-17    800.00       .00
7499   ALLEN       1981-02-20  1,600.00    300.00
7521   WARD        1981-02-22  1,250.00    500.00
7566   JONES       1981-04-02  2,975.00       .00
7654   MARTIN      1981-09-28  1,250.00  1,400.00
7698   BLAKE       1981-05-01  2,850.00       .00
7782   CLARK       1981-06-09  2,450.00       .00
7788   SCOTT       1987-04-19  3,000.00       .00
7839   KING        1981-11-17  5,000.00       .00
7844   TURNER      1981-09-08  1,500.00       .00
7876   ADAMS       1987-05-23  1,100.00       .00
7900   JAMES       1981-12-03    950.00       .00
7902   FORD        1981-12-03  3,000.00       .00
7934   MILLER      1982-01-23  1,300.00       .00

IS_OPEN

The IS_OPEN function is used to check whether the specified cursor is enabled.

status BOOLEAN IS_OPEN(c INTEGER)
            

Parameters

ParameterDescription
cThe ID of the cursor to be checked.
statusIf the cursor is enabled, this parameter is set to TRUE. If the cursor is disabled, this parameter is set to FALSE.

LAST ROW COUNT

The LAST_ROW_COUNT function is used to return the total number of rows that are fetched.

rowcnt INTEGER LAST_ROW_COUNT
            

Parameters

ParameterDescription
rowcntThe total number of fetched rows.

Examples

The following example uses the LAST_ROW_COUNT function to display the total number of rows fetched in the query.

DECLARE
    curid           INTEGER;
    v_empno         NUMBER(4);
    v_ename         VARCHAR2(10);
    v_hiredate      DATE;
    v_sal           NUMBER(7,2);
    v_comm          NUMBER(7,2);
    v_sql           VARCHAR2(50) := 'SELECT empno, ename, hiredate, sal, ' ||
                                    'comm FROM emp';
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid,v_sql,DBMS_SQL.native);
    DBMS_SQL.DEFINE_COLUMN(curid,1,v_empno);
    DBMS_SQL.DEFINE_COLUMN(curid,2,v_ename,10);
    DBMS_SQL.DEFINE_COLUMN(curid,3,v_hiredate);
    DBMS_SQL.DEFINE_COLUMN(curid,4,v_sal);
    DBMS_SQL.DEFINE_COLUMN(curid,5,v_comm);

    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('EMPNO  ENAME       HIREDATE    SAL       COMM');
    DBMS_OUTPUT.PUT_LINE('-----  ----------  ----------  --------  ' ||
        '--------');
    LOOP
        v_status := DBMS_SQL.FETCH_ROWS(curid);
        EXIT WHEN v_status = 0;
        DBMS_SQL.COLUMN_VALUE(curid,1,v_empno);
        DBMS_SQL.COLUMN_VALUE(curid,2,v_ename);
        DBMS_SQL.COLUMN_VALUE(curid,3,v_hiredate);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,4,v_sal);
        DBMS_SQL.COLUMN_VALUE(curid,5,v_comm);
        DBMS_OUTPUT.PUT_LINE(v_empno || '   ' || RPAD(v_ename,10) || '  ' ||
            TO_CHAR(v_hiredate,'yyyy-mm-dd') || ' ' ||
            TO_CHAR(v_sal,'9,999.99') || ' ' ||
            TO_CHAR(NVL(v_comm,0),'9,999.99'));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Number of rows: ' || DBMS_SQL.LAST_ROW_COUNT);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;
A similar output is displayed:
EMPNO  ENAME       HIREDATE    SAL       COMM
-----  ----------  ----------  --------  --------
7369   SMITH       1980-12-17    800.00       .00
7499   ALLEN       1981-02-20  1,600.00    300.00
7521   WARD        1981-02-22  1,250.00    500.00
7566   JONES       1981-04-02  2,975.00       .00
7654   MARTIN      1981-09-28  1,250.00  1,400.00
7698   BLAKE       1981-05-01  2,850.00       .00
7782   CLARK       1981-06-09  2,450.00       .00
7788   SCOTT       1987-04-19  3,000.00       .00
7839   KING        1981-11-17  5,000.00       .00
7844   TURNER      1981-09-08  1,500.00       .00
7876   ADAMS       1987-05-23  1,100.00       .00
7900   JAMES       1981-12-03    950.00       .00
7902   FORD        1981-12-03  3,000.00       .00
7934   MILLER      1982-01-23  1,300.00       .00
Number of rows: 14

OPEN_CURSOR

The OPEN_CURSOR function is used to create a cursor. A cursor must be used to parse and execute a dynamic SQL statements. After being enabled, a cursor can be re-used with the same or different SQL statements. without the need for you to disable and enable again the cursor.

c INTEGER OPEN_CURSOR
            

Parameters

ParameterDescription
cThe ID of the newly created cursor.

Examples

The following example shows how to create a new cursor.

DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
            .
            .
            .
END;

PARSE

The PARSE stored procedure is used to parse an SQL statement or SPL block. For a DDL statement, it is immediately executed and does not require calling the EXECUTE function.

PARSE(c INTEGER, statement VARCHAR2, language_flag INTEGER)
            

Parameters

ParameterDescription
cThe ID of an enabled cursor.
statementThe SQL statement or SPL block to be parsed. An SQL statement cannot end with a semicolon (;). An SPL block must end with a semicolon (;).
language_flagUsed for compatibility with Oracle syntax. Valid values: DBMS_SQL.V6, DBMS_SQL.V7, and DBMS_SQL.native. This flag is ignored, and all syntax is in PolarDB for PostgreSQL(Compatible with Oracle) form.

Examples

The following anonymous block creates a table named job. Note that DDL statements are immediately executed by the PARSE stored procedure and do not require calling the EXECUTE function.

DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(curid, 'CREATE TABLE job (jobno NUMBER(3), ' ||
        'jname VARCHAR2(9))',DBMS_SQL.native);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

The following code snippet inserts two rows into the job table.

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(50);
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    v_sql := 'INSERT INTO job VALUES (100, ''ANALYST'')';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
    v_sql := 'INSERT INTO job VALUES (200, ''CLERK'')';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_OUTPUT.PUT_LINE('Number of rows processed: ' || v_status);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;
            
A similar output is displayed:
Number of rows processed: 1
Number of rows processed: 1

The following anonymous block uses the DBMS_SQL package to execute a block that contains two INSERT statements. Note that the end of the block contains a terminating semicolon (;), while in the example of OPEN_CURSOR, each individual INSERT statement does not have a terminating semicolon (;).

DECLARE
    curid           INTEGER;
    v_sql           VARCHAR2(100);
    v_status        INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    v_sql := 'BEGIN ' ||
               'INSERT INTO job VALUES (300, ''MANAGER''); '  ||
               'INSERT INTO job VALUES (400, ''SALESMAN''); ' ||
             'END;';
    DBMS_SQL.PARSE(curid, v_sql, DBMS_SQL.native);
    v_status := DBMS_SQL.EXECUTE(curid);
    DBMS_SQL.CLOSE_CURSOR(curid);
END;