DBMS_SQL - PolarDB - Alibaba Cloud Documentation Center

The built-in DBMS_SQL package provides dynamic SQL queries to dynamically execute SQL statements during the runtime of the application.

Subprograms

Subprogram	Description
BIND_ARRAY Procedure	Binds a value of the array type to a variable.
BIND_VARIABLE Procedure	Binds a value to a variable.
BIND_VARIABLE_CHAR Procedure	Binds a value of the CHAR type to a variable.
BIND_VARIABLE_RAW Procedure	Binds a value of the RAW type to a variable.
CLOSE_CURSOR Procedure	Disables a cursor.
COLUMN_VALUE Procedure	Returns the value of the element at a position in a cursor.
COLUMN_VALUE_CHAR Procedure	Returns the value of the column of the CHAR type at a position in a cursor.
COLUMN_VALUE_LONG Procedure	Returns the value of the column of the LONG type at a position in a cursor.
COLUMN_VALUE_RAW Procedure	Returns the value of the column of the RAW type at a position in a cursor.
DEFINE_ARRAY Procedure	Defines the array that you want to select from a cursor.
DEFINE_COLUMN Procedure	Defines the column that you want to select from a cursor.
DEFINE_COLUMN_CHAR Procedure	Defines the column of the CHAR type that you want to select from a cursor.
DEFINE_COLUMN_LONG Procedure	Defines the column of the LONG type that you want to select from a cursor.
DEFINE_COLUMN_RAW Procedure	Defines the column of the RAW type that you want to select from a cursor.
EXECUTE Function	Executes a cursor.
EXECUTE_AND_FETCH Function	Executes a cursor and fetches row data.
FETCH_ROWS Function	Fetches row data from a cursor.
IS_OPEN Function	Determines whether a cursor is opened.
LAST_ROW_COUNT Function	Counts the cumulative number of rows that have been fetched.
OPEN_CURSOR Function	Returns the ID of the cursor that is newly opened.
PARSE Procedure	Parses a statement.
VARIABLE_VALUE Procedure	Returns the value of a named variable in a cursor.
VARIABLE_VALUE_CHAR Procedure	Returns the value of a named variable of the CHAR type in a cursor.
VARIABLE_VALUE_RAW Procedure	Returns the value of a named variable of the RAW type in a cursor.

Data types

VARCHAR2A

TYPE varchar2a IS TABLE OF varchar2(32767);

VARCHAR2S

TYPE varchar2s IS TABLE OF varchar2(256);

BIND_ARRAY

This stored procedure is used to bind a value of the array type to a variable.

Syntax

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

Parameters

Parameter	Description
c	The cursor ID of the value that you want to bind.
name	The name of the array in the statement.
value	The local variable that you want to bind.
index1	(Optional) The index that marks the lower limit of the array range. Default value: -1.
index2	(Optional) The index that marks the upper limit of the array range. Default value: -1.

Examples

The following example shows how to use the BIND_ARRAY stored procedure to insert multiple rows of data into the destination table:

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

This stored procedure is used to bind a value to a variable.

Syntax

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

Parameters

Parameter	Description
c	The cursor ID of the value that you want to bind.
name	The name of the variable in the statement.
value	The local variable that you want to bind.
out_value_size	(Optional) The maximum expected byte size of the OUT variable. Default value: -1.

Examples

The following example shows how to use the BIND_VARIABLE stored procedure to bind a variable:

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

This stored procedure is used to bind a value of the CHAR type to a 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	Description
c	The cursor ID of the value that you want to bind.
name	The name of the variable in the statement.
value	The local variable of the CHAR type that you want to bind.
out_value_size	(Optional) The maximum expected byte size of the OUT variable. Default value: -1.

Examples

The following example shows how to bind a variable of the CHAR type:

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

This stored procedure is used to bind a value of the RAW type to a 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	Description
c	The cursor ID of the value that you want to bind.
name	The name of the variable in the statement.
value	The local variable of the RAW type that you want to bind.
out_value_size	(Optional) The maximum expected byte size of the OUT variable. Default value: -1.

Examples

The following example shows how to bind a variable of the RAW type:

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

This stored procedure is used to disable a cursor.

Syntax

DBMS_SQL.CLOSE_CURSOR (
   c  INOUT INTEGER);

Parameters

Parameter	Description
c	The ID of the cursor.

Examples

The following example shows how to use the CLOSE_CURSOR stored procedure to disable a cursor:

DECLARE
  c   NUMBER;
BEGIN 
  c := DBMS_SQL.OPEN_CURSOR(); 
  DBMS_SQL.CLOSE_CURSOR(c);
END;

COLUMN_VALUE

This stored procedure is used to return the value of the element at a position in a cursor.

Syntax

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

Parameters

Parameter	Description
c	The cursor ID of the value that you want to fetch.
pos	The relative position of the column in the cursor.
value	The value of the column.
column_error	(Optional) The parameter is provided for compatibility with Oracle.
actual_length	(Optional) The parameter is provided for compatibility with Oracle.

Examples

The following example shows how to fetch the value of the element at a position in a cursor:

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

This stored procedure is used to return the value of the column of the CHAR type at a position 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	Description
c	The cursor ID of the value that you want to fetch.
pos	The relative position of the column in the cursor.
value	The value of the column.
column_error	(Optional) The parameter is provided for compatibility with Oracle.
actual_length	(Optional) The parameter is provided for compatibility with Oracle.

Examples

The following example shows how to fetch the cursor data of the CHAR type:

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

This stored procedure is used to return the value of the column of the LONG type at a position in a cursor.

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	Description
c	The cursor ID of the value that you want to fetch.
pos	The relative position of the column in the cursor.
length	The number of bytes of the value of the LONG type that you want to fetch.
off	The offset of the attribute of the LONG type.
value	The column value of the VARCHAR2 type.
value_length	The actual number of returned bytes.

Examples

The following example shows how to fetch the data of the LONG type in a cursor:

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

This stored procedure is used to return the value of the column of the RAW type at a position 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	Description
c	The cursor ID of the value that you want to fetch.
pos	The relative position of the column in the cursor.
value	The value of the column.
column_error	(Optional) The parameter is provided for compatibility with Oracle.
actual_length	(Optional) The parameter is provided for compatibility with Oracle.

Examples

The following example shows how to fetch the data of the RAW type in a cursor:

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

This stored procedure is used to define the array that you want to select from a cursor.

Syntax

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

Parameters

Parameter	Description
c	The cursor ID of the value that you want to define.
pos	The relative position of the column in the statement.
value	The local variable that you want to bind.
cnt	The number of rows that you want to fetch.
lower_bnd	The lower bound index.

Examples

The following example shows how to define the array that you want to select from a cursor:

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

This stored procedure is used to define the column that you want to select from a cursor.

Syntax

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

Parameters

Parameter	Description
c	The cursor ID of the value that you want to define.
pos	The relative position of the column in the statement.
col	The local variable that you want to bind.
column_size	(Optional) The maximum expected byte size of the column value. Default value: -1.

Examples

The following example shows how to define the column that you want to select from a cursor:

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

This stored procedure is used to define the column of the CHAR type that you want to select 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	Description
c	The cursor ID of the value that you want to define.
pos	The relative position of the column in the statement.
col	The local variable that you want to bind.
column_size	(Optional) The maximum expected byte size of the column value. Default value: -1.

Examples

The following example shows how to define the column of the CHAR type that you want to select from a cursor:

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

This stored procedure is used to define the column of the LONG type that you want to select from a cursor.

Syntax

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

Parameters

Parameter	Description
c	The cursor ID of the value that you want to define.
pos	The relative position of the column in the statement.

Examples

The following example shows how to define the column of the LONG type that you want to select from a cursor:

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

This stored procedure is used to define the column of the RAW type that you want to select 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	Description
c	The cursor ID of the value that you want to define.
pos	The relative position of the column in the statement.
col	The local variable that you want to bind.
column_size	(Optional) The maximum expected byte size of the column value. Default value: -1.

Examples

The following example shows how to define the column of the RAW type that you want to select from a cursor:

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

This function is used to execute a cursor.

Syntax

DBMS_SQL.EXECUTE (c IN INTEGER)
  RETURN INTEGER;

Parameters

Parameter	Description
c	The cursor ID of the SQL statement that you want to execute.

Return values

Return value	Description
BIGINT	If the SQL statement is of the DELETE, INSERT, or UPDATE type, this parameter indicates the number of processed records.

Examples

The following example shows how to use the EXECUTE function to execute an SQL statement:

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

This function is used to execute a cursor and fetch row data.

Syntax

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

Parameters

Parameter	Description
c	The cursor ID of the SQL statement that you want to execute.
exact	(Optional) Specifies whether an exact match is required. If the number of matched rows in the query is not equal to 1 and this parameter is set to TRUE, an exception is thrown. Default value: FALSE.

Return values

Return value	Description
INTEGER	If a row of data is fetched as expected, the value 1 is returned. If no data is fetched, the value 0 is returned.

Examples

The following example shows how to use the EXECUTE_AND_FETCH function to execute an SQL statement and fetch execution results:

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

This function is used to fetch row data from a cursor.

Syntax

DBMS_SQL.FETCH_ROWS (c IN INTEGER)
  RETURN INTEGER;

Parameters

Parameter	Description
c	The cursor ID of the data that you want to fetch.

Return values

Return value	Description
INTEGER	If the data is successfully fetched, the value 1 is returned. If no data is fetched, the value 0 is returned.

Examples

The following example shows how to fetch the execution results of the SQL statement specified by the EXECUTE function:

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

This function is used to determine whether a cursor is opened.

Syntax

DBMS_SQL.IS_OPEN (c IN INTEGER)
  RETURN BOOLEAN;

Parameters

Parameter	Description
c	The ID of the cursor.

Return values

Return value	Description
BOOLEAN	If the cursor is opened, the value TRUE is returned. Otherwise, the value FALSE is returned.

Examples

The following example shows how to determine whether a cursor is opened:

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

This function is used to count the cumulative number of rows that have been fetched.

Syntax

DBMS_SQL.LAST_ROW_COUNT 
   RETURN INTEGER;

Return values

Return value	Description
INTEGER	The number of rows that have been fetched.

Examples

The following example shows how to use the FETCH_ROWS function to fetch the execution results of a SELECT statement and then use the LAST_ROW_COUNT function to count the number of rows:

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

This function is used to return the ID of the cursor that is newly opened.

Syntax

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

Parameters

Parameter	Description
security_level	(Optional) The parameter is provided for compatibility with Oracle.
treat_as_client_for_results	(Optional) The parameter is provided for compatibility with Oracle.

Return values

Return value	Description
INTEGER	The ID of the cursor that is created.

Examples

The following example shows how to open a new cursor:

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

PARSE

This stored procedure is used to parse a statement.

Syntax

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

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

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	Description
c	The cursor ID of the SQL statement that you want to parse.
statement	The SQL statement that you want to parse.
language_flag	The behavior of the SQL statement. Valid values: DBMS_SQL.V6 DBMS_SQL.NATIVE DBMS_SQL.V7
lb	The lower boundary of the element in the statement.
ub	The upper boundary of the element in the statement.
lfflg	If this parameter is set to TRUE, a newline character is inserted after each element during concatenation.
edition	(Optional) The parameter is provided for compatibility with Oracle.
apply_crossediapply_crossedition_trigger	(Optional) The parameter is provided for compatibility with Oracle.
fire_apply_trigger	(Optional) The parameter is provided for compatibility with Oracle.
schema	(Optional) The parameter is provided for compatibility with Oracle.
container	(Optional) The parameter is provided for compatibility with Oracle.

Examples

The following example shows how to parse an array of SQL statements:

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

This stored procedure is used to return the value of a named variable in a cursor.

Syntax

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

Parameters

Parameter	Description
c	The cursor ID of the value that you want to fetch.
name	The name of the variable whose value that you want to fetch.
value	The fetched value.

Examples

The following example shows how to fetch the value of a named variable in a cursor:

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

This stored procedure is used to return the value of a named variable of the CHAR type in a cursor.

Syntax

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

Parameters

Parameter	Description
c	The cursor ID of the value that you want to fetch.
name	The name of the variable whose value that you want to fetch.
value	The fetched value.

Examples

The following example shows how to fetch the value of a named variable of the CHAR type in a cursor:

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

This stored procedure is used to return the value of a named variable of the RAW type in a cursor.

Syntax

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

Parameters

Parameter	Description
c	The cursor ID of the value that you want to fetch.
name	The name of the variable whose value that you want to fetch.
value	The fetched value.

Examples

The following example shows how to fetch the value of a named variable of the RAW type in a cursor:

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;