PolarDB:DBMS_SQL

Supported functions and stored procedures

Public variables

The following public variables are available for use with DBMS_SQL.PARSE to maintain Oracle syntax compatibility.

The language_flag parameter in PARSE accepts these values but ignores them — all syntax is processed in PolarDB for PostgreSQL (Compatible with Oracle) form regardless of the flag passed.

Cursor lifecycle

All DBMS_SQL operations follow this cursor lifecycle:

1. Open a cursor with OPEN_CURSOR.

2. Parse the SQL statement with PARSE.

3. Bind values to bind variables with BIND_VARIABLE (for DML statements).

4. Define output columns with DEFINE_COLUMN (for SELECT statements).

5. Execute the cursor with EXECUTE.

6. Fetch rows with FETCH_ROWS or EXECUTE_AND_FETCH (for SELECT statements).

7. Read column values with COLUMN_VALUE (for SELECT statements).

8. Close the cursor with CLOSE_CURSOR.

BIND_VARIABLE

Binds 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

Example

The following anonymous block inserts a row into the emp table using bind variables.

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;

Output:

Number of rows processed: 1

BIND_VARIABLE_CHAR

Binds 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

BIND_VARIABLE_RAW

Binds 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

CLOSE_CURSOR

Closes a cursor and releases all resources allocated to it. After closing, the cursor can no longer be used.

CLOSE_CURSOR(c IN OUT INTEGER)

Parameters

Example

DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    -- ... parse, execute, fetch ...
    DBMS_SQL.CLOSE_CURSOR(curid);
END;

COLUMN_VALUE

Reads a column value from a cursor into a variable after calling FETCH_ROWS.

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

Example

The following anonymous block fetches rows from the emp table and reads each column value.

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);
    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, 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

Reads a CHAR column value from a cursor into a variable after calling FETCH_ROWS.

COLUMN_VALUE_CHAR(c INTEGER, position INTEGER, value OUT CHAR
  [, column_error OUT NUMBER [, actual_length OUT INTEGER ]])

Parameters

COLUMN_VALUE_RAW

Reads a RAW column value from a cursor into a variable after calling FETCH_ROWS.

COLUMN_VALUE_RAW(c INTEGER, position INTEGER, value OUT RAW
  [, column_error OUT NUMBER [, actual_length OUT INTEGER ]])

Parameters

DEFINE_COLUMN

Defines a column or expression in the SELECT list so that FETCH_ROWS can return data into it.

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

Parameters

The data type precision of the column variable does not affect what data is returned. For example, a NUMBER(1) variable defined for a NUMBER(7,2) column still returns the full NUMBER(7,2) value. For VARCHAR2 columns, the size is controlled by column_size, not the variable declaration.

Example

The following anonymous block defines output columns for a SELECT query on 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);
    -- ... execute and fetch ...
END;

DEFINE_COLUMN_CHAR

Defines a CHAR column or expression in the SELECT list so that FETCH_ROWS can return data into it.

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

Parameters

DEFINE_COLUMN_RAW

Defines a RAW column or expression in the SELECT list so that FETCH_ROWS can return data into it.

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

Parameters

DEFINE_ARRAY

Defines the columns into which FETCH_ROWS should collect rows as arrays.

DEFINE_ARRAY(
   c             IN INTEGER,
   position      IN INTEGER,
   table_variable IN <datatype>,
   cnt           IN INTEGER,
   lower_bnd     IN INTEGER)

Parameters

Supported collection types for `table_variable`

Example

The following anonymous block creates a table t, fetches two rows, and prints them.

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;

Output:

fetch rows is 2
1
2

DESCRIBE_COLUMNS

Describes the columns returned by a cursor, populating a table of column metadata records.

DESCRIBE_COLUMNS(c INTEGER, col_cnt OUT INTEGER, desc_tab OUT DESC_TAB)

Parameters

Fields in each `DESC_REC` record

EXECUTE

Executes a parsed SQL statement or SPL (Stored Procedure Language) block.

status INTEGER EXECUTE(c INTEGER)

Parameters

Example

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

Executes a parsed SELECT statement and fetches exactly one row.

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

Parameters

Behavior of `exact`

Example

The following stored procedure looks up an employee by name. If the employee is not found or multiple employees share the same name, an exception is raised and handled.

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('SQLERRM: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
        DBMS_SQL.CLOSE_CURSOR(curid);
END;

Call the procedure and view results:

EXEC select_by_name('MARTIN')

Output:

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

FETCH_ROWS

Fetches the next row from a cursor. Call COLUMN_VALUE after each successful fetch to read column data.

status INTEGER FETCH_ROWS(c INTEGER)

Parameters

Example

The following anonymous block fetches all rows from the emp table and prints them.

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

Output:

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

Checks whether a cursor is currently open.

status BOOLEAN IS_OPEN(c INTEGER)

Parameters

LAST_ROW_COUNT

Returns the cumulative number of rows fetched so far by the cursor.

rowcnt INTEGER LAST_ROW_COUNT

Parameters

Example

The following anonymous block fetches all rows from the emp table and prints the total count after the loop.

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

Output:

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

Opens a new cursor and returns its cursor ID. A cursor must be opened before you can parse and execute a dynamic SQL statement.

c INTEGER OPEN_CURSOR

Parameters

An open cursor can be reused with different SQL statements without closing and reopening it.

Example

DECLARE
    curid           INTEGER;
BEGIN
    curid := DBMS_SQL.OPEN_CURSOR;
    -- ... parse, execute, fetch ...
END;

PARSE

Parses an SQL statement or SPL block and associates it with the cursor. DDL statements execute immediately on parse — no separate EXECUTE call is needed.

PARSE(c INTEGER, statement VARCHAR2, language_flag INTEGER)

Parameters

Example: DDL statement

DDL statements execute immediately on parse. The following anonymous block creates a table named job.

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;

Example: DML statements

The following anonymous block inserts two rows into the job table. The cursor is reused for the second INSERT without closing and reopening.

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;

Output:

Number of rows processed: 1
Number of rows processed: 1

Example: SPL block

The following anonymous block uses DBMS_SQL to execute an SPL block containing two INSERT statements. The SPL block ends with a semicolon (;) after END.

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;