Oracle DBMS_SQL
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Create and execute dynamic SQL statements within PL/SQL
Note DBMS_SQL is the traditional form of dynamic SQL in Oracle. For most purposes native dynamic sql (NDS) will be the dynamic SQL engine of choice but there are some things this package can do that can not be done any other way or certainly not with equal ease. This page emphasizes some of those areas where there is no substitute.
AUTHID CURRENT_USER
Data Type Constants
Name Data Type Value
Varchar2_Type PLS_INTEGER 1
Number_Type PLS_INTEGER 2
Long_Type PLS_INTEGER 8
Rowid_Type PLS_INTEGER 11
Date_Type PLS_INTEGER 12
Raw_Type PLS_INTEGER 23
Long_Raw_Type PLS_INTEGER 24
Char_Type PLS_INTEGER 96
Binary_Float_Type PLS_INTEGER 100
Binary_Double_Type PLS_INTEGER 101
MLSLabel_Type PLS_INTEGER 106
User_Defined_Type PLS_INTEGER 109
Ref_Type PLS_INTEGER 111
Clob_Type PLS_INTEGER 112
Blob_Type PLS_INTEGER 113
Bfile_Type PLS_INTEGER 114
Timestamp_Type PLS_INTEGER 180
Timestamp_With_TZ_Type PLS_INTEGER 181
Interval_Year_to_Month_Type PLS_INTEGER 182
Interval_Day_To_Second_Type PLS_INTEGER 183
Urowid_Type PLS_INTEGER 208
Timestamp_With_Local_TZ_type PLS_INTEGER 231
Language Flag Constants
Name Data Type Value
v6 INTEGER 0
native INTEGER 1
v7 INTEGER 2
Data Types General Types
TYPE desc_rec IS RECORD (
col_type BINARY_INTEGER := 0,
col_max_len BINARY_INTEGER := 0,
col_name varchar2(32) := '',
col_name_len BINARY_INTEGER := 0,
col_schema_name varchar2(32) := '',
col_schema_name_len BINARY_INTEGER := 0,
col_precision BINARY_INTEGER := 0,
col_scale BINARY_INTEGER := 0,
col_charsetid BINARY_INTEGER := 0,
col_charsetform BINARY_INTEGER := 0,
col_null_ok boolean := TRUE);

TYPE desc_rec2 IS RECORD (
col_type BINARY_INTEGER := 0,
col_max_len BINARY_INTEGER := 0,
col_name varchar2(32767) := '',
col_name_len BINARY_INTEGER := 0,
col_schema_name varchar2(32) := '',
col_schema_name_len BINARY_INTEGER := 0,
col_precision BINARY_INTEGER := 0,
col_scale BINARY_INTEGER := 0,
col_charsetid BINARY_INTEGER := 0,
col_charsetform BINARY_INTEGER := 0,
col_null_ok boolean := TRUE);

TYPE desc_tab IS TABLE OF desc_rec
INDEX BY BINARY_INTEGER;

TYPE desc_tab2 IS TABLE OF desc_rec2
INDEX BY BINARY_INTEGER;

TYPE varchar2a IS TABLE OF VARCHAR2(32767)
INDEX BY BINARY_INTEGER;

TYPE varchar2s IS TABLE OF VARCHAR2(256)
INDEX BY BINARY_INTEGER;

Bulk SQL Types
TYPE bfile_table IS TABLE OF bfile
INDEX BY BINARY_INTEGER;

TYPE binary_double_table IS TABLE OF binary_double
INDEX BY BINARY_INTEGER;

TYPE binary_float_table IS TABLE OF binary_float
INDEX BY BINARY_INTEGER;

TYPE blob_table IS TABLE OF blob
INDEX BY BINARY_INTEGER;

TYPE clob_table IS TABLE OF clob
INDEX BY BINARY_INTEGER;

TYPE date_table IS TABLE OF date
INDEX BY BINARY_INTEGER;

TYPE interval_day_to_second_table IS TABLE OF dsinterval_unconstrained
INDEX BY BINARY_INTEGER;

TYPE interval_year_to_MONTH_Table IS TABLE OF yminterval_unconstrained
INDEX BY BINARY_INTEGER;

TYPE number_table IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;

TYPE time_table IS TABLE OF time_unconstrained
INDEX BY BINARY_INTEGER;

TYPE time_with_time_zone_Table IS TABLE OF TIME_TZ_UNCONSTRAINED
INDEX BY BINARY_INTEGER;

TYPE timestamp_table IS TABLE OF timestamp_unconstrained
INDEX BY BINARY_INTEGER;

TYPE timestamp_with_ltz_table IS TABLE OF TIMESTAMP_LTZ_UNCONSTRAINED
INDEX BY BINARY_INTEGER;

TYPE urowid_table IS TABLE OF urowid
INDEX BY BINARY_INTEGER;

TYPE timestamp_with_time_zone_table IS TABLE OF TIMESTAMP_TZ_UNCONSTRAINED
INDEX BY BINARY_INTEGER;

TYPE varchar2_table IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;
Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_SQL'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_SQL';

-- returns 213 objects
Documented Yes
Exceptions
Error Code Reason
ORA-06562 Inconsistent types: Raised by procedure "column_value" or "variable_value" if the type of out argument where to put the requested value is different from the type of the value
First Available 7.3.4
Flow Diagram
Security Model Owned by SYS with EXECUTE granted to APEX_040200, ORACLE_OCM, ORDSYS, and PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmssql.sql
Subprograms
 
BIND_ARRAY (2 new 12.1 overloads)
Binds a given value to a given collection

Overload 1
dbms_sql.BIND_ARRAY(
c      IN INTEGER,
name   IN VARCHAR2,
n_tab  IN NUMBER_TABLE);
conn scott/tiger@pdborcl

DECLARE
 stmt          VARCHAR2(200);
 dept_no_array dbms_sql.number_table;
 c             NUMBER;
 dummy         NUMBER;
BEGIN
  dept_no_array(1) := 10; dept_no_array(2) := 20;
  dept_no_array(3) := 30; dept_no_array(4) := 40;
  dept_no_array(5) := 30; dept_no_array(6) := 40;
  stmt := 'delete from emp where deptno = :dept_array';
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, stmt, dbms_sql.NATIVE);
  dbms_sql.bind_array(c, ':dept_array', dept_no_array, 1, 4);
  dummy := dbms_sql.execute(c);
  dbms_sql.close_cursor(c);
EXCEPTION
  WHEN OTHERS THEN
    IF dbms_sql.is_open(c) THEN
      dbms_sql.close_cursor(c);
    END IF;
    RAISE;
END;
/
VARCHAR2 Support

Overload 2
dbms_sql.bind_array (
c     IN INTEGER,
name  IN VARCHAR2,
c_tab IN VARCHAR2_TABLE);
TBD
Date Support

Overload 3
dbms_sql.bind_array (
c     IN INTEGER,
name  IN VARCHAR2,
d_tab IN DATE_TABLE);
TBD
BLOB Support

Overload 4
dbms_sql.bind_array (
c      IN INTEGER,
name   IN VARCHAR2,
bl_tab IN BLOB_TABLE);
TBD
CLOB Support

Overload 5
dbms_sql.bind_array (
c      IN INTEGER,
name   IN VARCHAR2,
cl_tab IN CLOB_TABLE);
TBD
BFile Support

Overload 6
dbms_sql.bind_array (
c      IN INTEGER,
name   IN VARCHAR2,
bf_tab IN BFILE_TABLE);
TBD
Number Support

Overload 7
dbms_sql.bind_array (
c      IN INTEGER,
name   IN VARCHAR2,
n_tab  IN NUMBER_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
VARCHAR2 Support

Overload 8
dbms_sql.bind_array (
c      IN INTEGER,
name   IN VARCHAR2,
c_tab  IN VARCHAR2_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
Date Support

Overload 9
dbms_sql.bind_array (
c      IN INTEGER,
name   IN VARCHAR2,
d_tab  IN DATE_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
BLOB Support

Overload 10
dbms_sql.bind_array (
c      IN INTEGER,
name   IN VARCHAR2,
bl_tab IN BLOB_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
BFile Support

Overload 11
dbms_sql.bind_array (
c      IN INTEGER,
name   IN VARCHAR2,
cl_tab IN CLOB_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
BFile Support

Overload 12
dbms_sql.bind_array (
c      IN INTEGER,
name   IN VARCHAR2,
bf_tab IN BFILE_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
UROWID Support

Overload 13
dbms_sql.bind_array(
c      IN INTEGER,
name   IN VARCHAR2,
ur_tab IN UROWID_TABLE);
TBD
UROWID Support

Overload 14
dbms_sql.bind_array (
c      IN INTEGER,
name   IN VARCHAR2,
ur_tab IN UROWID_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
Time Support

Overload 15
dbms_sql.bind_array (
c      IN INTEGER,
name   IN VARCHAR2,
tm_tab IN TIME_TABLE);
TBD
Time Support

Overload 16
dbms_sql.bind_array (
c      IN INTEGER,
name   IN VARCHAR2,
tm_tab IN TIME_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
Timestamp Support

Overload 17
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
tms_tab IN TIMESTAMP_TABLE);
TBD
Timestamp Support

Overload 18
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
tms_tab IN TIMESTAMP_TABLE
index1  IN INTEGER,
index2  IN INTEGER);
TBD
Time with Time Zone Support

Overload 19
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
ttz_tab IN TIME_WITH_TIME_ZONE_TABLE);
TBD
Time with Time Zone Support

Overload 20
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
ttz_tab IN TIME_WITH_TIME_ZONE_TABLE,
index1  IN INTEGER,
index2  IN INTEGER);
TBD
Timestamp with Time Zone Support

Overload 21
dbms_sql.bind_array (
c        IN INTEGER,
name     IN VARCHAR2,
tstz_tab IN TIMESTAMP_WITH_TIME_ZONE_TABLE);
TBD
Timestamp with Time Zone Support

Overload 22
dbms_sql.bind_array (
c        IN INTEGER,
name     IN VARCHAR2,
tstz_tab IN TIMESTAMP_WITH_TIME_ZONE_TABLE,
index1   IN INTEGER,
index2   IN INTEGER);
TBD
Timestamp with Local Time Zone Support

Overload 23
dbms_sql.bind_array (
c        IN INTEGER,
name     IN VARCHAR2,
tstz_tab IN TIMESTAMP_WITH_LTZ_TABLE);
TBD
Timestamp with Local Time Zone Support

Overload 24
dbms_sql.bind_array (
c        IN INTEGER,
name     IN VARCHAR2,
tstz_tab IN TIMESTAMP_WITH_LTZ_TABLE,
index1   IN INTEGER,
index2   IN INTEGER);
TBD
Interval Year-Month Support

Overload 25
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
iym_tab IN INTERVAL_YEAR_TO_MONTH_TABLE);
TBD
Interval Year-Month Support

Overload 26
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
iym_tab IN INTERVAL_YEAR_TO_MONTH_TABLE,
index1  IN INTEGER,
index2  IN INTEGER);
TBD
Interval Day-Second Support

Overload 27
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
ids_tab IN INTERVAL_DAY_TO_SECOND_TABLE);
TBD
Interval Day-Second Support

Overload 28
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
ids_tab IN INTERVAL_DAY_TO_SECOND_TABLE,
index1  IN INTEGER,
index2  IN INTEGER);
TBD
Overload 29 dbms_sql.bind_array (
c        IN INTEGER,
name     IN VARCHAR2,
bflt_tab IN BINARY_FLOAT_TABLE);
TBD
Overload 30 dbms_sql.bind_array (
c        IN INTEGER,
name     IN VARCHAR2,
bflt_tab IN BINARY_FLOAT_TABLE,
index1   IN INTEGER,
index2   IN INTEGER);
TBD
Binary Double Support

Overload 31
dbms_sql.bind_array (
c        IN INTEGER,
name     IN VARCHAR2,
bdbl_tab IN BINARY_DOUBLE_TABLE);
TBD
Binary Double Support

Overload 32
dbms_sql.bind_array (
c        IN INTEGER,
name     IN VARCHAR2,
bdbl_tab IN BINARY_DOUBLE_TABLE,
index1   IN INTEGER,
index2   IN INTEGER);
TBD
32k varchar2 support for bind_array

Overload 33
dbms_sql.bind_array (
c     IN INTEGER,
name  IN VARCHAR2,
c_tab IN dbms_sql.varchar2a);
TBD
32k varchar2 support for bind_array

Overload 34
dbms_sql.bind_array(
c      IN INTEGER,
name   IN VARCHAR2,
c_tab  IN dbms_sql.varchar2a,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
 
BIND_VARIABLE
Binds a given value to a given variable

Overload 1
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN NUMBER);
conn scott/tiger@pdborcl

SELECT COUNT(*) FROM emp;

CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS
 cursor_name    INTEGER;
 rows_processed INTEGER;
BEGIN
  cursor_name := dbms_sql.open_cursor;
  dbms_sql.parse(cursor_name, 'DELETE FROM emp WHERE sal > :x', dbms_sql.NATIVE);
  dbms_sql.bind_variable(cursor_name, ':x', salary);
  rows_processed := dbms_sql.execute(cursor_name);
  dbms_sql.close_cursor(cursor_name);
EXCEPTION
  WHEN OTHERS THEN
    dbms_sql.close_cursor(cursor_name);
END demo;
/

exec demo(30001)

SELECT COUNT(*) FROM emp;

ROLLBACK;
Overload 2 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN VARCHAR2 CHARACTER SET ANY_CS);
TBD
Overload 3 dbms_sql.bind_variable(
c              IN INTEGER,
name           IN VARCHAR2,
value          IN VARCHAR2 CHARACTER SET ANY_CS,
out_value_size IN INTEGER);
TBD
Overload 4 dbms_sql.bind_variable (
c     IN INTEGER,
name  IN VARCHAR2,
value IN DATE);
TBD
Overload 5 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN BLOB);
TBD
Overload 6 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN CLOB CHARACTER SET ANY_CS);
TBD
Overload 7 dbms_sql.bind_variable (
c     IN INTEGER,
name  IN VARCHAR2,
value IN BFILE);
TBD
Overload 8 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN UROWID);
TBD
Overload 9 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN TIME_UNCONSTRAINED);
TBD
Overload 10 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN TIMESTAMP_UNCONSTRAINED);
TBD
Overload 11 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN TIME_TZ_UNCONSTRAINED);
TBD
Overload 12 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN TIMESTAMP_TZ_UNCONSTRAINED);
TBD
Overload 13 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN TIMESTAMP_LTZ_UNCONSTRAINED);
TBD
Overload 14 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN YMINTERVAL_UNCONSTRAINED);
TBD
Overload 15 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN DSINTERVAL_UNCONSTRAINED);
TBD
Overload 16 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN BINARY_FLOAT);
TBD
Overload 17 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN BINARY_DOUBLE);
TBD
Overload 18 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN "<ADT_1>");
TBD
Overload 19 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN REF "<ADT_1>");
TBD
Overload 20 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN "<TABLE_1>");
TBD
Overload 21 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN "<VARRAY_1>");
TBD
Overload 22 dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN "<OPAQUE_1>");
TBD
 
BIND_VARIABLE_CHAR
Binds a given value to a given variable

Overload 1
dbms_sql.bind_variable_char(
c     IN INTEGER,
name  IN VARCHAR2,
value IN CHAR CHARACTER SET ANY_CS);
TBD
Overload 2 dbms_sql.bind_variable_char(
c              IN INTEGER,
name           IN VARCHAR2,
value          IN CHAR CHARACTER SET ANY_CS,
out_value_size IN INTEGER);
TBD
 
BIND_VARIABLE_RAW
Binds a given value to a given variable

Overload 1
dbms_sql.bind_variable_raw(
c     IN INTEGER,
name  IN VARCHAR2,
value IN RAW);
TBD
Overload 2 dbms_sql.bind_variable_raw(
c              IN INTEGER,
name           IN VARCHAR2,
value          IN RAW,
out_value_size IN INTEGER);
TBD
 
BIND_VARIABLE_ROWID
Binds a given value to a given variable

Overload 1
dbms_sql.bind_variable_rowid(
c     IN INTEGER,
name  IN VARCHAR2,
value IN ROWID);
TBD
 
CLOSE_CURSOR
Closes cursor and free memory dbms_sql.close_cursor(c IN OUT INTEGER);
See IS_OPEN Demo Below
 
COLUMN_VALUE (new 12.1 overload)
Returns value of the cursor element for a given position in a cursor

Overload 1
dbms_sql.column_value
c        IN  INTEGER,
position IN  INTEGER,
value    OUT NUMBER);
TBD
VARCHAR2 Support

Overload 2
dbms_sql.column_value(
c         IN  INTEGER,
position  IN  INTEGER,
value     OUT VARCHAR2 CHARACTER SET ANY_CS);
TBD
DATE Support

Overload 3
dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT DATE);
TBD
BLOB Support

Overload 4
dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT BLOB);
TBD
CLOB Support

Overload 5
dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT CLOB CHARACTER SET ANY_CS);
TBD
BFile Support

Overload 6
dbms_sql.column_value (
c        IN  INTEGER,
position IN  INTEGER,
value    OUT BFILE);
TBD
Number Support

Overload 7
dbms_sql.column_value(
c             IN  INTEGER,
position      IN  INTEGER,
value         OUT NUMBER,
column_error  OUT NUMBER,
actual_length OUT INTEGER);
TBD
VARCHAR2 Support

Overload 8
dbms_sql.column_value(
c             IN  INTEGER,
position      IN  INTEGER,
value         OUT VARCHAR2 CHARACTER SET ANY_CS,
column_error  OUT NUMBER,
actual_length OUT INTEGER);
TBD
DATE Support

Overload 9
dbms_sql.column_value(
c             IN  INTEGER,
position      IN  INTEGER,
value         OUT DATE,
column_error  OUT NUMBER,
actual_length OUT INTEGER);
TBD
Overload 10 dbms_sql.column_value(
c        IN     INTEGER,
position  IN     INTEGER,
n_tab    IN OUT NOCOPY NUMBER_TABLE);
TBD
Overload 11 dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
c_tab    IN OUT NOCOPY VARCHAR2_TABLE);
TBD
Overload 12 dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
d_tab    IN OUT NOCOPY DATE_TABLE);
TBD
Overload 13 dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
bl_tab   IN OUT NOCOPY BLOB_TABLE);
TBD
Overload 14 dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
cl_tab   IN OUT NOCOPY CLOB_TABLE);
TBD
Overload 15 dbms_sql.column_value (
c        IN     INTEGER,
position IN     INTEGER,
bf_tab   IN OUT NOCOPY BFILE_TABLE);
TBD
Overload 16 dbms_sql.column_value (
c        IN  INTEGER,
position IN  INTEGER,
value    OUT UROWID);
TBD
Overload 17 dbms_sql.column_value (
c        IN     INTEGER,
position IN     INTEGER,
ur_tab   IN OUT NOCOPY UROWID_TABLE);
TBD
Overload 18 dbms_sql.column_value (
c        IN  INTEGER,
position IN  INTEGER,
value    OUT TIME_UNCONSTRAINED);
TBD
Overload 19 dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
tm_tab   IN OUT NOCOPY TIME_TABLE);
TBD
Overload 20 dbms_sql.column_value (
c        IN  INTEGER,
position IN  INTEGER,
value    OUT TIMESTAMP_UNCONSTRAINED);
TBD
Overload 21 dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
tms_tab  IN OUT NOCOPY TIMESTAMP_TABLE);
TBD
Overload 22 dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT TIME_TZ_UNCONSTRAINED);
TBD
Overload 23 dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
ttz_tab  IN OUT NOCOPY TIME_WITH_TIME_ZONE_TABLE);
TBD
Overload 24 dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT TIMESTAMP_TZ_UNCONSTRAINED);
TBD
Overload 25 dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
tstz_tab IN OUT NOCOPY TIMESTAMP_WITH_TIME_ZONE_TABLE);
TBD
Overload 26 dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT TIMESTAMP_LTZ_UNCONSTRAINED);
TBD
Overload 27 dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
tstz_tab IN OUT NOCOPY TIMESTAMP_WITH_LTZ_TABLE);
TBD
Overload 28 dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT YMINTERVAL_UNCONSTRAINED);
TBD
Overload 29 dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
iym_tab  IN OUT NOCOPY INTERVAL_YEAR_TO_MONTH_TABLE);
TBD
Overload 30 dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT DSINTERVAL_UNCONSTRAINED);
TBD
Overload 31 dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
ids_tab  IN OUT NOCOPY INTERVAL_DAY_TO_SECOND_TABLE);
TBD
Overload 32 dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT BINARY_FLOAT);
TBD
Overload 33 dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
bflt_tab IN OUT NOCOPY BINARY_FLOAT_TABLE);
TBD
Overload 34 dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT BINARY_DOUBLE);
TBD
Overload 35 dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
bdbl_tab IN OUT NOCOPY BINARY_DOUBLE_TABLE);
TBD
Overload 36 dbms_sql.column_value(
c        IN  INTEGER,
position IN  BINARY_INTEGER,
value    OUT "<ADT_1>");
TBD
Overload 37 dbms_sql.column_value(
c        IN  INTEGER,
position IN  BINARY_INTEGER,
value    OUT REF "<ADT_1>");
TBD
Overload 38 dbms_sql.column_value(
c        IN  INTEGER,
position IN  BINARY_INTEGER,
value    OUT "<TABLE_1>");
TBD
Overload 39 dbms_sql.column_value(
c        IN  INTEGER,
position IN  BINARY_INTEGER,
value    OUT "<VARRAY_1>");
TBD
Overload 40 dbms_sql.column_value(
c        IN  INTEGER,
position IN  BINARY_INTEGER,
value    OUT "<OPAQUE_1>");
TBD
Overload 41 dbms_sql.column_value(
c        IN     INTEGER,
position IN     BINARY_INTEGER,
c_tab    IN OUT dbms_sql.varchar2a);
TBD
 
COLUMN_VALUE_CHAR
Returns value of the cursor element for a given position in a cursor

Overload 1
dbms_sql.column_value_char(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT CHAR CHARACTER SET ANY_CS);
See COLUMN_VALUE In "Final" Demo At Page Bottom
Overload 2 dbms_sql.column_value_char(
c             IN  INTEGER,
position      IN  INTEGER,
value         OUT CHAR CHARACTER SET ANY_CS,
column_error  OUT NUMBER,
actual_length OUT INTEGER);
TBD
 
COLUMN_VALUE_LONG
Returns a selected part of a LONG column, that has been defined using DEFINE_COLUMN_LONG dbms_sql.column_value_long(
c            IN  INTEGER,
position     IN  INTEGER,
length       IN  INTEGER,
offset       IN  INTEGER,
value        OUT VARCHAR2,
value_length OUT INTEGER);
See COLUMN_VALUE In "Final" Demo At Page Bottom
 
COLUMN_VALUE_RAW
Returns value of the cursor element for a given position in a cursor

Overload 1
dbms_sql.column_value_raw(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT RAW);
See COLUMN_VALUE In "Final" Demo At Page Bottom
Overload 2 dbms_sql.column_value_raw(
c             IN  INTEGER,
position      IN  INTEGER,
value         OUT RAW,
column_error  OUT NUMBER,
actual_length OUT INTEGER);
TBD
 
COLUMN_VALUE_ROWID
Undocumented

Overload 1
dbms_sql.column_value_rowid(
c               IN  INTEGER,
position        IN  INTEGER,
value           OUT ROWID);
See COLUMN_VALUE In "Final" Demo At Page Bottom
Overload 2 dbms_sql.column_value_rowid(
c             IN  INTEGER,
position      IN  INTEGER,
value         OUT ROWID,
column_error  OUT NUMBER,
actual_length OUT INTEGER);
TBD
 
DEFINE_ARRAY (new 12.1 overload)
Defines a collection to be selected from the given cursor, used only with SELECT statements

Overload 1
dbms_sql.define_array(
c           IN INTEGER,
position    IN INTEGER,
n_tab       IN NUMBER_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
DECLARE
 c     NUMBER;
 d     NUMBER;
 n_tab dbms_sql.number_table;
 indx  NUMBER := -10;
BEGIN
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,'select n from t order by 1',dbms_sql.NATIVE);

  dbms_sql.define_array(c, 1, n_tab, 10, indx);

  d := dbms_sql.execute(c);

  LOOP
    d := dbms_sql.fetch_rows(c);
    dbms_sql.column_value(c, 1, n_tab);
    exit when d != 10;
  END LOOP;

  dbms_sql.close_cursor(c);
EXCEPTION
  WHEN OTHERS THEN
    IF dbms_sql.is_open(c) THEN
      dbms_sql.close_cursor(c);
    END IF;
    RAISE;
END;
/
Overload 2 dbms_sql.define_array (
c           IN INTEGER,
position    IN INTEGER,
c_tab       IN VARCHAR2_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 3 dbms_sql.define_array (
c           IN INTEGER,
position    IN INTEGER,
d_tab       IN DATE_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 4 dbms_sql.define_array (
c           IN INTEGER,
position    IN INTEGER,
bl_tab      IN BLOB_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 5 dbms_sql.define_array (
c           IN INTEGER,
position    IN INTEGER,
cl_tab      IN CLOB_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 6 dbms_sql.define_array (
c           IN INTEGER,
position    IN INTEGER,
bf_tab      IN BFILE_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 7 dbms_sql.define_array (
c           IN INTEGER,
position    IN INTEGER,
ur_tab      IN UROWID_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 8 dbms_sql.define_array(
c           IN INTEGER,
position    IN INTEGER,
tm_tab      IN TIME_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 9 dbms_sql.define_array(
c           IN INTEGER,
position    IN INTEGER,
tms_tab     IN TIMESTAMP_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 10 dbms_sql.define_array(
c           IN INTEGER,
position    IN INTEGER,
ttz_tab     IN TIME_WITH_TIME_ZONE_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 11 dbms_sql.define_array(
c           IN INTEGER,
position    IN INTEGER,
tstz_tab    IN TIMESTAMP_WITH_TIME_ZONE_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 12 dbms_sql.define_array(
c           IN INTEGER,
position    IN INTEGER,
tstz_tab    IN TIMESTAMP_WITH_LTZ_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 13 dbms_sql.define_array(
c           IN INTEGER,
position    IN INTEGER,
iym_tab     IN INTERVAL_YEAR_TO_MONTH_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 14 dbms_sql.define_array(
c           IN INTEGER,
position    IN INTEGER,
ids_tab     IN INTERVAL_DAY_TO_SECOND_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Binary Float Support

Overload 15
dbms_sql.define_array(
c           IN INTEGER,
position    IN INTEGER,
bflt_tab    IN dbms_sql.binary_float_table,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Binary Double Support

Overload 16
dbms_sql.define_array(
c           IN INTEGER,
position    IN INTEGER,
bdbl_tab    IN dbms_sql.binary_double_table,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
32K Support

Overload 17
dbms_sql.define_array(
c           IN INTEGER
position    IN INTEGER,
c_tab       IN dbms_sql.varchar2a,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
 
DEFINE_COLUMN
Defines a column to be selected from the given cursor, used only with SELECT statements

Overload 1
dbms_sql.define_column (
c        IN INTEGER,
position IN INTEGER,
column   IN NUMBER)
See DEFINE_COLUMN In "Final" Demo At Page Bottom
Overload 2 dbms_sql.define_column (
c           IN INTEGER,
position    IN INTEGER,
column      IN VARCHAR2 CHARACTER SET ANY_CS,
column_size IN INTEGER);
TBD
Overload 3 dbms_sql.define_column (
c           IN INTEGER,
position    IN INTEGER,
column      IN DATE);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
Overload 4 dbms_sql.define_column (
c           IN INTEGER,
position    IN INTEGER,
column      IN BLOB);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
Overload 5 dbms_sql.define_column (
c           IN INTEGER,
position    IN INTEGER,
column      IN CLOB);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
Overload 6 dbms_sql.define_column (
c           IN INTEGER,
position    IN INTEGER,
column      IN BFILE);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
Overload 7 dbms_sql.define_column (
c           IN INTEGER,
position    IN INTEGER,
column      IN UROWID);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
Overload 8 dbms_sql.define_column(
c           IN INTEGER,
position    IN INTEGER,
column      IN TIME_UNCONSTRAINED);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
Overload 9 dbms_sql.define_column(
c           IN INTEGER,
position    IN INTEGER,
column      IN TIMESTAMP_UNCONSTRAINED);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
Overload 10 dbms_sql.define_column(
c         IN INTEGER,
position  IN INTEGER,
column    IN TIME_TZ_UNCONSTRAINED);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
Overload 11 dbms_sql.define_column(
c        IN INTEGER,
position IN INTEGER,
column   IN TIMESTAMP_TZ_UNCONSTRAINED);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
Overload 12 dbms_sql.define_column(
c        IN INTEGER,
position IN INTEGER,
column   in TIMESTAMP_LTZ_UNCONSTRAINED);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
Overload 13 dbms_sql.define_column(
c        IN INTEGER,
position IN INTEGER,
column   IN YMINTERVAL_UNCONSTRAINED);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
Overload 14 dbms_sql.define_column(
c        IN INTEGER,
position IN INTEGER,
column   IN DSINTERVAL_UNCONSTRAINED);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
Overload 15 dbms_sql.define_column(
c        IN INTEGER,
position IN INTEGER,
column   IN BINARY_FLOAT);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
Overload 16 dbms_sql.define_column(
c        IN INTEGER,
position IN INTEGER,
column   IN BINARY_DOUBLE);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
Overload 17 dbms_sql.define_column(
c        IN INTEGER,
position IN BINARY_INTEGER,
column   IN "<ADT_1>");
TBD
Overload 18 dbms_sql.define_column(
c        IN INTEGER,
position IN BINARY_INTEGER,
column   IN REF "<ADT_1>");
TBD
Overload 19 dbms_sql.define_column(
c        IN INTEGER,
position IN BINARY_INTEGER,
column   IN "<TABLE_1>");
TBD
Overload 20 dbms_sql.define_column(
c        IN INTEGER,
position IN BINARY_INTEGER,
column   IN "<VARRAY_1>");
TBD
Overload 21 dbms_sql.define_column(
c        IN INTEGER,
position IN BINARY_INTEGER,
column   IN "<OPAQUE_1>");
TBD
 
DEFINE_COLUMN_CHAR
Undocumented dbms_sql.define_column_char(
c           IN INTEGER,
position    IN INTEGER,
column      IN CHAR CHARACTER SET ANY_CS,
column_size IN INTEGER);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
 
DEFINE_COLUMN_LONG
Defines a LONG column to be selected from the given cursor, used only with SELECT statements dbms_sql.define_column_long(c IN INTEGER, position IN INTEGER);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
 
DEFINE_COLUMN_RAW
Undocumented dbms_sql.define_column_raw(
c           IN INTEGER,
position    IN INTEGER,
column      IN RAW,
column_size IN INTEGER);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
 
DEFINE_COLUMN_ROWID
Undocumented dbms_sql.define_column_rowid(
c        IN INTEGER,
position IN INTEGER,
column   IN ROWID);
See DEFINE_COLUMN In "Final" Demo At Page Bottom
 
DESCRIBE_COLUMNS
Describes the columns for a cursor opened and parsed through DBMS_SQL dbms_sql.describe_columns(
c       IN  INTEGER,
col_cnt OUT INTEGER,
desc_t  OUT DESC_TAB);
DECLARE
 c       NUMBER;
 d       NUMBER;
 col_cnt PLS_INTEGER;
 f       BOOLEAN;
 rec_tab dbms_sql.desc_tab; -- use desc_tab2 if column names > 30 bytes
 col_num NUMBER;

PROCEDURE print_rec(rec in dbms_sql.desc_rec) IS
BEGIN
  dbms_output.new_line;
  dbms_output.put_line('col_type = ' || rec.col_type);
  dbms_output.put_line('col_maxlen = ' || rec.col_max_len);
  dbms_output.put_line('col_name = ' || rec.col_name);
  dbms_output.put_line('col_name_len = ' || rec.col_name_len);
  dbms_output.put_line('col_schema_name= ' || rec.col_schema_name);
  dbms_output.put_line('col_schema_name_len= ' || rec.col_schema_name_len);
  dbms_output.put_line('col_precision = ' || rec.col_precision);
  dbms_output.put_line('col_scale = ' || rec.col_scale);
  dbms_output.put('col_null_ok = ');

  IF (rec.col_null_ok) THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;


BEGIN
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,'select * from scott.bonus',dbms_sql.NATIVE);
  d := dbms_sql.execute(c);
  dbms_sql.describe_columns(c, col_cnt, rec_tab);

/* Following loop could simply be for j in 1..col_cnt loop. Here we are simply illustrating some of the PL/SQL table features. */
  col_num := rec_tab.first;

  IF (col_num IS NOT NULL) THEN
    LOOP
      print_rec(rec_tab(col_num));
      col_num := rec_tab.next(col_num);

      EXIT WHEN (col_num is null);
    END LOOP;
  END IF;

  dbms_sql.close_cursor(c);
END;
/
 
DESCRIBE_COLUMNS2
Describes the specified column, an alternative method fixing a bug dbms_sql.describe_columns2(
c         IN  INTEGER,
col_cnt   OUT INTEGER,
desc_tab2 OUT DESC_TAB2);
TBD
 
DESCRIBE_COLUMNS3
Describes the specified column, an alternative method dbms_sql.describe_columns2(
c       IN  INTEGER,
col_cnt OUT INTEGER,
desc_t  OUT DESC_TAB3);
TBD
 
EXECUTE
Execute the dynamic SQL identified by the cursor dbms_sql.execute(c IN INTEGER) RETURN INTEGER;
DECLARE
 sqlstr  VARCHAR2(50);
 tCursor PLS_INTEGER;
 RetVal  NUMBER;
BEGIN
  sqlstr := 'DROP SYNONYM my_synonym';
  tCursor := dbms_sql.open_cursor;
  dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
  RetVal := dbms_sql.execute(tCursor);
  dbms_sql.close_cursor(tCursor);
END;
/
 
EXECUTE_AND_FETCH
Executes a given cursor and fetch rows dbms_sql.execute_and_fetch(
c     IN INTEGER,
exact IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;
Demonstrated on the DBMS_LOB page under DBMS_LOB.READ
 
FETCH_ROWS
Fetches a row from a given cursor dbms_sql.fetch_rows(c IN INTEGER) RETURN INTEGER;
See COLUMN_VALUE In Demo At Page Bottom
 
GET_NEXT_RESULT (new 12.1)
Gets the statement of the next result returned to the caller of the recursive statement or, if this caller sets itself as the client for the recursive statement, the next result returned to this caller as client.
Overload 1
dbms_sql.get_next_result(c IN INTEGER, rc OUT SYS_REFCURSOR);
TBD
Overload 2 dbms_sql.get_next_result(c IN INTEGER, rc OUT INTEGER);
pragma restrict_references(get_next_result,RNDS,WNDS);
TBD
 
IS_OPEN
Determine whether a  cursor is open dbms_sql.is_open(c IN INTEGER) RETURN BOOLEAN;
set serveroutput on

DECLARE
 tCursor PLS_INTEGER;
BEGIN
  tCursor := dbms_sql.open_cursor;

  IF dbms_sql.is_open(tCursor) THEN
    dbms_output.put_line('1-OPEN');
  ELSE
    dbms_output.put_line('1-CLOSED');
  END IF;

  dbms_sql.close_cursor(tCursor);

  IF dbms_sql.is_open(tCursor) THEN
    dbms_output.put_line('2-OPEN');
  ELSE
    dbms_output.put_line('2-CLOSED');
  END IF;
END;
/
 
LAST_ERROR_POSITION
Returns byte offset in the SQL statement text where the error occurred dbms_sql.last_error_position RETURN INTEGER;
TBD
 
LAST_ROW_COUNT
Returns cumulative count of the number of rows fetched dbms_sql.last_row_count RETURN INTEGER;
TBD
 
LAST_ROW_ID
Returns ROWID of last row processed dbms_sql.last_row_id RETURN ROWID;
TBD
 
LAST_SQL_FUNCTION_CODE
Returns SQL function code for statement dbms_sql.last_sql_function_code RETURN INTEGER;
SELECT dbms_sql.last_sql_function_code
FROM dual;

DECLARE
 x NUMBER;
BEGIN
  execute immediate 'commit';
  x := dbms_sql.last_sql_function_code;
  dbms_output.put_line(TO_CHAR(x));
END;
/
 
OPEN_CURSOR
Open dynamic SQL cursor and return cursor ID number of new cursor

Overload 1
dbms_sql.open_cursor RETURN INTEGER;
See the IS_OPEN Demo Above
Overload 2 dbms_sql.open_cursor(security_level IN INTEGER) RETURN INTEGER;
  • Level 0 allows all DBMS_SQL operations on the cursor without any security checks. The cursor may be fetched from, and even re-bound and re-executed, by code running with a different effective userid or roles than those in effect at the time the cursor was parsed. This level of security is off by default.
  • Level 1 requires that the effective userid and roles of the caller to DBMS_SQL for bind and execute operations on this cursor must be the same as those of the caller of the most recent parse operation on this cursor.
  • Level 2 requires that the effective userid and roles of the caller to DBMS_SQL for all bind, execute, define, describe, and fetch operations on this cursor must be the same as those of the caller of the most recent parse operation on this cursor.
DECLARE
  sqlstr  VARCHAR2(50);
  tCursor PLS_INTEGER;
BEGIN
  sqlstr := 'DROP SYNONYM test_syn';
  tCursor := dbms_sql.open_cursor(2);
  dbms_sql.parse(tCursor, sqlstr, dbms_sql.native);
  dbms_sql.close_cursor(tCursor);
END;
/
Overload 3 dbms_sql.open_cursor(treat_as_client_for_result IN BOOLEAN) RETURN INTEGER;
DECLARE
 x INTEGER;
BEGIN
  x := dbms_sql.open_cursor(TRUE);
  dbms_output.put_line(TO_CHAR(x));
END;
/
Overload 4 dbms_sql.open_cursor(
security_level IN NUMBER,
treat_as_client_for_result IN BOOLEAN)
RETURN NUMBER;
DECLARE
 x INTEGER;
BEGIN
  x := dbms_sql.open_cursor(2, FALSE);
  dbms_output.put_line(TO_CHAR(x));
END;
/
 
PARSE (new 12.1 overloads)
Parse statement in VARCHAR2

Overload 1
dbms_sql.parse(
c             IN NUMBER,
statement     IN VARCHAR2,
language_flag IN NUMBER);
TBD
Parse PL/SQL table containing a SQL statement

Overload 2
dbms_sql.parse(
c             IN NUMBER,
statement     IN dbms_sql.varchar2a,
lb            IN NUMBER,
ub            IN NUMBER,
lfflg         IN BOOLEAN,
language_flag IN NUMBER);
TBD
Parse PL/SQL table containing a SQL statement

Overload 3
dbms_sql.parse(
c             IN NUMBER,
statement     IN dbms_sql.varchar2s,
lb            IN NUMBER,
ub            IN NUMBER,
lfflg         IN BOOLEAN,
language_flag IN NUMBER);
TBD
Parse statement in CLOB

Overload 4
dbms_sql.parse(
c             IN NUMBER,
statement     IN CLOB,
language_flag IN NUMBER);
TBD
Parse statement in VARCHAR2 naming the edition

Overload 5
dbms_sql.parse(
c             IN NUMBER,
statement     IN VARCHAR2,
language_flag IN NUMBER,
edition       IN VARCHAR2);
TBD
Parse statement in VARCHAR2 naming the edition and specify cross-edition trigger behavior

Overload 6
dbms_sql.parse(
c                           IN NUMBER,
statement                   IN VARCHAR2,
language_flag               IN NUMBER,
edition                     IN VARCHAR2 DEFAULT NULL,
apply_cross_edition_trigger IN VARCHAR2,
fire_apply_trigger          IN BOOLEAN  DEFAULT TRUE
);
TBD
Parse statement in CLOB naming the edition

Overload 7
dbms_sql.parse(
c             IN NUMBER,
statement     IN CLOB,
language_flag IN NUMBER,
edition       IN VARCHAR2);
TBD
Parse statement in CLOB naming the edition and specify cross-edition trigger behavior

Overload 8
dbms_sql.parse(
c                           IN NUMBER,
statement                   IN CLOB,
language_flag               IN NUMBER,
edition                     IN VARCHAR2 DEFAULT NULL,
apply_cross_edition_trigger IN VARCHAR2,
fire_apply_trigger          IN BOOLEAN  DEFAULT TRUE
);
TBD
Parse PL/SQL table containing a SQL statement

Overload 9
dbms_sql.parse(
c             IN NUMBER,
statement     IN dbms_sql.varchar2a,
lb            IN NUMBER,
ub            IN NUMBER,
lfflg         IN BOOLEAN,
language_flag IN NUMBER,
edition       IN VARCHAR2
);
TBD
Parse PL/SQL table containing a SQL statement naming the edition and specify cross-edition trigger behavior

Overload 10
dbms_sql.parse(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2a,
lb                         IN NUMBER,
ub                         IN NUMBER,
lfflg                      IN BOOLEAN,
language_flag              IN NUMBER,
edition                    IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN  DEFAULT TRUE);
TBD
Parse PL/SQL table containing a SQL statement

Overload 11
dbms_sql.parse(
c             IN NUMBER,
statement     IN dbms_sql.varchar2s,
lb            IN NUMBER,
ub            IN NUMBER,
lfflg         IN BOOLEAN,
language_flag IN NUMBER,
edition       IN VARCHAR2
);
TBD
Parse PL/SQL table containing a SQL statement naming the edition and specify cross-edition trigger behavior

Overload 12
dbms_sql.parse(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2s,
lb                         IN NUMBER,
ub                         IN NUMBER,
lfflg                      IN BOOLEAN,
language_flag              IN NUMBER,
edition                    IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN  DEFAULT TRUE);
TBD
Overload 13 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);
CREATE SYNONYM test_syn FOR dual;

SELECT *
FROM test_syn;

SELECT synonym_name
FROM user_synonyms;

DECLARE
  sqlstr  VARCHAR2(50);
  tCursor PLS_INTEGER;
BEGIN
  sqlstr := 'DROP SYNONYM test_syn';
  tCursor := dbms_sql.open_cursor;
  dbms_sql.parse(tCursor, sqlstr, dbms_sql.native);
  dbms_sql.close_cursor(tCursor);
END;
/

SELECT synonym_name
FROM user_synonyms;
-- with RETURNING clause

CREATE OR REPLACE PROCEDURE single_row_insert(c1 NUMBER, c2 NUMBER, r OUT NUMBER) IS
 c NUMBER;
 n NUMBER;
BEGIN
  c := dbms_sql.open_cursor;

  dbms_sql.parse(c, 'INSERT INTO tab VALUES (:bnd1, :bnd2) ' ||
  'returning c1*c2 into :bnd3', 2);

  dbms_sql.bind_variable(c, 'bnd1', c1);
  dbms_sql.bind_variable(c, 'bnd2', c2);
  dbms_sql.bind_variable(c, 'bnd3', r);

  n := dbms_sql.execute(c);

  dbms_sql.variable_value(c, 'bnd3', r); -- get value of outbind
  dbms_sql.close_cursor(c);
END single_row_insert;
/
Overload 14 dbms_sql.parse(
c                          IN INTEGER,
statement                  IN CLOB,
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);
TBD
Overload 15 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);
TBD
Overload 16 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);
TBD
Overload 17 dbms_sql.parse(
c                          IN NUMBER,
statement                  IN VARCHAR2,
language_flag              IN NUMBER,
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);
TBD
Overload 18 dbms_sql.parse(
c                          IN NUMBER,
statement                  IN CLOB,
language_flag              IN NUMBER,
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);
TBD
Overload 19 dbms_sql.parse(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2a,
lb                         IN NUMBER,
ub                         IN NUMBER,
lfflg                      IN BOOLEAN,
language_flag              IN NUMBER,
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);
TBD
Overload 20 dbms_sql.parse(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2s,
lb                         IN NUMBER,
ub                         IN NUMBER,
lfflg                      IN BOOLEAN,
language_flag              IN NUMBER,
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);
TBD
 
RETURN_RESULT (new 12.1)
Returns the result of an executed statement to the client application

Overload 1
dbms_sql.return_result(
rc        IN OUT SYS_REFCURSOR,
to_client IN     BOOLEAN DEFAULT TRUE);
TBD
Overload 2 dbms_sql.return_result(
rc        IN OUT INTEGER,
to_client IN     BOOLEAN DEFAULT TRUE);
pragma restrict_references(return_result,RNDS,WNDS);
TBD
 
TO_CURSOR_NUMBER
Takes a REF CURSOR generated by TO_REFCURSOR and returns its handle dbms_sql.to_cursor_number(rc in out sys_refcursor) RETURN INTEGER;
See Weakly Typed Ref Cursor Demos using the REF CURSOR link at page bottom
 
TO_REFCURSOR
Takes a DBMS_SQL OPENed, PARSEd, and EXECUTEd cursor and transforms/migrates it into PL/SQL manageable REF CURSOR dbms_sql.to_refcursor(cursor_number IN OUT INTEGER) RETURN SYS_REFCURSOR;
CREATE OR REPLACE TYPE vc_array IS TABLE OF VARCHAR2(200);
/

CREATE OR REPLACE TYPE numlist IS TABLE OF NUMBER;
/

CREATE OR REPLACE PROCEDURE do_query_1(placeholder vc_array, bindvars vc_array, sql_stmt VARCHAR2) IS
 TYPE curtype IS REF CURSOR;
 src_cur   curtype;
 curid     NUMBER;
 bindnames vc_array;
 empnos    numlist;
 depts     numlist;
 ret       NUMBER;
 isopen    BOOLEAN;
BEGIN
  -- open SQL cursor number
  curid := dbms_sql.open_cursor;

  -- parse SQL cursor number
  dbms_sql.parse(curid, sql_stmt, DBMS_SQL.NATIVE);

  bindnames := placeholder;

  -- bind arguments
  FOR i IN 1 .. bindnames.COUNT LOOP
    dbms_sql.bind_variable(curid, bindnames(i), bindvars(i));
  END LOOP;

  -- execute SQL cursor number
  ret := dbms_sql.execute(curid);

  -- switch from DBMS_SQL to native dynamic SQL
  src_cur := dbms_sql.to_refcursor(curid);
  FETCH src_cur BULK COLLECT INTO empnos, depts;

-- This would cause an error because curid was converted to a REF CURSOR:
-- isopen := DBMS_SQL.IS_OPEN(curid);

CLOSE src_cur;
END;
/
 
VARIABLE_VALUE (new 12.1 overload)
Returns value of named variable for given cursor

Overload 1
dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT NUMBER);
TBD
Oveload 2 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT VARCHAR2 CHARACTER SET ANY_CS);
TBD
Oveload 3 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT DATE);
TBD
Oveload 4 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT BLOB);
TBD
Oveload 5 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT CLOB CHARACTER SET ANY_CS);
TBD
Oveload 6 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT BFILE);
TBD
Oveload 7 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN VARCHAR2,
value IN NUMBER_TABLE);
TBD
Oveload 8 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN VARCHAR2,
value IN VARCHAR2_TABLE);
TBD
Oveload 9 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN VARCHAR2,
value IN DATE_TABLE);
TBD
Oveload 10 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN VARCHAR2,
value IN BLOB_TABLE);
TBD
Oveload 11 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN VARCHAR2,
value IN CLOB_TABLE);
TBD
Oveload 12 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN VARCHAR2,
value IN BFILE_TABLE);
TBD
Oveload 13 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT UROWID);
TBD
Oveload 14 dbms_sql.variable_value(
c     IN         INTEGER,
name  IN         VARCHAR2,
value OUT NOCOPY UROWID_TABLE);
TBD
Oveload 15 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT TIME_UNCONSTRAINED);
TBD
Oveload 16 dbms_sql.variable_value(
c     IN         INTEGER,
name  IN         VARCHAR2,
value OUT NOCOPY TIME_TABLE);
TBD
Oveload 17 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT TIMESTAMP_UNCONSTRAINED);
TBD
Oveload 18 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT NOCOPY TIMESTAMP_TABLE);
TBD
Oveload 19 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT TIME_TZ_UNCONSTRAINED);
TBD
Oveload 20 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT NOCOPY TIME_WITH-TIME_ZONE_TABLE);
TBD
Oveload 21 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT TIMESTAMP_TZ_UNCONSTRAINED);
TBD
Oveload 22 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT NOCOPY TIMESTAMP_WITH_TIME_ZONE_TABLE);
TBD
Oveload 23 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT TIMESTAMP_LTZ_UNCONSTRAINED);
TBD
Oveload 24 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT NOCOPY TIMESTAMP_WITH_LTZ_TABLE);
TBD
Oveload 25 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT YMINTERVAL_UNCONSTRAINED);
TBD
Oveload 26 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT NOCOPY INTERVAL_YEAR_TO_MONTH_TABLE);
TBD
Oveload 27 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT DSINTERVAL_UNCONSTRAINED);
TBD
Oveload 28 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT NOCOPY INTERVAL_DAY_TO_SECOND_TABLE);
TBD
Oveload 29 dbms_sql.variable_value(c IN INTEGER, name IN VARCHAR2, value OUT BINARY_FLOAT);
TBD
Oveload 30 dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT NOCOPY BINARY_FLOAT_TABLE);
TBD
Oveload 31 dbms_sql.variable_value(c IN INTEGER, name IN VARCHAR2, value OUT BINARY_DOUBLE);
TBD
Binary Double Support

Oveload 32
dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT NOCOPY BINARY_DOUBLE_TABLE);
TBD
ADT Data Type Support
Oveload 33
dbms_sql.variable_value(c IN INTEGER, name IN VARCHAR2, value OUT "<ADT_1>");
TBD
REF ADT Data Type Support
Oveload 34
dbms_sql.variable_value(c IN INTEGER, name IN VARCHAR2, value OUT REF "<ADT_1>");
TBD
Table Type Support
Oveload 35
dbms_sql.variable_value(c IN INTEGER, name IN VARCHAR2, value OUT "<TABLE_1>");
TBD
Varray Support
Oveload 36
dbms_sql.variable_value(c IN INTEGER, name IN VARCHAR2, value OUT "<VARRAY_1>");
TBD
Opaque Data Type Support
Oveload 37
dbms_sql.variable_value(c IN INTEGER, name IN VARCHAR2, value OUT "<OPAQUE_1>");
TBD
32K Support

Oveload 38
dbms_sql.variable_value(
c    IN INTEGER,
name  IN VARCHAR2,
value IN dbms_sql.varchar2a);
TBD
 
VARIABLE_VALUE_CHAR
Undocumented dbms_sql.variable_value_char(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT CHAR CHARACTER SET ANY_CS);
TBD
 
VARIABLE_VALUE_RAW
Undocumented dbms_sql.variable_value_raw(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT RAW);
TBD
 
VARIABLE_VALUE_ROWID
Undocumented dbms_sql.variable_value_rowid(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT ROWID);
set serveroutput on

DECLARE
 c      INTEGER;
 rid    VARCHAR2(20);
 retval ROWID;
BEGIN
  SELECT rowid
  INTO rid
  FROM servers
  WHERE rownum = 1;

  dbms_output.put_line(rid);

  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, 'DELETE FROM servers WHERE rowid = :x', dbms_sql.NATIVE);
  dbms_sql.bind_variable_rowid(c, ':x', rid);
  dbms_sql.variable_value_rowid(c, ':x', retval);

  dbms_sql.close_cursor(c);

  dbms_output.put_line(retval);
END;
/
 
Demos
Drop Synonym Demo SELECT synonym_name
FROM user_synonyms;

CREATE SYNONYM d FOR dept;
CREATE SYNONYM e FOR emp;

SELECT synonym_name
FROM user_synonyms;

DECLARE
 CURSOR syn_cur IS
 SELECT synonym_name
 FROM user_synonyms;

 RetVal  NUMBER;
 sqlstr  VARCHAR2(200);
 tCursor PLS_INTEGER;
BEGIN
  FOR syn_rec IN syn_cur
  LOOP
    sqlstr := 'DROP SYNONYM ' || syn_rec.synonym_name;
    tCursor := dbms_sql.open_cursor;
    dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
    RetVal := dbms_sql.execute(tCursor);
    dbms_sql.close_cursor(tCursor);
  END LOOP;
END dropsyn;
/

SELECT synonym_name
FROM user_synonyms;
Executing CLOBS Demo Tables conn uwclass/uwclass@pdbdev

CREATE TABLE workstations (
srvr_id     NUMBER(10),
ws_id       NUMBER(10),
location_id NUMBER(10),
cust_id     VARCHAR2(15),
status      VARCHAR2(1),
latitude    FLOAT(20),
longitude   FLOAT(20),
netaddress  VARCHAR2(15));

CREATE TABLE test (test VARCHAR2(50));
Demonstration dynamic SQL CREATE OR REPLACE PROCEDURE execute_plsql_block(plsql_code_block CLOB) IS

ds_cur    PLS_INTEGER := dbms_sql.open_cursor;
sql_table dbms_sql.VARCHAR2S;

c_buf_len CONSTANT BINARY_INTEGER := 256;
v_accum   INTEGER := 0;
v_beg     INTEGER := 1;
v_end     INTEGER := 256;
v_loblen  PLS_INTEGER;
v_RetVal  PLS_INTEGER;

---------------------------
-- local function to the execute_plsql_block procedure

FUNCTION next_row(
 clob_in IN CLOB,
 len_in IN INTEGER,
 off_in IN INTEGER) RETURN VARCHAR2 IS
BEGIN
  RETURN DBMS_LOB.SUBSTR(clob_in, len_in, off_in);
END next_row;

---------------------------
BEGIN
  v_loblen := DBMS_LOB.GETLENGTH(plsql_code_block);
  INSERT INTO test
  (test)
  VALUES
  ('Demo block is ' || TO_CHAR(v_loblen) || ' bytes in length');
  COMMIT;

  LOOP
    -- set the length to the remaining size ... if there are < c_buf_len characters remaining.
    IF v_accum + c_buf_len > v_loblen THEN
      v_end := v_loblen - v_accum;
    END IF;

    sql_table(NVL(sql_table.LAST, 0) + 1) :=
    next_row(plsql_code_block, v_end, v_beg);

    v_beg := v_beg + c_BUF_LEN;
    v_accum := v_accum + v_end;

    IF v_accum >= v_loblen THEN
      EXIT;
    END IF;
  END LOOP;

  -- parse the pl/sql and execute it
   dbms_sql.parse(ds_cur, sql_table, sql_table.FIRST, sql_table.LAST,
  FALSE, dbms_sql.NATIVE);

  v_RetVal := dbms_sql.execute(ds_cur);

   dbms_sql.close_cursor(ds_cur);
END execute_plsql_block;
/
Executing CLOBs Demo Data DECLARE
 clob_in CLOB;
BEGIN
  clob_in := CAST('BEGIN
  INSERT INTO WORKSTATIONS VALUES (1,1,20075,'''',''Y'',32.97948,-117.2569,'''');
  INSERT INTO WORKSTATIONS VALUES (1,10,20077,'''',''N'',32.97125,-117.2675,'''');
  INSERT INTO WORKSTATIONS VALUES (1,11,20078,'''',''N'',33.03865,-96.83579,'''');
  INSERT INTO WORKSTATIONS VALUES (1,12,20079,'''',''Y'',32.97413,-117.2694,''10.128.48.121'');
  INSERT INTO WORKSTATIONS VALUES (1,2,20081,'''',''N'',32.97948,-117.2569,'''');
  INSERT INTO WORKSTATIONS VALUES (1,3,20082,'''',''Y'',32.97948,-117.2569,''10.128.0.1'');
  INSERT INTO WORKSTATIONS VALUES (1,4,20083,''15689'',''N'',32.98195,-117.2636,'''');
  INSERT INTO WORKSTATIONS VALUES (1,5,20085,'''',''Y'',32.98195,-117.2636,''10.128.16.105'');
  INSERT INTO WORKSTATIONS VALUES (1,6,20086,'''',''N'',32.97096,-117.2689,'''');
  INSERT INTO WORKSTATIONS VALUES (1,7,20077,'''',''Y'',32.97125,-117.2675,''10.128.48.105'');
  INSERT INTO WORKSTATIONS VALUES (1,8,20090,'''',''N'',32.97124,-117.2676,'''');
  INSERT INTO WORKSTATIONS VALUES (1,9,20092,'''',''N'',32.97023,-117.2688,'''');
  INSERT INTO WORKSTATIONS VALUES (10,1,20094,'''',''Y'',61.2224,-149.8047,''10.128.112.1'');
  INSERT INTO WORKSTATIONS VALUES (10,2,20095,'''',''N'',61.2224,-149.8047,'''');
  INSERT INTO WORKSTATIONS VALUES (10,3,20096,'''',''Y'',61.2224,-149.8047,''10.128.112.113'');
  INSERT INTO WORKSTATIONS VALUES (10,4,13545,'''',''Y'',61.14104,-149.9519,''10.128.112.121'');
  INSERT INTO WORKSTATIONS VALUES (10,5,20104,'''',''N'',61.2224,-149.8047,'''');
  INSERT INTO WORKSTATIONS VALUES (10,6,20106,'''',''Y'',61.21685,-149.8002,''10.128.80.113'');
  INSERT INTO WORKSTATIONS VALUES (11,1,20110,'''',''N'',61.137,-149.9395,'''');
  INSERT INTO WORKSTATIONS VALUES (11,10,20113,'''',''N'',61.14104,-149.9519,'''');
  INSERT INTO WORKSTATIONS VALUES (11,11,20116,'''',''N'',61.14104,-149.9519,'''');
  INSERT INTO WORKSTATIONS VALUES (11,12,20117,'''',''Y'',61.137,-149.9395,''10.128.32.193'');
  INSERT INTO WORKSTATIONS VALUES (11,13,20118,'''',''Y'',61.137,-149.9395,''10.128.16.129'');
  INSERT INTO WORKSTATIONS VALUES (11,14,20119,'''',''Y'',61.137,-149.9395,'''');
  INSERT INTO WORKSTATIONS VALUES (11,15,20121,'''',''N'',61.13695,-149.9396,'''');
  INSERT INTO WORKSTATIONS VALUES (11,16,20122,'''',''N'',61.13695,-149.9396,'''');
  INSERT INTO WORKSTATIONS VALUES (11,17,13545,'''',''Y'',61.14104,-149.9519,'''');
  INSERT INTO WORKSTATIONS VALUES (11,18,19922,'''',''Y'',61.13549,-149.959,''10.128.48.153'');
  INSERT INTO WORKSTATIONS VALUES (11,19,19923,'''',''N'',61.13422,-149.962,'''');
  INSERT INTO WORKSTATIONS VALUES (11,2,19924,'''',''N'',61.137,-149.9395,'''');
  INSERT INTO WORKSTATIONS VALUES (11,20,19925,'''',''N'',61.146,-149.9799,'''');
  INSERT INTO WORKSTATIONS VALUES (11,21,19926,'''',''N'',61.146,-149.9799,'''');
  INSERT INTO WORKSTATIONS VALUES (11,22,19927,'''',''N'',61.13695,-149.9396,'''');
  INSERT INTO WORKSTATIONS VALUES (11,23,19928,'''',''N'',61.14104,-149.9519,'''');
  INSERT INTO WORKSTATIONS VALUES (11,24,19930,'''',''N'',61.13422,-149.962,'''');
  INSERT INTO WORKSTATIONS VALUES (11,25,19931,'''',''N'',61.13678,-149.9644,'''');
  INSERT INTO WORKSTATIONS VALUES (11,26,20033,'''',''N'',61.14477,-149.9586,'''');
  INSERT INTO WORKSTATIONS VALUES (11,27,20034,'''',''N'',61.13466,-149.975,'''');
  INSERT INTO WORKSTATIONS VALUES (11,28,20035,'''',''N'',61.14142,-149.9668,'''');
  INSERT INTO WORKSTATIONS VALUES (11,29,20036,'''',''N'',61.13695,-149.9396,'''');
  INSERT INTO WORKSTATIONS VALUES (11,3,20037,'''',''Y'',61.137,-149.9395,''10.128.16.105'');
  INSERT INTO WORKSTATIONS VALUES (11,30,20038,'''',''N'',61.13695,-149.9396,'''');
  INSERT INTO WORKSTATIONS VALUES (11,31,20039,'''',''N'',61.13695,-149.9396,'''');
  INSERT INTO WORKSTATIONS VALUES (11,32,20040,'''',''N'',61.13695,-149.9396,'''');
  INSERT INTO WORKSTATIONS VALUES (11,33,20042,'''',''N'',61.12887,-149.9578,'''');
  INSERT INTO WORKSTATIONS VALUES (11,4,20043,'''',''N'',61.137,-149.9395,'''');
  INSERT INTO WORKSTATIONS VALUES (11,5,20044,'''',''Y'',61.137,-149.9395,''10.128.32.129'');
  INSERT INTO WORKSTATIONS VALUES (11,6,20045,'''',''N'',61.137,-149.9395,'''');
  INSERT INTO WORKSTATIONS VALUES (11,7,20046,'''',''N'',61.137,-149.9395,'''');
  INSERT INTO WORKSTATIONS VALUES (11,8,20047,'''',''N'',61.137,-149.9395,'''');
  INSERT INTO WORKSTATIONS VALUES (11,9,20048,'''',''Y'',61.137,-149.9395,''10.128.32.169'');
  INSERT INTO WORKSTATIONS VALUES (12,1,20051,''15706'',''Y'',32.75604,-117.1201,'''');
  INSERT INTO WORKSTATIONS VALUES (12,10,20053,'''',''N'',32.75689,-117.12,'''');
  INSERT INTO WORKSTATIONS VALUES (12,100,20054,'''',''N'',32.7596,-117.124,'''');
  INSERT INTO WORKSTATIONS VALUES (12,101,20056,'''',''N'',32.75689,-117.129,'''');
  INSERT INTO WORKSTATIONS VALUES (12,102,20057,'''',''Y'',32.75677,-117.1241,''10.129.112.25'');
  INSERT INTO WORKSTATIONS VALUES (12,103,20058,'''',''Y'',32.75662,-117.124,''10.129.112.33'');
  INSERT INTO WORKSTATIONS VALUES (12,104,20060,'''',''N'',32.7571,-117.1242,'''');
  INSERT INTO WORKSTATIONS VALUES (12,105,20061,'''',''N'',32.75316,-117.1253,'''');
  INSERT INTO WORKSTATIONS VALUES (12,106,20063,'''',''N'',32.76154,-117.1251,'''');
  COMMIT;
  END;' AS CLOB);
  execute_plsql_block(clob_in);
END;
/

set linesize 121

SELECT * FROM workstations;
Final Demo conn uwclass/uwclass@pdbdev

CREATE TABLE one (
id    NUMBER(5),
name  VARCHAR2(30),
dob   DATE);

CREATE TABLE two (
id    NUMBER(5),
name  VARCHAR2(30),
dob   DATE);

INSERT INTO one
(id, name, dob)
VALUES
(100, 'Dan Morgan', SYSDATE+10);

INSERT INTO one
(id, name, dob)
VALUES
(200, 'Joze Senegacnik', SYSDATE-10);
COMMIT;


CREATE OR REPLACE PROCEDURE copy_proc(src_tab VARCHAR2,
dest_tab VARCHAR2) IS

col1     NUMBER(5);
col2     VARCHAR2(30);
col3     DATE;
src_cur  INTEGER;
dest_cur INTEGER;
ignore   INTEGER;

BEGIN
  -- open cursor on source table
  src_cur := dbms_sql.open_cursor;

  -- parse the SELECT statement
  dbms_sql.parse(src_cur, 'SELECT id, name, dob FROM ' || src_tab,
  dbms_sql.NATIVE);

  -- define the column type
  dbms_sql.define_column(src_cur, 1, col1);
  dbms_sql.define_column(src_cur, 2, col2, 30);
  dbms_sql.define_column(src_cur, 3, col3);

  ignore := dbms_sql.execute(src_cur);

  -- open cursor on destination table
  dest_cur := dbms_sql.open_cursor;

  -- parse the INSERT statement
  dbms_sql.parse(dest_cur, 'INSERT INTO ' || dest_tab || ' VALUES
  (:n_bind, :c_bind, :d_bind)', dbms_sql.NATIVE);

    LOOP
      -- fetch a row from the source table
      IF dbms_sql.fetch_rows(src_cur) > 0 THEN
        -- get column values of the row
        dbms_sql.column_value(src_cur, 1, col1);
        dbms_sql.column_value(src_cur, 2, col2);
        dbms_sql.column_value(src_cur, 3, col3);

        -- bind in the values to be inserted
        dbms_sql.bind_variable(dest_cur, ':n_bind', col1);
        dbms_sql.bind_variable(dest_cur, ':c_bind', col2);
        dbms_sql.bind_variable(dest_cur, ':d_bind', col3);
        ignore := dbms_sql.execute(dest_cur);
      ELSE
        -- no more rows to copy
        EXIT;
      END IF;
    END LOOP;

  -- commit and close all cursors
  COMMIT;
  dbms_sql.close_cursor(src_cur);
  dbms_sql.close_cursor(dest_cur);
EXCEPTION
  WHEN OTHERS THEN
    IF dbms_sql.is_open(src_cur) THEN
      dbms_sql.close_cursor(src_cur);
    END IF;
    IF dbms_sql.is_open(dest_cur) THEN
      dbms_sql.close_cursor(dest_cur);
    END IF;
    RAISE;
END copy_proc;
/

exec copy_proc('ONE', 'TWO');

SELECT * FROM one;
SELECT * FROM two;
Bulk Insert Demo conn uwclass/uwclass@pdbdev

CREATE TABLE obj (
objno   INTEGER,
objname VARCHAR2(30));


CREATE OR REPLACE FUNCTION objname(rnum INTEGER) RETURN VARCHAR2 IS
 retval VARCHAR2(30);
BEGIN
  SELECT object_name
  INTO retval
  FROM (
    SELECT rownum X, object_name
    FROM all_objects
    WHERE rownum < 11)
  WHERE x = rnum+1;
  RETURN retval;
END objname;
/


DECLARE
 sqlstr        VARCHAR2(200);
 objno_array   dbms_sql.number_table;
 objname_array dbms_sql.varchar2_table;
 handle        NUMBER;
 dummy         NUMBER;
BEGIN
  FOR i IN 0..9
  LOOP
    objno_array(i) := 1000 + i;
    objname_array(i) := objname(i);
  END LOOP;

  sqlstr := 'INSERT INTO obj VALUES (:num_array, :name_array)';
  handle := dbms_sql.open_cursor;
  dbms_sql.parse(handle, sqlstr, dbms_sql.NATIVE);
  dbms_sql.bind_array(handle, ':num_array', objno_array);
  dbms_sql.bind_array(handle, ':name_array', objname_array);
  dummy := dbms_sql.execute(handle);
  dbms_sql.close_cursor(handle);

EXCEPTION
  WHEN OTHERS THEN
    IF dbms_sql.is_open(handle) THEN
      dbms_sql.close_cursor(handle);
    END IF;
    RAISE;
END;
/

SELECT * FROM obj;

Related Topics
DBMS_SQL2
Editioning Demo 4
Editions
Native Dynamic SQL
Packages
REF CURSORS

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved