| General |
Note:
DMBS_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 |
| Name |
Data Type |
Value |
| v6 |
INTEGER |
0 |
| native |
INTEGER |
1 |
| v7 |
INTEGER |
2 |
|
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); |