Reference for the built-in dynamic SQL package - PolarDB:DBMS_SQL

The DBMS_SQL package lets you build and execute SQL statements dynamically at runtime — useful when the full statement isn't known until the application runs.

Subprograms

All subprograms follow a cursor-based workflow: open a cursor, parse the statement, bind variables, define output columns, execute, fetch results, and close the cursor. The table below lists every subprogram along with its type, return type, and a brief description.

Subprogram	Type	Return type	Description
BIND_ARRAY	Procedure	—	Binds an array value to a placeholder variable.
BIND_VARIABLE	Procedure	—	Binds a scalar value to a placeholder variable.
BIND_VARIABLE_CHAR	Procedure	—	Binds a CHAR value to a placeholder variable.
BIND_VARIABLE_RAW	Procedure	—	Binds a RAW value to a placeholder variable.
CLOSE_CURSOR	Procedure	—	Closes a cursor and releases its memory.
COLUMN_VALUE	Procedure	—	Retrieves the value of a column at a given position in a cursor.
COLUMN_VALUE_CHAR	Procedure	—	Retrieves the CHAR column value at a given position in a cursor.
COLUMN_VALUE_LONG	Procedure	—	Retrieves the LONG column value at a given position in a cursor.
COLUMN_VALUE_RAW	Procedure	—	Retrieves the RAW column value at a given position in a cursor.
DEFINE_ARRAY	Procedure	—	Defines an array column to fetch from a cursor.
DEFINE_COLUMN	Procedure	—	Defines a column to fetch from a cursor.
DEFINE_COLUMN_CHAR	Procedure	—	Defines a CHAR column to fetch from a cursor.
DEFINE_COLUMN_LONG	Procedure	—	Defines a LONG column to fetch from a cursor.
DEFINE_COLUMN_RAW	Procedure	—	Defines a RAW column to fetch from a cursor.
EXECUTE	Function	INTEGER	Executes a parsed cursor.
EXECUTE_AND_FETCH	Function	INTEGER	Executes a cursor and fetches the first row.
FETCH_ROWS	Function	INTEGER	Fetches the next row from an executed cursor.
IS_OPEN	Function	BOOLEAN	Checks whether a cursor is open.
LAST_ROW_COUNT	Function	INTEGER	Returns the cumulative number of rows fetched so far.
OPEN_CURSOR	Function	INTEGER	Opens a new cursor and returns its ID.
PARSE	Procedure	—	Parses a SQL statement and associates it with a cursor.
VARIABLE_VALUE	Procedure	—	Retrieves the value of a named bind variable from a cursor.
VARIABLE_VALUE_CHAR	Procedure	—	Retrieves the CHAR value of a named bind variable from a cursor.
VARIABLE_VALUE_RAW	Procedure	—	Retrieves the RAW value of a named bind variable from a cursor.

Data types

The package defines two array types for use with the PARSE procedure overloads that accept chunked statements.

VARCHAR2A

TYPE varchar2a IS TABLE OF varchar2(32767);

VARCHAR2S

TYPE varchar2s IS TABLE OF varchar2(256);

Execution flow

DBMS_SQL follows a fixed sequence of steps. The exact path depends on whether you are running a query or a DML statement.

Queries (SELECT)

Call OPEN_CURSOR to get a cursor ID.
Call PARSE to associate a SQL statement with the cursor.
Call BIND_VARIABLE (or BIND_ARRAY) for each placeholder in the statement.
Call DEFINE_COLUMN (or a type-specific variant) for each column in the SELECT list.
Call EXECUTE to run the statement.
Call FETCH_ROWS in a loop (or call EXECUTE_AND_FETCH to combine steps 5 and 6) to retrieve rows.
Call COLUMN_VALUE inside the loop to read each column from the fetched row.
Call CLOSE_CURSOR when done.

DML statements (INSERT, UPDATE, DELETE)

Call OPEN_CURSOR to get a cursor ID.
Call PARSE to associate a SQL statement with the cursor.
Call BIND_VARIABLE (or BIND_ARRAY) for each placeholder in the statement.
Call EXECUTE to run the statement. The return value is the number of rows affected.
If the statement uses a RETURNING clause, call VARIABLE_VALUE to retrieve the returned values.
Call CLOSE_CURSOR when done.

Important

Always call CLOSE_CURSOR in an exception handler as well. If the block exits with an error before reaching CLOSE_CURSOR, the cursor leaks until the session ends.

BIND_ARRAY

Binds an array to a placeholder variable, letting a single EXECUTE call insert or process multiple rows.

Syntax

DBMS_SQL.BIND_ARRAY (
   c        IN INTEGER,
   name     IN VARCHAR2,
   value    IN ANYARRAY
  [,index1  IN INTEGER,
   index2   IN INTEGER]);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
name	IN	Placeholder name in the SQL statement.
value	IN	Local array variable to bind.
index1	IN	(Optional) Lower index of the array range. Default: -1 (use the full array).
index2	IN	(Optional) Upper index of the array range. Default: -1 (use the full array).

Example

The following example inserts three rows in a single EXECUTE call by binding arrays to each placeholder.

CREATE TABLE test(a int, b varchar2, c numeric);

DECLARE
  c int;
  a int[];
  b varchar[];
  ca numeric[];
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c, 'insert into test values(:a, :b, :c)', DBMS_SQL.NATIVE);
  a := ARRAY[1, 2, 3];
  b := ARRAY['Alice', 'Bob', 'Cindy'];
  ca := ARRAY[5, 4, 3];

  DBMS_SQL.BIND_ARRAY(c, 'a', a);
  DBMS_SQL.BIND_ARRAY(c, 'b', b);
  DBMS_SQL.BIND_ARRAY(c, 'c', ca);
-- inserted rows: 3
  DBMS_OUTPUT.PUT_LINE('inserted rows: ' || DBMS_SQL.EXECUTE(c));
  DBMS_SQL.CLOSE_CURSOR(c);
END;

SELECT * FROM test ORDER BY 1, 2, 3;
 a |   b   | c
---+-------+---
 1 | Alice | 5
 2 | Bob   | 4
 3 | Cindy | 3
(3 rows)

BIND_VARIABLE

Binds a scalar value to a placeholder variable.

Syntax

DBMS_SQL.BIND_VARIABLE (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN VARCHAR2,
   out_value_size IN INTEGER DEFAULT -1);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
name	IN	Placeholder name in the SQL statement.
value	IN	Local variable to bind.
out_value_size	IN	(Optional) Maximum byte size expected for an OUT variable. Default: -1.

Example

The following example updates a row and retrieves the updated value through a RETURNING clause.

CREATE TABLE test(id number, c VARCHAR2(30));
INSERT INTO test VALUES (1, 'bind_variable for varchar2');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  c_var  VARCHAR2(25);
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c,
      'UPDATE test SET c = :n WHERE id = :id RETURNING c INTO :n',
      DBMS_SQL.NATIVE);

  id_var := 1;
  c_var := 'bind_variable_varchar2';

  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.BIND_VARIABLE(c, 'n', c_var, 25);

  ignore := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.VARIABLE_VALUE(c, 'n', c_var);

  -- VARCHAR returned is:bind_variable_varchar2
  DBMS_OUTPUT.PUT_LINE('VARCHAR returned is:' || c_var);
  DBMS_SQL.CLOSE_CURSOR(c);
END;

BIND_VARIABLE_CHAR

Binds a CHAR value to a placeholder variable.

Syntax

DBMS_SQL.BIND_VARIABLE_CHAR (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN CHAR,
   out_value_size IN INTEGER DEFAULT -1);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
name	IN	Placeholder name in the SQL statement.
value	IN	Local CHAR variable to bind.
out_value_size	IN	(Optional) Maximum byte size expected for an OUT variable. Default: -1.

Example

CREATE TABLE test(id number, c VARCHAR2(30));
INSERT INTO test VALUES (1, 'bind_variable for char');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  c_var  CHAR(25);
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c,
      'UPDATE test SET c = :n WHERE id = :id RETURNING c INTO :n',
      DBMS_SQL.NATIVE);

  id_var := 1;
  c_var := 'bind_variable_char';

  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.BIND_VARIABLE_CHAR(c, 'n', c_var, 25);

  ignore := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.VARIABLE_VALUE(c, 'n', c_var);

  -- CHAR returned is:bind_variable_char
  DBMS_OUTPUT.PUT_LINE('CHAR returned is:' || c_var);
  DBMS_SQL.CLOSE_CURSOR(c);
END;

BIND_VARIABLE_RAW

Binds a RAW value to a placeholder variable.

Syntax

DBMS_SQL.BIND_VARIABLE_RAW (
   c              IN INTEGER,
   name           IN VARCHAR2,
   value          IN RAW,
   out_value_size IN INTEGER DEFAULT -1);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
name	IN	Placeholder name in the SQL statement.
value	IN	Local RAW variable to bind.
out_value_size	IN	(Optional) Maximum byte size expected for an OUT variable. Default: -1.

Example

CREATE TABLE test(id number, c RAW(20));
INSERT INTO test VALUES (1, 'aaabbbccc');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  r_var  RAW(20);
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c,
      'UPDATE test SET c = :n WHERE id = :id RETURNING c INTO :n',
      DBMS_SQL.NATIVE);
  id_var := 1;
  r_var := 'aaaabbbbcccc';
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.BIND_VARIABLE_RAW(c, 'n', r_var, utl_raw.length(r_var));
  ignore := DBMS_SQL.EXECUTE(c);

  DBMS_SQL.CLOSE_CURSOR(c);
END;

SELECT * from test ORDER BY 1, 2;
 id |             c
----+----------------------------
  1 | \x616161616262626263636363
(1 row)

CLOSE_CURSOR

Closes a cursor and releases the memory it holds. After calling CLOSE_CURSOR, the cursor ID is no longer valid.

Syntax

DBMS_SQL.CLOSE_CURSOR (
   c  INOUT INTEGER);

Parameters

Parameter	Mode	Description
c	INOUT	Cursor ID.

Example

The following example shows the recommended pattern: close the cursor both in the normal path and in the exception handler to avoid cursor leaks.

DECLARE
  c NUMBER;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  -- ... parse, bind, execute ...
  DBMS_SQL.CLOSE_CURSOR(c);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_SQL.CLOSE_CURSOR(c);
    RAISE;
END;

COLUMN_VALUE

Retrieves the value of a column at a specific position from the current row in a cursor. Call this after each FETCH_ROWS call.

Syntax

DBMS_SQL.COLUMN_VALUE (
   c              IN    INTEGER,
   pos            IN    INTEGER,
   value          INOUT ANYELEMENT
  [,column_error  INOUT NUMBER]
  [,actual_length INOUT INTEGER]);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
pos	IN	1-based column position in the SELECT list.
value	INOUT	Variable to receive the column value.
column_error	INOUT	(Optional) Provided for Oracle compatibility.
actual_length	INOUT	(Optional) Provided for Oracle compatibility.

Example

DECLARE
  c         INTEGER;
  processd  INTEGER;
  strval    VARCHAR2(100);
  intval    INTEGER;
  nrows     INTEGER DEFAULT 5;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c, 'select ''num'' || i, i from generate_series(1, :nrows) g(i)',
                 DBMS_SQL.NATIVE);
  dbms_sql.BIND_VARIABLE(c, 'nrows', nrows);
  dbms_sql.DEFINE_COLUMN(c, 1, strval);
  dbms_sql.DEFINE_COLUMN(c, 2, intval);
  processd := DBMS_SQL.EXECUTE(c);
  WHILE DBMS_SQL.FETCH_ROWS(c) > 0
  LOOP
    DBMS_SQL.COLUMN_VALUE(c, 1, strval);
    DBMS_SQL.COLUMN_VALUE(c, 2, intval);
    DBMS_OUTPUT.PUT_LINE('c1: ' || strval || ', c2: ' || intval);
    DBMS_OUTPUT.PUT_LINE('last count is: ' || DBMS_SQL.LAST_ROW_COUNT());
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c);
END;

-- c1: num1, c2: 1
-- last count is: 1
-- c1: num2, c2: 2
-- last count is: 2
-- c1: num3, c2: 3
-- last count is: 3
-- c1: num4, c2: 4
-- last count is: 4
-- c1: num5, c2: 5
-- last count is: 5

COLUMN_VALUE_CHAR

Retrieves the CHAR value of a column at a specific position from the current row in a cursor.

Syntax

DBMS_SQL.COLUMN_VALUE_CHAR (
   c              IN  INTEGER,
   pos            IN  INTEGER,
   value          INOUT CHAR
  [,column_error  INOUT NUMBER]
  [,actual_length INOUT INTEGER]);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
pos	IN	1-based column position in the SELECT list.
value	INOUT	CHAR variable to receive the column value.
column_error	INOUT	(Optional) Provided for Oracle compatibility.
actual_length	INOUT	(Optional) Provided for Oracle compatibility.

Example

CREATE TABLE test(id number, c CHAR(20));
INSERT INTO test VALUES (1, 'define_column_char');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  c_var  CHAR(30);
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c,
      'SELECT c FROM test WHERE id = :id',
      DBMS_SQL.NATIVE);
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_CHAR(c, 1, c_var, 20);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  DBMS_SQL.COLUMN_VALUE_CHAR(c, 1, c_var);
  -- COLUMN c is: define_column_char
  DBMS_OUTPUT.PUT_LINE('COLUMN c is: ' || c_var);

  DBMS_SQL.CLOSE_CURSOR(c);
END;

COLUMN_VALUE_LONG

Retrieves a slice of a LONG column value at a specific position from the current row in a cursor. Use this in a loop to read large LONG values in chunks.

Syntax

DBMS_SQL.COLUMN_VALUE_LONG (
   c             IN  INTEGER,
   pos           IN  INTEGER,
   length        IN  INTEGER,
   off           IN  INTEGER,
   value         INOUT VARCHAR2,
   value_length  INOUT INTEGER);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
pos	IN	1-based column position in the SELECT list.
length	IN	Number of bytes to read from the LONG value.
off	IN	Byte offset to start reading from.
value	INOUT	VARCHAR2 variable to receive the chunk.
value_length	INOUT	Actual number of bytes returned. Returns 0 when there is no more data.

Example

The following example reads a LONG column in 8-byte chunks.

CREATE TABLE test(id NUMBER, doc LONG);
INSERT INTO test VALUES (1, 'This is test for dbms_sql.define_column_long and dbms_sql.column_value_long');

DECLARE
  c       NUMBER;
  ignore  NUMBER;
  id_var  NUMBER;
  doc_var VARCHAR2(10);
  pos     INTEGER := 0;
  str_len INTEGER := 8;
  returned_len INTEGER;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c,
      'SELECT doc FROM test WHERE id = :id',
      DBMS_SQL.NATIVE);
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_LONG(c, 1);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  LOOP
      DBMS_SQL.COLUMN_VALUE_LONG(c, 1, str_len, pos, doc_var, returned_len);
      EXIT WHEN returned_len = 0;
      DBMS_OUTPUT.PUT_LINE('doc is: ' || doc_var);
      pos := pos + returned_len;
  END LOOP;
END;

-- doc is: This is
-- doc is: test for
-- doc is:  dbms_sq
-- doc is: l.define
-- doc is: _column_
-- doc is: long and
-- doc is:  dbms_sq
-- doc is: l.column
-- doc is: _value_l
-- doc is: ong

COLUMN_VALUE_RAW

Retrieves the RAW value of a column at a specific position from the current row in a cursor.

Syntax

DBMS_SQL.COLUMN_VALUE_RAW (
   c              IN  INTEGER,
   pos            IN  INTEGER,
   value          INOUT RAW
  [,column_error  INOUT NUMBER]
  [,actual_length INOUT INTEGER]);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
pos	IN	1-based column position in the SELECT list.
value	INOUT	RAW variable to receive the column value.
column_error	INOUT	(Optional) Provided for Oracle compatibility.
actual_length	INOUT	(Optional) Provided for Oracle compatibility.

Example

CREATE TABLE test(id number, c RAW(20));
INSERT INTO test VALUES (1, 'aaabbbccc');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  r_var  RAW(10);
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c,
      'SELECT c FROM test WHERE id = :id',
      DBMS_SQL.NATIVE);
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_RAW(c, 1, r_var, 10);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  DBMS_SQL.COLUMN_VALUE_RAW(c, 1, r_var);
  -- COLUMN c is: \x616161626262636363
  DBMS_OUTPUT.PUT_LINE('COLUMN c is: ' || r_var);

  DBMS_SQL.CLOSE_CURSOR(c);
END;

DEFINE_ARRAY

Defines an array output column to fetch from a cursor. Use this to retrieve multiple rows into an array variable in a single FETCH_ROWS call.

Syntax

DBMS_SQL.DEFINE_ARRAY (
   c         IN INTEGER,
   pos       IN INTEGER,
   value     IN ANYARRAY,
   cnt       IN INTEGER,
   lower_bnd IN INTEGER);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
pos	IN	1-based column position in the SELECT list.
value	IN	Local array variable that determines the element type.
cnt	IN	The number of rows that you want to fetch.
lower_bnd	IN	Starting index for the output array.

Example

DECLARE
  cur      INTEGER;
  processd INTEGER;
  a        INTEGER[];
  b        VARCHAR2[];
  c        NUMBER[];
BEGIN
  cur := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(cur, 'select i, ''num'' || i, i + 0.003 from generate_series(1, 5) g(i)',
                 DBMS_SQL.NATIVE);
  DBMS_SQL.DEFINE_ARRAY(cur, 1, a, 10, 1);
  DBMS_SQL.DEFINE_ARRAY(cur, 2, b, 13, 1);
  DBMS_SQL.DEFINE_ARRAY(cur, 3, c, 6, 1);

  processd := DBMS_SQL.EXECUTE(cur);
  LOOP
    processd := DBMS_SQL.FETCH_ROWS(cur);
    DBMS_SQL.COLUMN_VALUE(cur, 1, a);
    DBMS_SQL.COLUMN_VALUE(cur, 2, b);
    DBMS_SQL.COLUMN_VALUE(cur, 3, c);
    EXIT WHEN processd != 6;
  END LOOP;
  RAISE NOTICE 'a is: %', a;
  RAISE NOTICE 'b is: %', b;
  RAISE NOTICE 'c is: %', c;
  DBMS_SQL.CLOSE_CURSOR(cur);
END;

-- NOTICE:  a = {1,2,3,4,5}
-- NOTICE:  b = {Ahoj1,Ahoj2,Ahoj3,Ahoj4,Ahoj5}
-- NOTICE:  c = {1.003,2.003,3.003,4.003,5.003}

DEFINE_COLUMN

Defines an output column to fetch from a cursor. Call this once per SELECT column before calling EXECUTE.

Syntax

DBMS_SQL.DEFINE_COLUMN (
   c           IN INTEGER,
   pos         IN INTEGER,
   col         IN ANYELEMENT,
   column_size IN INTEGER DEFAULT -1);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
pos	IN	1-based column position in the SELECT list.
col	IN	Local variable that determines the column's data type.
column_size	IN	(Optional) Maximum byte size for the column value. Default: -1.

Example

DECLARE
  c         INTEGER;
  processd  INTEGER;
  strval    VARCHAR2(100);
  intval    INTEGER;
  nrows     INTEGER DEFAULT 5;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c, 'select ''num'' || i, i from generate_series(1, :nrows) g(i)',
                 DBMS_SQL.NATIVE);
  dbms_sql.BIND_VARIABLE(c, 'nrows', nrows);
  dbms_sql.DEFINE_COLUMN(c, 1, strval);
  dbms_sql.DEFINE_COLUMN(c, 2, intval);
  processd := DBMS_SQL.EXECUTE(c);
  WHILE DBMS_SQL.FETCH_ROWS(c) > 0
  LOOP
    DBMS_SQL.COLUMN_VALUE(c, 1, strval);
    DBMS_SQL.COLUMN_VALUE(c, 2, intval);
    DBMS_OUTPUT.PUT_LINE('c1: ' || strval || ', c2: ' || intval);
    DBMS_OUTPUT.PUT_LINE('last count is: ' || DBMS_SQL.LAST_ROW_COUNT());
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c);
END;

-- c1: num1, c2: 1
-- last count is: 1
-- c1: num2, c2: 2
-- last count is: 2
-- c1: num3, c2: 3
-- last count is: 3
-- c1: num4, c2: 4
-- last count is: 4
-- c1: num5, c2: 5
-- last count is: 5

DEFINE_COLUMN_CHAR

Defines a CHAR output column to fetch from a cursor.

Syntax

DBMS_SQL.DEFINE_COLUMN_CHAR (
   c           IN INTEGER,
   pos         IN INTEGER,
   col         IN CHAR,
   column_size IN INTEGER DEFAULT -1);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
pos	IN	1-based column position in the SELECT list.
col	IN	Local CHAR variable that determines the column type.
column_size	IN	(Optional) Maximum byte size for the column value. Default: -1.

Example

CREATE TABLE test(id number, c CHAR(20));
INSERT INTO test VALUES (1, 'define_column_char');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  c_var  CHAR(30);
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c,
      'SELECT c FROM test WHERE id = :id',
      DBMS_SQL.NATIVE);
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_CHAR(c, 1, c_var, 20);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  DBMS_SQL.COLUMN_VALUE_CHAR(c, 1, c_var);
  -- COLUMN c is: define_column_char
  DBMS_OUTPUT.PUT_LINE('COLUMN c is: ' || c_var);

  DBMS_SQL.CLOSE_CURSOR(c);
END;

DEFINE_COLUMN_LONG

Defines a LONG output column to fetch from a cursor. Read the value in chunks using COLUMN_VALUE_LONG.

Syntax

DBMS_SQL.DEFINE_COLUMN_LONG (
   c   IN INTEGER,
   pos IN INTEGER);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
pos	IN	1-based column position in the SELECT list.

Example

CREATE TABLE test(id NUMBER, doc LONG);
INSERT INTO test VALUES (1, 'This is test for dbms_sql.define_column_long and dbms_sql.column_value_long');

DECLARE
  c       NUMBER;
  ignore  NUMBER;
  id_var  NUMBER;
  doc_var VARCHAR2(10);
  pos     INTEGER := 0;
  str_len INTEGER := 8;
  returned_len INTEGER;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c,
      'SELECT doc FROM test WHERE id = :id',
      DBMS_SQL.NATIVE);
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_LONG(c, 1);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  LOOP
      DBMS_SQL.COLUMN_VALUE_LONG(c, 1, str_len, pos, doc_var, returned_len);
      EXIT WHEN returned_len = 0;
      DBMS_OUTPUT.PUT_LINE('doc is: ' || doc_var);
      pos := pos + returned_len;
  END LOOP;
END;

-- doc is: This is
-- doc is: test for
-- doc is:  dbms_sq
-- doc is: l.define
-- doc is: _column_
-- doc is: long and
-- doc is:  dbms_sq
-- doc is: l.column
-- doc is: _value_l
-- doc is: ong

DEFINE_COLUMN_RAW

Defines a RAW output column to fetch from a cursor.

Syntax

DBMS_SQL.DEFINE_COLUMN_RAW (
   c           IN INTEGER,
   pos         IN INTEGER,
   col         IN RAW,
   column_size IN INTEGER DEFAULT -1);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
pos	IN	1-based column position in the SELECT list.
col	IN	Local RAW variable that determines the column type.
column_size	IN	(Optional) Maximum byte size for the column value. Default: -1.

Example

CREATE TABLE test(id number, c RAW(20));
INSERT INTO test VALUES (1, 'aaabbbccc');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  r_var  RAW(10);
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c,
      'SELECT c FROM test WHERE id = :id',
      DBMS_SQL.NATIVE);
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_RAW(c, 1, r_var, 10);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  DBMS_SQL.COLUMN_VALUE_RAW(c, 1, r_var);
  -- COLUMN c is: \x616161626262636363
  DBMS_OUTPUT.PUT_LINE('COLUMN c is: ' || r_var);

  DBMS_SQL.CLOSE_CURSOR(c);
END;

EXECUTE

Executes a parsed cursor. For DML statements, the return value is the number of rows affected.

Syntax

DBMS_SQL.EXECUTE (c IN INTEGER)
  RETURN INTEGER;

Parameters

Parameter	Mode	Description
c	IN	Cursor ID of the statement to execute.

Return values

Return value	Description
INTEGER	For DELETE, INSERT, or UPDATE statements: the number of rows processed.

Example

CREATE TABLE test(id number, c CHAR(20));

DECLARE
  c       NUMBER;
  ret     NUMBER;
  id_var  NUMBER;
  c_var   CHAR(20);
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c,
     'INSERT INTO test VALUES (:id, :n) RETURNING c INTO :n',
     DBMS_SQL.NATIVE);
  id_var := 3;
  c_var := 'bind_variable_char';
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.BIND_VARIABLE_CHAR(c, 'n', c_var, 18);
  ret := DBMS_SQL.EXECUTE(c);
  -- RESULT is: 1
  DBMS_OUTPUT.PUT_LINE('RESULT is: ' || ret);
  DBMS_SQL.CLOSE_CURSOR(c);
END;

SELECT * from test ORDER BY 1, 2;
 id |          c
----+----------------------
  3 | bind_variable_char
(1 row)

EXECUTE_AND_FETCH

Executes a cursor and fetches the first row in a single call. Useful for queries expected to return exactly one row.

Syntax

DBMS_SQL.EXECUTE_AND_FETCH (
   c     IN INTEGER,
   exact IN BOOLEAN DEFAULT FALSE)
  RETURN INTEGER;

Parameters

Parameter	Mode	Description
c	IN	Cursor ID of the statement to execute.
exact	IN	(Optional) If TRUE, raises an exception when the query returns a number of rows other than 1. Default: FALSE.

Return values

Return value	Description
1	A row was fetched successfully.
0	No rows were returned.

Example

CREATE TABLE test(id number, c RAW(20));
INSERT INTO test VALUES (1, 'aaabbbccc');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  r_var  RAW(10);
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c,
      'SELECT c FROM test WHERE id = :id',
      DBMS_SQL.NATIVE);
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_RAW(c, 1, r_var, 10);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  DBMS_SQL.COLUMN_VALUE_RAW(c, 1, r_var);
  -- COLUMN c is: \x616161626262636363
  DBMS_OUTPUT.PUT_LINE('COLUMN c is: ' || r_var);

  DBMS_SQL.CLOSE_CURSOR(c);
END;

FETCH_ROWS

Fetches the next row from an executed cursor. Call this in a loop and exit when it returns 0.

Syntax

DBMS_SQL.FETCH_ROWS (c IN INTEGER)
  RETURN INTEGER;

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.

Return values

Return value	Description
1	A row was fetched successfully.
0	No more rows to fetch.

Example

DECLARE
  c         INTEGER;
  processd  INTEGER;
  strval    VARCHAR2(100);
  intval    INTEGER;
  nrows     INTEGER DEFAULT 5;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c, 'select ''num'' || i, i from generate_series(1, :nrows) g(i)',
                 DBMS_SQL.NATIVE);
  dbms_sql.BIND_VARIABLE(c, 'nrows', nrows);
  dbms_sql.DEFINE_COLUMN(c, 1, strval);
  dbms_sql.DEFINE_COLUMN(c, 2, intval);
  processd := DBMS_SQL.EXECUTE(c);
  WHILE DBMS_SQL.FETCH_ROWS(c) > 0
  LOOP
    DBMS_SQL.COLUMN_VALUE(c, 1, strval);
    DBMS_SQL.COLUMN_VALUE(c, 2, intval);
    DBMS_OUTPUT.PUT_LINE('c1: ' || strval || ', c2: ' || intval);
    DBMS_OUTPUT.PUT_LINE('last count is: ' || DBMS_SQL.LAST_ROW_COUNT());
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c);
END;

-- c1: num1, c2: 1
-- last count is: 1
-- c1: num2, c2: 2
-- last count is: 2
-- c1: num3, c2: 3
-- last count is: 3
-- c1: num4, c2: 4
-- last count is: 4
-- c1: num5, c2: 5
-- last count is: 5

IS_OPEN

Checks whether a cursor is currently open.

Syntax

DBMS_SQL.IS_OPEN (c IN INTEGER)
  RETURN BOOLEAN;

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.

Return values

Return value	Description
TRUE	The cursor is open.
FALSE	The cursor is not open.

Example

DECLARE
  c INTEGER;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  -- CURSOR is open? true
  DBMS_OUTPUT.PUT_LINE('CURSOR is open? ' || DBMS_SQL.IS_OPEN(c));

  DBMS_SQL.CLOSE_CURSOR(c);
  -- CURSOR is open? false
  DBMS_OUTPUT.PUT_LINE('CURSOR is open? ' || DBMS_SQL.IS_OPEN(c));
END;

LAST_ROW_COUNT

Returns the cumulative number of rows fetched.

Syntax

DBMS_SQL.LAST_ROW_COUNT
  RETURN INTEGER;

Return values

Return value	Description
INTEGER	Cumulative number of rows fetched.

Example

DECLARE
  c         INTEGER;
  processd  INTEGER;
  strval    VARCHAR2(100);
  intval    INTEGER;
  nrows     INTEGER DEFAULT 5;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c, 'select ''num'' || i, i from generate_series(1, :nrows) g(i)',
                 DBMS_SQL.NATIVE);
  dbms_sql.BIND_VARIABLE(c, 'nrows', nrows);
  dbms_sql.DEFINE_COLUMN(c, 1, strval);
  dbms_sql.DEFINE_COLUMN(c, 2, intval);
  processd := DBMS_SQL.EXECUTE(c);
  WHILE DBMS_SQL.FETCH_ROWS(c) > 0
  LOOP
    DBMS_SQL.COLUMN_VALUE(c, 1, strval);
    DBMS_SQL.COLUMN_VALUE(c, 2, intval);
  END LOOP;
  -- last count is: 5
  DBMS_OUTPUT.PUT_LINE('last count is: ' || DBMS_SQL.LAST_ROW_COUNT());
  DBMS_SQL.CLOSE_CURSOR(c);
END;

OPEN_CURSOR

Opens a new cursor and returns its ID. Pass this ID to all subsequent DBMS_SQL calls.

Syntax

DBMS_SQL.OPEN_CURSOR (
  [security_level               IN INTEGER]
  [,treat_as_client_for_results IN BOOLEAN DEFAULT FALSE])
  RETURN INTEGER;

Parameters

Parameter	Mode	Description
security_level	IN	(Optional) Provided for Oracle compatibility.
treat_as_client_for_results	IN	(Optional) Provided for Oracle compatibility.

Return values

Return value	Description
INTEGER	The ID of the newly opened cursor.

Example

DECLARE
  c INTEGER;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  -- CURSOR id is: 1
  DBMS_OUTPUT.PUT_LINE('CURSOR id is: ' || c);
END;

PARSE

Parses a SQL statement and associates it with a cursor. Three overloads are available: one accepts a single VARCHAR2 string, and two accept an array of statement fragments (VARCHAR2A or VARCHAR2S) with explicit bounds.

Syntax

Overload 1 — single VARCHAR2 statement:

DBMS_SQL.PARSE (
   c                          IN INTEGER,
   statement                  IN VARCHAR2,
   language_flag              IN INTEGER,
   edition                    IN VARCHAR2 DEFAULT NULL,
   apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
   fire_apply_trigger         IN BOOLEAN DEFAULT TRUE,
   schema                     IN VARCHAR2 DEFAULT NULL,
   container                  IN VARCHAR2);

Overload 2 — VARCHAR2A array:

DBMS_SQL.PARSE (
   c                          IN INTEGER,
   statement                  IN VARCHAR2a,
   lb                         IN INTEGER,
   ub                         IN INTEGER,
   lfflg                      IN BOOLEAN,
   language_flag              IN INTEGER,
   edition                    IN VARCHAR2 DEFAULT NULL,
   apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
   fire_apply_trigger         IN BOOLEAN DEFAULT TRUE,
   schema                     IN VARCHAR2 DEFAULT NULL,
   container                  IN VARCHAR2);

Overload 3 — VARCHAR2S array:

DBMS_SQL.PARSE (
   c                          IN INTEGER,
   statement                  IN VARCHAR2s,
   lb                         IN INTEGER,
   ub                         IN INTEGER,
   lfflg                      IN BOOLEAN,
   language_flag              IN INTEGER,
   edition                    IN VARCHAR2 DEFAULT NULL,
   apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
   fire_apply_trigger         IN BOOLEAN DEFAULT TRUE,
   schema                     IN VARCHAR2 DEFAULT NULL,
   container                  IN VARCHAR2);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
statement	IN	SQL statement to parse. Accepts VARCHAR2, VARCHAR2A, or VARCHAR2S depending on the overload.
language_flag	IN	Controls statement behavior. Valid values: `DBMS_SQL.V6`, `DBMS_SQL.NATIVE`, `DBMS_SQL.V7`.
lb	IN	Lower index of the statement array (overloads 2 and 3).
ub	IN	Upper index of the statement array (overloads 2 and 3).
lfflg	IN	If TRUE, a newline character is inserted after each element during concatenation (overloads 2 and 3).
edition	IN	(Optional) Provided for Oracle compatibility.
apply_crossedition_trigger	IN	(Optional) Provided for Oracle compatibility.
fire_apply_trigger	IN	(Optional) Provided for Oracle compatibility.
schema	IN	(Optional) Provided for Oracle compatibility.
container	IN	(Optional) Provided for Oracle compatibility.

Example

CREATE TABLE test(a int, b varchar2, c numeric);

DECLARE
  c int;
  a int[];
  b varchar[];
  ca numeric[];
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c, 'insert into test values(:a, :b, :c)', DBMS_SQL.NATIVE);
  a := ARRAY[1, 2, 3];
  b := ARRAY['Alice', 'Bob', 'Cindy'];
  ca := ARRAY[5, 4, 3];

  DBMS_SQL.BIND_ARRAY(c, 'a', a);
  DBMS_SQL.BIND_ARRAY(c, 'b', b);
  DBMS_SQL.BIND_ARRAY(c, 'c', ca);
-- inserted rows: 3
  DBMS_OUTPUT.PUT_LINE('inserted rows: ' || DBMS_SQL.EXECUTE(c));
  DBMS_SQL.CLOSE_CURSOR(c);
END;

SELECT * FROM test ORDER BY 1, 2, 3;
 a |   b   | c
---+-------+---
 1 | Alice | 5
 2 | Bob   | 4
 3 | Cindy | 3
(3 rows)

VARIABLE_VALUE

Retrieves the current value of a named bind variable from a cursor. Use this after EXECUTE to read values returned through a RETURNING clause or an OUT bind variable.

Syntax

DBMS_SQL.VARIABLE_VALUE (
   c     IN    INTEGER,
   name  IN    VARCHAR2,
   value INOUT ANYELEMENT);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
name	IN	Name of the bind variable to retrieve.
value	INOUT	Variable to receive the retrieved value.

Example

CREATE TABLE test(id number, c VARCHAR2(30));
INSERT INTO test VALUES (1, 'bind_variable for varchar2');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  c_var  VARCHAR2(25);
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c,
      'UPDATE test SET c = :n WHERE id = :id RETURNING c INTO :n',
      DBMS_SQL.NATIVE);

  id_var := 1;
  c_var := 'bind_variable_varchar2';

  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.BIND_VARIABLE(c, 'n', c_var, 25);

  ignore := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.VARIABLE_VALUE(c, 'n', c_var);

  -- VARCHAR returned is:bind_variable_varchar2
  DBMS_OUTPUT.PUT_LINE('VARCHAR returned is:' || c_var);
  DBMS_SQL.CLOSE_CURSOR(c);
END;

VARIABLE_VALUE_CHAR

Retrieves the CHAR value of a named bind variable from a cursor.

Syntax

DBMS_SQL.VARIABLE_VALUE_CHAR (
   c     IN    INTEGER,
   name  IN    VARCHAR2,
   value INOUT CHAR);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
name	IN	Name of the bind variable to retrieve.
value	INOUT	CHAR variable to receive the retrieved value.

Example

CREATE TABLE test(id number, c VARCHAR2(30));
INSERT INTO test VALUES (1, 'bind_variable for char');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  c_var  CHAR(25);
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c,
      'UPDATE test SET c = :n WHERE id = :id RETURNING c INTO :n',
      DBMS_SQL.NATIVE);

  id_var := 1;
  c_var := 'bind_variable_char';

  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.BIND_VARIABLE_CHAR(c, 'n', c_var, 25);

  ignore := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.VARIABLE_VALUE(c, 'n', c_var);

  -- CHAR returned is:bind_variable_char
  DBMS_OUTPUT.PUT_LINE('CHAR returned is:' || c_var);
  DBMS_SQL.CLOSE_CURSOR(c);
END;

VARIABLE_VALUE_RAW

Retrieves the RAW value of a named bind variable from a cursor.

Syntax

DBMS_SQL.VARIABLE_VALUE_RAW (
   c     IN    INTEGER,
   name  IN    VARCHAR2,
   value INOUT RAW);

Parameters

Parameter	Mode	Description
c	IN	Cursor ID.
name	IN	Name of the bind variable to retrieve.
value	INOUT	RAW variable to receive the retrieved value.

Example

CREATE TABLE test(id number, c RAW(20));
INSERT INTO test VALUES (1, 'aaabbbccc');

DECLARE
  c      NUMBER;
  ignore NUMBER;
  id_var NUMBER;
  r_var  RAW(10);
BEGIN
  c := DBMS_SQL.OPEN_CURSOR();
  DBMS_SQL.PARSE(c,
      'SELECT c FROM test WHERE id = :id',
      DBMS_SQL.NATIVE);
  id_var := 1;
  DBMS_SQL.BIND_VARIABLE(c, 'id', id_var);
  DBMS_SQL.DEFINE_COLUMN_RAW(c, 1, r_var, 10);
  ignore := DBMS_SQL.EXECUTE_AND_FETCH(c);
  DBMS_SQL.COLUMN_VALUE_RAW(c, 1, r_var);
  -- COLUMN c is: \x616161626262636363
  DBMS_OUTPUT.PUT_LINE('COLUMN c is: ' || r_var);

  DBMS_SQL.CLOSE_CURSOR(c);
END;