| General Information |
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 |
| Data Type Constants |
| Name |
Data Type |
Value |
| Varchar2_Type |
PLS_INTEGER |
1 |
| Number_Type |
PLS_INTEGER |
2 |
| Long_Type |
PLS_INTEGER |
8 |
| Rowid_Type |
PLS_INTEGER |
11 |
| Date_Type |
PLS_INTEGER |
12 |
| Raw_Type |
PLS_INTEGER |
23 |
| Long_Raw_Type |
PLS_INTEGER |
24 |
| Char_Type |
PLS_INTEGER |
96 |
| Binary_Float_Type |
PLS_INTEGER |
100 |
| Binary_Double_Type |
PLS_INTEGER |
101 |
| MLSLabel_Type |
PLS_INTEGER |
106 |
| User_Defined_Type |
PLS_INTEGER |
109 |
| Ref_Type |
PLS_INTEGER |
111 |
| Clob_Type |
PLS_INTEGER |
1112 |
| 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 |
|
| 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(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'; |
| 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 |
-- --------------
-- |
| Security Model |
Execute is granted to PUBLIC |
| |
| 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_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 |
| Overload 15 |
dbms_sql.define_array(
c IN INTEGER,
position IN INTEGER,
bflt_tab IN BINARY_FLOAT_TABLE,
cnt IN INTEGER,
lower_bound IN INTEGER); |
| TBD |
| Overload 16 |
dbms_sql.define_array(
c IN INTEGER,
position IN INTEGER,
bdbl_tab IN BINARY_DOUBLE_TABLE,
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 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(
c IN INTEGER,
position IN INTEGER,
column IN TIMESTAMP_TZ_UNCONSTRAINED); |
| TBD |
| Overload 12 |
dbms_sql.define_column(
c IN INTEGER,
position IN INTEGER,
column in TIMESTAMP_LTZ_UNCONSTRAINED); |
| TBD |
| Overload 13 |
dbms_sql.define_column(
c IN INTEGER,
position IN INTEGER,
column IN YMINTERVAL_UNCONSTRAINED); |
| TBD |
| Overload 14 |
dbms_sql.define_column(
c IN INTEGER,
position IN INTEGER,
column IN DSINTERVAL_UNCONSTRAINED); |
| TBD |
| Overload 15 |
dbms_sql.define_column(
c IN INTEGER,
position IN INTEGER,
column IN BINARY_FLOAT); |
| TBD |
| Overload 16 |
dbms_sql.define_column(
c IN INTEGER,
position IN INTEGER,
column IN BINARY_DOUBLE); |
| TBD |
| 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 |
| |
| 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; -- 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 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(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;
/ |
| |
| 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(c IN INTEGER, statement IN CLOB, language_flag IN INTEGER); |
| TBD |
| Overload 5 |
dbms_sql.parse(
c IN INTEGER,
statement IN VARCHAR2,
language_flag IN INTEGER,
edition IN VARCHAR2); |
| TBD |
| Overload 6 |
dbms_sql.parse(
c IN INTEGER,
statement IN VARCHAR2,
language_flag IN INTEGER,
edition IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE); |
DECLARE
c NUMBER := dbms_sql.open_cursor;
stmnt VARCHAR2(1024) := 'update t set rid = rid+1'; -- the DML statement
act VARCHAR2('contacts_fwd_xed'); -- the trigger name
x NUMBER;
BEGIN
dbms_sql.parse(c, stmnt, dbms_sql.native, 'DEMO_ED', act, TRUE);
x := dbms_sql.execute(c);
commit;
dbms_sql.close(c);
END;
/ |
| Overload 7 |
dbms_sql.parse(
c IN INTEGER,
statement IN CLOB,
language_flag IN INTEGER,
edition IN VARCHAR2); |
| TBD |
| Overload 8 |
dbms_sql.parse(
c IN INTEGER,
statement IN CLOB,
language_flag IN INTEGER,
edition IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE); |
| TBD |
| Overload 9 |
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); |
| TBD |
| Overload 10 |
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,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE); |
| TBD |
| Overload 11 |
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); |
| TBD |
| Overload 12 |
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,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE); |
| 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 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 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_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 |
| Overload 32 |
dbms_sql.variable_value(
c IN INTEGER,
name IN VARCHAR2,
value OUT NOCOPY BINARY_DOUBLE_TABLE); |
| TBD |
| Overload 33 |
dbms_sql.variable_value(c IN INTEGER, name IN VARCHAR2, value OUT "<ADT_1>"); |
| TBD |
| Overload 34 |
dbms_sql.variable_value(c IN INTEGER, name IN VARCHAR2, value OUT REF "<ADT_1>"); |
| TBD |
| Overload 35 |
dbms_sql.variable_value(c IN INTEGER, name IN VARCHAR2, value OUT "<TABLE_1>"); |
| TBD |
| Overload 36 |
dbms_sql.variable_value(c IN INTEGER, name IN VARCHAR2, value OUT "<VARRAY_1>"); |
| TBD |
| Overload 37 |
dbms_sql.variable_value(c IN INTEGER, name IN VARCHAR2, value OUT "<OPAQUE_1>"); |
| TBD |
| |
| VARIABLE_VALUE_CHAR |
| Undocumented |
dbms_sql.variable_value_char(
c IN INTEGER,
name IN VARCHAR2,
value OUT CHAR CHARACTER SET ANY_CS); |
| TBD |
| |
| VARIABLE_VALUE_RAW |
| Undocumented |
dbms_sql.variable_value_raw(
c IN INTEGER,
name IN VARCHAR2,
value OUT RAW); |
| TBD |
| |
| VARIABLE_VALUE_ROWID |
| Undocumented |
dbms_sql.variable_value_rowid(
c IN INTEGER,
name IN VARCHAR2,
value OUT ROWID); |
set serveroutput on
DECLARE
c INTEGER;
rid VARCHAR2(20);
retval ROWID;
BEGIN
SELECT rowid
INTO rid
FROM servers
WHERE rownum = 1;
dbms_output.put_line(rid);
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'DELETE FROM servers WHERE rowid = :x',
dbms_sql.NATIVE);
dbms_sql.bind_variable_rowid(c, ':x', rid);
dbms_sql.variable_value_rowid(c, ':x', retval);
dbms_sql.close_cursor(c);
dbms_output.put_line(retval);
END;
/ |
| |
| Demos |
| Drop Synonym Demo |
SELECT synonym_name
FROM user_synonyms;
CREATE SYNONYM d FOR dept;
CREATE SYNONYM e FOR emp;
SELECT synonym_name
FROM user_synonyms;
DECLARE
CURSOR syn_cur IS
SELECT synonym_name
FROM user_synonyms;
RetVal NUMBER;
sqlstr VARCHAR2(200);
tCursor PLS_INTEGER;
BEGIN
FOR syn_rec IN syn_cur
LOOP
sqlstr := 'DROP SYNONYM ' || syn_rec.synonym_name;
tCursor := dbms_sql.open_cursor;
dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
RetVal := dbms_sql.execute(tCursor);
dbms_sql.close_cursor(tCursor);
END LOOP;
END dropsyn;
/ |
SELECT synonym_name
FROM user_synonyms; |
| Executing CLOBS Demo Tables |
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 |
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; |
|
