The DBMS_SQL package provides an application interface compatible with Oracle databases to the PolarDB-O dynamic SQL functionality. By using the DBMS_SQL package, you can construct queries and other commands at run time, rather than when you write the application. PolarDB-O offers native support for dynamic SQL. The DBMS_SQL package provides a method of using dynamic SQL that is compatible with Oracle databases without modifying your application.

The DBMS_SQL package assumes that the current user has the required permissions when running dynamic SQL statements.

Table 1. DBMS_SQL functions and stored procedures
Function/stored procedure Function or stored procedure Return type Description
BIND_VARIABLE(c, name, value [, out_value_size ]) Stored procedure N/A Binds a value to a variable.
BIND_VARIABLE_CHAR(c, name, value [, out_value_size ]) Stored procedure N/A Binds a CHAR value to a variable.
BIND_VARIABLE_RAW(c, name, value [, out_value_size ]) Stored procedure N/A Binds a RAW value to a variable.
CLOSE_CURSOR(c IN OUT) Stored procedure N/A Closes a cursor.
COLUMN_VALUE(c, position, value OUT [, column_error OUT [, actual_length OUT ]]) Stored procedure N/A Returns a column value into a variable.
COLUMN_VALUE_CHAR(c, position, value OUT [, column_error OUT [, actual_length OUT ]]) Stored procedure N/A Returns a CHAR column value into a variable.
COLUMN_VALUE_RAW(c, position, value OUT [, column_error OUT [, actual_length OUT ]]) Stored procedure N/A Returns a RAW column value into a variable.
DEFINE_COLUMN(c, position, column [, column_size ]) Stored procedure N/A Defines a column in the SELECT list.
DEFINE_COLUMN_CHAR(c, position, column, column_size) Stored procedure N/A Defines a CHAR column in the SELECT list.
DEFINE_COLUMN_RAW(c, position, column, column_size) Stored procedure N/A Defines a RAW column in the SELECT list.
DESCRIBE_COLUMNS Stored procedure N/A Defines columns to hold a cursor result set.
EXECUTE(c) Function INTEGER Executes a cursor.
EXECUTE_AND_FETCH(c [, exact ]) Function INTEGER Executes a cursor and fetches a single row.
FETCH_ROWS(c) Function INTEGER Fetches rows from the cursor.
IS_OPEN(c) Function BOOLEAN Check whether a cursor is open.
LAST_ROW_COUNT Function INTEGER Returns the cumulative number of rows fetched.
OPEN_CURSOR Function INTEGER Opens a cursor.
PARSE(c, statement, language_flag) Stored procedure N/A Parses a statement.

The DBMS_SQL package in PolarDB-O is partially implemented when compared to Oracle's version. PolarDB-O 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 variable Data type Value Description
native INTEGER 1 Provided for compatibility with Oracle syntax. For more information, see DBMS_SQL.PARSE.
V6 INTEGER 2 Provided for compatibility with Oracle syntax. For more information, see DBMS_SQL.PARSE.
V7 INTEGER 3 Provided for compatibility with Oracle syntax. For more information, see DBMS_SQL.PARSE.

BIND_VARIABLE

The BIND_VARIABLE stored procedure provides the capability to associate a value with an IN or IN OUT bind variable in an SQL command.

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

Parameters

Parameter Description
c The ID of the cursor for the SQL command with bind variables.
name The name of the bind variable in the SQL command.
value The value to be assigned.
out_value_size If 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;

Number of rows processed: 1

BIND_VARIABLE_CHAR

The BIND_VARIABLE_CHAR stored procedure provides the capability to associate a CHAR value with an IN or IN OUT bind variable in an SQL command.

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

Parameters

Parameter Description
c The ID of the cursor for the SQL command with bind variables.
name The name of the bind variable in the SQL command.
value The value of type CHAR to be assigned.
out_value_size If 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 provides the capability to associate a RAW value with an IN or IN OUT bind variable in an SQL command.

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

Parameters

Parameter Description
c The ID of the cursor for the SQL command with bind variables.
name The name of the bind variable in the SQL command.
value The value of type RAW to be assigned.
out_value_size If 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 closes a cursor. When the cursor is closed, resources allocated to the cursor are released and the cursor can no longer be used.

CLOSE_CURSOR(c IN OUT INTEGER)
            

Parameters

Parameter Description
c The ID of the cursor to be closed.

Examples

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

COLUMN_VALUE

The COLUMN_VALUE stored procedure defines 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

Parameter Description
c The ID of the cursor that returns data to the variable being defined.
position The position of the returned data within the cursor. The first value in the cursor is position 1.
value The variable that receives the data returned in the cursor by a prior fetch call.
column_error If an error occurs, this parameter indicates the error code associated with the column.
actual_length The 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 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 ]])

Parameters

Parameter Description
c The ID of the cursor that returns data to the variable being defined.
position The position of the returned data within the cursor. The first value in the cursor is position 1.
value The variable of data type CHAR that receives the data returned in the cursor by a prior fetch call.
column_error If an error occurs, this parameter indicates the error code associated with the column.
actual_length The actual length of the data before truncation.

COLUMN_VALUE_RAW

The COLUMN_VALUE_RAW stored 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 ]])

Parameters

Parameter Description
c The ID of the cursor that returns data to the variable being defined.
position The position of the returned data within the cursor. The first value in the cursor is position 1.
value The variable of data type RAW that receives the data returned in the cursor by a prior fetch call.
column_error If an error occurs, this parameter indicates the error code associated with the column.
actual_length The actual length of the data before truncation.

DEFINE_COLUMN

The DEFINE_COLUMN stored 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 |
  CLOB | DATE | FLOAT | INTEGER | NUMBER | TIMESTAMP | VARCHAR2 }
  [, column_size INTEGER ])

Parameters

Parameter Description
c The ID of the cursor associated with the SELECT command.
position The position of the column or expression in the SELECT list that is being defined.
column A variable that matches the data type of the column or expression in the specified position of the SELECT result set.
column_size The 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), respectively, even though v_num is defined as NUMBER(1). In the preceding example, each of the declarations in the COLUMN_VALUE stored procedure are configured with 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. 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 COLUMN_VALUE 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 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)

Parameters

Parameter Description
c The ID of the cursor associated with the SELECT command.
position The position of the column or expression in the SELECT list that is being defined.
column A CHAR variable.
column_size The 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 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)

Parameters

Parameter Description
c The ID of the cursor associated with the SELECT command.
position The position of the column or expression in the SELECT list that is being defined.
column A RAW variable.
column_size The maximum length of the returned data. Returned data exceeding column_size is truncated to column_size characters.

DESCRIBE_COLUMNS

The DESCRIBE_COLUMNS stored procedure describes the columns returned by a cursor.

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

Parameters

Parameter Description
c The ID of the cursor.
col_cnt The number of columns in the cursor result set.
desc_tab The table that contains a description of each column returned by the cursor. The descriptions are of type DESC_REC, and contain the following values:
Column name Type
col_type INTEGER
col_max_len INTEGER
col_name VARCHAR2(128)
col_name_len INTEGER
col_schema_name VARCHAR2(128)
col_schema_name_len INTEGER
col_precision INTEGER
col_scale INTEGER
col_charsetid INTEGER
col_charsetform INTEGER
col_null_ok BOOLEAN

EXECUTE

The EXECUTE function runs a parsed SQL command or SPL block.

status INTEGER EXECUTE(c INTEGER)
            

Parameters

Parameter Description
c The cursor ID of the parsed SQL statement or SPL block to be run.
status If the SQL command is DELETE, INSERT, or UPDATE, this parameter indicates the number of records processed. This parameter is meaningless for other commands.

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 runs a parsed SELECT command and fetches one row.

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

Parameters

Parameter Description
c The ID of the cursor for the SELECT command to be run.
exact If this parameter is set to TRUE, an exception occurs if the number of rows in the result set is not equal to 1. If this parameter is set to FALSE, no exception occurs. The default value of this parameter is FALSE. 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 for this parameter. If no rows are fetched, 0 is returned for this parameter. If an exception occurs, no value is returned.

Examples

The following stored procedure uses the EXECUTE_AND_FETCH function to retrieve 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;

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 retrieves a row from a cursor.

status INTEGER FETCH_ROWS(c INTEGER)
            

Parameters

Parameter Description
c The ID of the cursor used to fetch a row.
status If a row is fetched, 1 is returned for this parameter. If no rows are fetched, 0 is returned for this parameter.

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;

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 provides the capability to checks whether the specified cursor is open.

status BOOLEAN IS_OPEN(c INTEGER)
            

Parameters

Parameter Description
c The ID of the cursor to be checked.
status If the cursor is open, this parameter is set to TRUE. If the cursor is not open, this parameter is set to FALSE.

LAST_ROW_COUNT

The LAST_ROW_COUNT function returns the total number of rows that are fetched.

rowcnt INTEGER LAST_ROW_COUNT
            

Parameters

Parameter Description
rowcnt The 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;

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 creates a new cursor. A cursor must be used to parse and execute a dynamic SQL statements. After being opened, a curser can be re-used with the same or different SQL statements without the need for you to close and re-open the cursor.

c INTEGER OPEN_CURSOR
            

Parameters

Parameter Description
c The 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 parses an SQL command or SPL block. If the SQL command is a DDL command, it is immediately run and does not require calling the EXECUTE function.

PARSE(c INTEGER, statement VARCHAR2, language_flag INTEGER)
            

Parameters

Parameter Description
c The ID of an open cursor.
statement The SQL command or SPL block to be parsed. An SQL command cannot end with a semicolon (;). An SPL block must end with a semicolon (;).
language_flag The 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 PolarDB-O form.

Examples

The following anonymous block creates a table named job. Note that DDL statements are immediately run 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;

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 preceding example, 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;