Oracle | DBMS

Oracle DBMS_SQL Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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

Please note that Binary_Bouble_Type, as humorous as it may be, has been intentionally retained by Oracle for purposes of backward compatibility.

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_Bouble_Type (yes, valid)	PLS_INTEGER	101
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
foreign_syntax	INTEGER	4294967295

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_rec3 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, col_type_name VARCHAR2(32) := '', col_type_name_len BINARY_INTEGER := 0); TYPE desc_rec4 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 dbms_id := '', 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, col_type_name dbms_id := '', col_type_name_len BINARY_INTEGER := 0); 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 desc_tab3 IS TABLE OF desc_rec3 INDEX BY BINARY_INTEGER; TYPE desc_tab3 IS TABLE OF desc_rec4 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 134 objects

Documented Yes: Packages and Types Reference

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 AUDSYS, DVSYS, ORACLE_OCM, ORDSYS, and PUBLIC
Clearly no one at Oracle pays sufficient intention to database security. The grant of EXECUTE to PUBLIC should be dropped.

Source {ORACLE_HOME}/rdbms/admin/dbmssql.sql

Subprograms

BIND_ARRAY	DEFINE_COLUMN_CHAR	LAST_ROW_ID
BIND_VARIABLE	DEFINE_COLUMN_LONG	LAST_SQL_FUNCTION_CODE
BIND_VARIABLE_CHAR	DEFINE_COLUMN_RAW	OPEN_CURSOR
BIND_VARIABLE_PKG	DEFINE_COLUMN_ROWID	PARSE
BIND_VARIABLE_RAW	DESCRIBE_COLUMNS	RETURN_RESULT
BIND_VARIABLE_ROWID	DESCRIBE_COLUMNS2	TO_CURSOR_NUMBER
CLOSE_CURSOR	DESCRIBE_COLUMNS3	TO_REFCURSOR
COLUMN_VALUE	EXECUTE	VARIABLE_VALUE
COLUMN_VALUE_CHAR	EXECUTE_AND_FETCH	VARIABLE_VALUE_CHAR
COLUMN_VALUE_LONG	FETCH_ROWS	VARIABLE_VALUE_PKG
COLUMN_VALUE_RAW	GET_NEXT_RESULT	VARIABLE_VALUE_RAW
COLUMN_VALUE_ROWID	IS_OPEN	VARIABLE_VALUE_ROWID
DEFINE_ARRAY	LAST_ERROR_POSITION	Demos
DEFINE_COLUMN	LAST_ROW_COUNT

BIND_ARRAY

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@pdbdev

      

      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@pdbdev

      

      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

Binds a variable of BINARY_FLOAT type

Overload 16 dbms_sql.bind_variable( c IN INTEGER, name IN VARCHAR2, value IN BINARY_FLOAT);

TBD

Binds a variable of BINARY_DOUBLE type

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

Binds a variable of Boolean type

Overload 23 dbms_sql.bind_variable( c IN INTEGER, name IN VARCHAR2, value IN BOOLEAN);

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_PKG

Binds a variable of record type

Overload 1 dbms_sql.bind_variable_pkg( c IN INTEGER, name IN VARCHAR2, value IN "<RECORD_1>"); pragma interface(c, bind_variable_pkg);

TBD

Binds a variable of index by table type

Overload 2 dbms_sql.bind_variable_pkg( c IN INTEGER, name IN VARCHAR2, value IN "<V2_TABLE_1>"); pragma interface(c, bind_variable_pkg);

TBD

Binds a variable of nested table type

Overload 3 dbms_sql.bind_variable_pkg( c IN INTEGER, name IN VARCHAR2, value IN "<TABLE_1>"); pragma interface(c, bind_variable_pkg);

TBD

Binds a variable of varray type

Overload 4 dbms_sql.bind_variable_pkg( c IN INTEGER, name IN VARCHAR2, value IN "<VARRAY_1>"); pragma interface(c, bind_variable_pkg);

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

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

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

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

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

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

Returns value of named variable for given cursor

Overload 1 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT NUMBER);

TBD

Overload 2 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT VARCHAR2 CHARACTER SET ANY_CS);

TBD

Overload 3 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT DATE);

TBD

Overload 4 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT BLOB);

TBD

Overload 5 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT CLOB CHARACTER SET ANY_CS);

TBD

Overload 6 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT BFILE);

TBD

Overload 7 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT NUMBER_TABLE);

TBD

Overload 8 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT VARCHAR2_TABLE);

TBD

Overload 9 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT DATE_TABLE);

TBD

Overload 10 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT BLOB_TABLE);

TBD

Overload 11 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT CLOB_TABLE);

TBD

Overload 12 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT BFILE_TABLE);

TBD

Overload 13 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT UROWID);

TBD

Overload 14 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY UROWID_TABLE);

TBD

Overload 15 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT TIME_UNCONSTRAINED);

TBD

Overload 16 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY TIME_TABLE);

TBD

Overload 17 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT TIMESTAMP_UNCONSTRAINED);

TBD

Overload 18 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY TIMESTAMP_TABLE);

TBD

Overload 19 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT TIME_TZ_UNCONSTRAINED);

TBD

Overload 20 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY TIME_WITH-TIME_ZONE_TABLE);

TBD

Overload 21 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT TIMESTAMP_TZ_UNCONSTRAINED);

TBD

Overload 22 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY TIMESTAMP_WITH_TIME_ZONE_TABLE);

TBD

Overload 23 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT TIMESTAMP_LTZ_UNCONSTRAINED);

TBD

Overload 24 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY TIMESTAMP_WITH_LTZ_TABLE);

TBD

Overload 25 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT YMINTERVAL_UNCONSTRAINED);

TBD

Overload 26 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY INTERVAL_YEAR_TO_MONTH_TABLE);

TBD

Overload 27 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT DSINTERVAL_UNCONSTRAINED);

TBD

Overload 28 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY INTERVAL_DAY_TO_SECOND_TABLE);

TBD

Overload 29 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT BINARY_FLOAT);

TBD

Overload 30 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY BINARY_FLOAT_TABLE);

TBD

Overload 31 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT BINARY_DOUBLE);

TBD

Binary Double Support

Overload 32 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY BINARY_DOUBLE_TABLE);

TBD

ADT Data Type Support
Overload 33 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT "<ADT_1>");

TBD

REF ADT Data Type Support
Overload 34 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT REF "<ADT_1>");

TBD

Table Type Support
Overload 35 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT "<TABLE_1>");

TBD

Varray Support
Overload 36 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT "<VARRAY_1>");

TBD

Opaque Data Type Support
Overload 37 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT "<OPAQUE_1>");

TBD

32K Support

Overload 38 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT dbms_sql.varchar2a);

TBD

Boolean data type

Overload 39 dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT BOOLEAN);

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_PKG

Returns a variable of record type

Overload 1 dbms_sql.variable_value_pkg( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY "<RECORD_1>"); PRAGMA_INTERFACE(c, variable_value_pkg);

See {$ORACLE_HOME/rdbms/admin/dbmssql.sql

Returns a variable of index by table type

Overload 2 dbms_sql.variable_value_pkg( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY "<V2_TABLE_1>"); PRAGMA_INTERFACE(c, variable_value_pkg);

See {$ORACLE_HOME/rdbms/admin/dbmssql.sql

Returns a variable of nested table type

Overload 3 dbms_sql.variable_value_pkg( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY "<TABLE_1>"); PRAGMA_INTERFACE(c, variable_value_pkg);

See {$ORACLE_HOME/rdbms/admin/dbmssql.sql

Returns a variable of varray type

Overload 4 dbms_sql.variable_value_pkg( c IN INTEGER, name IN VARCHAR2, value OUT NOCOPY "<VARRAY_1>"); PRAGMA_INTERFACE(c, variable_value_pkg);

See {$ORACLE_HOME/rdbms/admin/dbmssql.sql

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;