Oracle DBMS_SQL
Version 11.1
General
Note:
DBMS_SQL is the traditional form of dynamic SQL in Oracle.
For most purposes native dynamic sql (NDS) will suffice but there are some things the DBMS_SQL package does that
can not be done any other way. This page emphasizes those areas where there is no substitute.
Source
{ORACLE_HOME}/rdbms/admin/dbmssql.sql
First Available
7.3.4
Constants
Defined 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 (2000)
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';
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
Flow
--
--
-----------
--
| open_cursor |
--
-----------
--
|
--
|
--
v
--
-----
-- ------------>| parse |
-- |
-----
-- |
|
-- |
| ---------
-- |
v |
-- |
-------------- |
-- |-------->| bind_variable | |
-- |
^ ------------- |
-- |
| |
|
-- |
-----------| |
-- |
|<--------
-- |
v
-- |
query?---------- yes ---------
-- |
|
|
-- |
no
|
-- |
|
|
-- |
v
v
-- |
-------
-------------
-- |----------->| execute |
->| define_column |
-- |
------- |
-------------
-- |
|------------ |
|
-- |
| |
----------|
-- |
v |
v
-- |
-------------- |
-------
-- |
->| variable_value | | ------>| execute |
-- |
| -------------- | |
-------
-- |
| |
| | |
-- |
----------| | |
|
-- |
| | |
v
-- |
| | |
----------
-- |
|<----------- |----->| fetch_rows |
-- |
| |
----------
-- |
| |
|
-- |
| |
v
-- |
| |
-----------------
-- |
| | | column_value
|
-- |
| | | variable_value
|
-- |
| |
-----------------
-- |
| |
|
-- |
|<--------------------------
-- |
|
--
-----------------|
--
|
--
v
--
------------
--
| close_cursor |
--
------------
--
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
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 ;
/
Overload 2
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
c_tab IN VARCHAR2_TABLE);
TBD
Overload 3
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
d_tab IN DATE_TABLE);
TBD
Overload 4
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
bl_tab IN BLOB_TABLE);
TBD
Overload 5
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
cl_tab IN CLOB_TABLE);
TBD
Overload 6
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
bf_tab IN BFILE_TABLE);
TBD
Overload 7
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
n_tab IN NUMBER_TABLE,
index1 IN INTEGER ,
index2 IN INTEGER );
TBD
Overload 8
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
c_tab IN VARCHAR2_TABLE,
index1 IN INTEGER ,
index2 IN INTEGER );
TBD
Overload 9
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
d_tab IN DATE_TABLE,
index1 IN INTEGER ,
index2 IN INTEGER );
TBD
Overload 10
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
bl_tab IN BLOB_TABLE,
index1 IN INTEGER ,
index2 IN INTEGER );
TBD
Overload 11
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
cl_tab IN CLOB_TABLE,
index1 IN INTEGER ,
index2 IN INTEGER );
TBD
Overload 12
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
bf_tab IN BFILE_TABLE,
index1 IN INTEGER ,
index2 IN INTEGER );
TBD
Overload 13
dbms_sql.bind_array(
c IN INTEGER ,
name IN VARCHAR2 ,
ur_tab IN UROWID_TABLE);
TBD
Overload 14
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
ur_tab IN UROWID_TABLE,
index1 IN INTEGER ,
index2 IN INTEGER );
TBD
Overload 15
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
tm_tab IN TIME_TABLE);
TBD
Overload 16
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
tm_tab IN TIME_TABLE,
index1 IN INTEGER ,
index2 IN INTEGER );
TBD
Overload 17
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
tms_tab IN TIMESTAMP_TABLE);
TBD
Overload 18
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
tms_tab IN TIMESTAMP_TABLE
index1 IN INTEGER ,
index2 IN INTEGER );
TBD
Overload 19
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
ttz_tab IN TIME_WITH_TIME_ZONE_TABLE);
TBD
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
Overload 21
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
tstz_tab IN TIMESTAMP_WITH_TIME_ZONE_TABLE);
TBD
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
Overload 23
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
tstz_tab IN TIMESTAMP_WITH_LTZ_TABLE);
TBD
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
Overload 25
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
iym_tab IN INTERVAL_YEAR_TO_MONTH_TABLE);
TBD
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
Overload 27
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
ids_tab IN INTERVAL_DAY_TO_SECOND_TABLE);
TBD
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
Overload 31
dbms_sql.bind_array (
c IN INTEGER ,
name IN VARCHAR2 ,
bdbl_tab IN BINARY_DOUBLE_TABLE);
TBD
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
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
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);
See bind_variable demo
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);
See bind_variable demo
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);
See bind_variable demo
Overload 2
dbms_sql.bind_variable_rowid(
c IN INTEGER ,
name IN VARCHAR2 ,
value IN ROWID,
out_value_size IN INTEGER );
TBD
CLOSE_CURSOR
Closes cursor and free memory
dbms_sql.close_cursor(c IN OUT INTEGER );
See is_open demo
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 );
See final demo
Overload 2
dbms_sql.column_value(
c IN
INTEGER ,
position IN INTEGER ,
value OUT VARCHAR2 CHARACTER SET ANY_CS);
TBD
Overload 3
dbms_sql.column_value(
c IN INTEGER ,
position IN INTEGER ,
value OUT DATE );
TBD
Overload 4
dbms_sql.column_value(
c IN INTEGER ,
position IN INTEGER ,
value OUT BLOB);
TBD
Overload 5
dbms_sql.column_value(
c IN INTEGER ,
position IN INTEGER ,
value OUT CLOB CHARACTER SET ANY_CS);
TBD
Overload 6
dbms_sql.column_value (
c IN INTEGER ,
position IN INTEGER ,
value OUT BFILE);
TBD
Overload
7
dbms_sql.column_value(
c IN
INTEGER ,
position IN INTEGER ,
value OUT NUMBER ,
column_error OUT NUMBER ,
actual_length OUT INTEGER );
TBD
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
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
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
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
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
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
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_bnd 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);
EXCEPTIONS
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(
TBD
Overload
12
dbms_sql.define_array(
TBD
Overload
13
dbms_sql.define_array(
TBD
Overload
14
dbms_sql.define_array(
TBD
Overload
15
dbms_sql.define_array(
TBD
Overload
16
dbms_sql.define_array(
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 final demo
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 );
TBD
Overload 4
dbms_sql.define_column (
c IN INTEGER ,
position IN INTEGER ,
column IN BLOB);
TBD
Overload 5
dbms_sql.define_column (
c IN INTEGER ,
position IN INTEGER ,
column IN CLOB );
TBD
Overload 6
dbms_sql.define_column (
c IN INTEGER ,
position IN INTEGER ,
column IN BFILE);
TBD
Overload 7
dbms_sql.define_column (
c IN INTEGER ,
position IN INTEGER ,
column IN UROWID);
TBD
Overload 8
dbms_sql.define_column(
c IN INTEGER ,
position IN INTEGER ,
column IN TIME_UNCONSTRAINED);
TBD
Overload 9
dbms_sql.define_column(
c IN INTEGER ,
position IN INTEGER ,
column IN TIMESTAMP_UNCONSTRAINED);
TBD
Overload 10
dbms_sql.define_column(
c IN INTEGER ,
position IN INTEGER ,
column IN TIME_TZ_UNCONSTRAINED);
TBD
Overload
11
dbms_sql.define_column(
TBD
Overload
12
dbms_sql.define_column(
TBD
Overload
13
dbms_sql.define_column(
TBD
Overload
14
dbms_sql.define_column(
TBD
Overload
15
dbms_sql.define_column(
TBD
Overload
16
dbms_sql.define_column(
TBD
Overload
17
dbms_sql.define_column(
TBD
Overload
18
dbms_sql.define_column(
TBD
Overload
19
dbms_sql.define_column(
TBD
Overload
20
dbms_sql.define_column(
TBD
Overload
21
dbms_sql.define_column(
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
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
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
DEFINE_COLUMN_ROWID
Undocumented
dbms_sql.define_column_rowid(
c IN INTEGER ,
position IN INTEGER ,
column IN ROWID);
See define_column in final demo
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 ;
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
(new 11g)
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 dynamic SQL 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 ;
Combine demo w/ last_row_count and last_row_id demos
FETCH_ROWS
Fetches a row from a given cursor
dbms_sql.fetch_rows(c IN INTEGER ) RETURN INTEGER ;
See final demo
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 ;
TBD
OPEN_CURSOR
Open dynamic SQL cursor and return cursor ID number of new cursor
Overload 1
dbms_sql.open_cursor RETURN INTEGER ;
See is_open demo
Overload 2
dbms_sql.open_cursor ??? HELP
TBD
PARSE
Parse statement
Overload 1
dbms_sql.parse(
c IN INTEGER ,
statement IN VARCHAR2 ,
language_flag IN INTEGER );
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 2
dbms_sql.parse(
c IN INTEGER ,
statement IN VARCHAR2A,
lb IN INTEGER ,
ub IN INTEGER ,
lfflg IN BOOLEAN ,
language_flag IN INTEGER );
TBD
Overload 3
dbms_sql.parse(
c IN INTEGER ,
statement IN VARCHAR2S,
lb IN INTEGER ,
ub IN INTEGER ,
lfflg IN BOOLEAN ,
language_flag IN INTEGER );
TBD
Overload
4
dbms_sql.parse(
TBD
Overload
5
dbms_sql.parse(
TBD
Overload
6
dbms_sql.parse(
TBD
Overload
7
dbms_sql.parse(
TBD
Overload
8
dbms_sql.parse(
TBD
Overload
9
dbms_sql.parse(
TBD
Overload
10
dbms_sql.parse(
TBD
Overload
11
dbms_sql.parse(
TBD
Overload
12
dbms_sql.parse(
TBD
TO_CURSOR_NUMBER
Takes a REF CURSOR generated by TO_REFCURSOR and returns its handle.
dbms_sql.rc in out
sys_refcursor) RETURN INTEGER ;
TBD
TO_REFCURSOR
Takes a DBMS_SQL OPENed, PARSEd, and EXECUTEd cursor
and transforms/migrates it into a PL/SQL manageable REF CURSOR.
dbms_sql.cursor_number IN
OUT INTEGER ) RETURN SYS_REFCURSOR;
TBD
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 IN NUMBER_TABLE);
TBD
Overload 8
dbms_sql.variable_value(
c IN INTEGER ,
name IN VARCHAR2 ,
value IN VARCHAR2_TABLE);
TBD
Overload 9
dbms_sql.variable_value(
c IN INTEGER ,
name IN VARCHAR2 ,
value IN DATE_TABLE);
TBD
Overload 10
dbms_sql.variable_value(
c IN INTEGER ,
name IN VARCHAR2 ,
value IN BLOB_TABLE);
TBD
Overload 11
dbms_sql.variable_value(
c IN INTEGER ,
name IN VARCHAR2 ,
value IN CLOB_TABLE);
TBD
Overload 12
dbms_sql.variable_value(
c IN INTEGER ,
name IN VARCHAR2 ,
value IN 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_TABLE);
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(
TBD
Overload
21
dbms_sql.variable_value(
TBD
Overload
22
dbms_sql.variable_value(
TBD
Overload
23
dbms_sql.variable_value(
TBD
Overload
24
dbms_sql.variable_value(
TBD
Overload
25
dbms_sql.variable_value(
TBD
Overload
26
dbms_sql.variable_value(
TBD
Overload
27
dbms_sql.variable_value(
TBD
Overload
28
dbms_sql.variable_value(
TBD
Overload
29
dbms_sql.variable_value(
TBD
Overload
30
dbms_sql.variable_value(
TBD
Overload
31
dbms_sql.variable_value(
TBD
Overload
32
dbms_sql.variable_value(
TBD
Overload
33
dbms_sql.variable_value(
TBD
Overload
34
dbms_sql.variable_value(
TBD
Overload
35
dbms_sql.variable_value(
TBD
Overload
36
dbms_sql.variable_value(
TBD
Overload
37
dbms_sql.variable_value(
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);
TBD
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
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
SQL> commit;
SQL> ed
-- delete "commit" and paste in colored section
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
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
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;