ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Create and execute dynamic SQL statements within PL/SQL
Note
DBMS_SQL is the traditional form of dynamic SQL in Oracle.
For most purposes native dynamic sql (NDS) will be the dynamic SQL engine of choice but there are some things this package can do that can not be done any other way or certainly not with equal ease.
This page emphasizes some of those areas where there is no substitute.
AUTHID
CURRENT_USER
Data Type Constants
Please note that Binary_Bouble_Type, as humorous as it may be, has been
intentionally retained by Oracle for purposes of backward compatibility.
Inconsistent types: Raised by procedure "column_value" or "variable_value" if the type of out argument where to put the requested value is different from the type of the value
First Available
7.3.4
Flow Diagram
Security Model
Owned by SYS with EXECUTE granted to AUDSYS, DVSYS,
ORACLE_OCM, ORDSYS, and PUBLIC
Clearly no one at Oracle pays sufficient intention to database security.
The grant of EXECUTE to PUBLIC should be dropped.
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN NUMBER);
conn scott/tiger@pdbdev
SELECT COUNT(*) FROM emp;
CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS
cursor_name INTEGER;
rows_processed INTEGER;
BEGIN
cursor_name := dbms_sql.open_cursor;
dbms_sql.parse(cursor_name, 'DELETE FROM emp WHERE sal > :x', dbms_sql.NATIVE);
dbms_sql.bind_variable(cursor_name, ':x', salary);
rows_processed := dbms_sql.execute(cursor_name);
dbms_sql.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(cursor_name);
END demo;
/
exec demo(30001)
SELECT COUNT(*) FROM emp;
ROLLBACK;
Overload 2
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN VARCHAR2 CHARACTER SET ANY_CS);
TBD
Overload 3
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN VARCHAR2 CHARACTER SET ANY_CS,
out_value_size IN INTEGER);
TBD
Overload 4
dbms_sql.bind_variable (
c IN INTEGER,
name IN VARCHAR2,
value IN DATE);
TBD
Overload 5
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN BLOB);
TBD
Overload 6
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN CLOB CHARACTER SET ANY_CS);
TBD
Overload 7
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN BFILE);
TBD
Overload 8
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN UROWID);
TBD
Overload 9
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN TIME_UNCONSTRAINED);
TBD
Overload 10
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN TIMESTAMP_UNCONSTRAINED);
TBD
Overload 11
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN TIME_TZ_UNCONSTRAINED);
TBD
Overload 12
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN TIMESTAMP_TZ_UNCONSTRAINED);
TBD
Overload 13
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN TIMESTAMP_LTZ_UNCONSTRAINED);
TBD
Overload 14
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN YMINTERVAL_UNCONSTRAINED);
TBD
Overload 15
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN DSINTERVAL_UNCONSTRAINED);
TBD
Binds a variable of BINARY_FLOAT type
Overload 16
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN BINARY_FLOAT);
TBD
Binds a variable of BINARY_DOUBLE
type
Overload 17
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN BINARY_DOUBLE);
TBD
Overload 18
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN "<ADT_1>");
TBD
Overload 19
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN REF "<ADT_1>");
TBD
Overload 20
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN "<TABLE_1>");
TBD
Overload 21
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN "<VARRAY_1>");
TBD
Overload 22
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN "<OPAQUE_1>");
TBD
Binds a variable of Boolean type
Overload 23
dbms_sql.bind_variable(
c IN INTEGER,
name IN VARCHAR2,
value IN BOOLEAN);
Returns value of the cursor element for a given position in a cursor
Overload 1
dbms_sql.column_value
c IN INTEGER,
position IN INTEGER,
value OUT NUMBER);
TBD
VARCHAR2 Support
Overload 2
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
value OUT VARCHAR2 CHARACTER SET ANY_CS);
TBD
DATE Support
Overload 3
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
value OUT DATE);
TBD
BLOB Support
Overload 4
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
value OUT BLOB);
TBD
CLOB Support
Overload 5
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
value OUT CLOB CHARACTER SET ANY_CS);
TBD
BFile Support
Overload 6
dbms_sql.column_value (
c IN INTEGER,
position IN INTEGER,
value OUT BFILE);
TBD
Number Support
Overload 7
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
value OUT NUMBER,
column_error OUT NUMBER,
actual_length OUT INTEGER);
TBD
VARCHAR2 Support
Overload 8
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
value OUT VARCHAR2 CHARACTER SET ANY_CS,
column_error OUT NUMBER,
actual_length OUT INTEGER);
TBD
DATE Support
Overload 9
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
value OUT DATE,
column_error OUT NUMBER,
actual_length OUT INTEGER);
TBD
Overload 10
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
n_tab IN OUT NOCOPY NUMBER_TABLE);
TBD
Overload 11
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
c_tab IN OUT NOCOPY VARCHAR2_TABLE);
TBD
Overload 12
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
d_tab IN OUT NOCOPY DATE_TABLE);
TBD
Overload 13
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
bl_tab IN OUT NOCOPY BLOB_TABLE);
TBD
Overload 14
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
cl_tab IN OUT NOCOPY CLOB_TABLE);
TBD
Overload 15
dbms_sql.column_value (
c IN INTEGER,
position IN INTEGER,
bf_tab IN OUT NOCOPY BFILE_TABLE);
TBD
Overload 16
dbms_sql.column_value (
c IN INTEGER,
position IN INTEGER,
value OUT UROWID);
TBD
Overload 17
dbms_sql.column_value (
c IN INTEGER,
position IN INTEGER,
ur_tab IN OUT NOCOPY UROWID_TABLE);
TBD
Overload 18
dbms_sql.column_value (
c IN INTEGER,
position IN INTEGER,
value OUT TIME_UNCONSTRAINED);
TBD
Overload 19
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
tm_tab IN OUT NOCOPY TIME_TABLE);
TBD
Overload 20
dbms_sql.column_value (
c IN INTEGER,
position IN INTEGER,
value OUT TIMESTAMP_UNCONSTRAINED);
TBD
Overload 21
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
tms_tab IN OUT NOCOPY TIMESTAMP_TABLE);
TBD
Overload 22
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
value OUT TIME_TZ_UNCONSTRAINED);
TBD
Overload 23
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
ttz_tab IN OUT NOCOPY TIME_WITH_TIME_ZONE_TABLE);
TBD
Overload 24
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
value OUT TIMESTAMP_TZ_UNCONSTRAINED);
TBD
Overload 25
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
tstz_tab IN OUT NOCOPY TIMESTAMP_WITH_TIME_ZONE_TABLE);
TBD
Overload 26
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
value OUT TIMESTAMP_LTZ_UNCONSTRAINED);
TBD
Overload 27
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
tstz_tab IN OUT NOCOPY TIMESTAMP_WITH_LTZ_TABLE);
TBD
Overload 28
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
value OUT YMINTERVAL_UNCONSTRAINED);
TBD
Overload 29
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
iym_tab IN OUT NOCOPY INTERVAL_YEAR_TO_MONTH_TABLE);
TBD
Overload 30
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
value OUT DSINTERVAL_UNCONSTRAINED);
TBD
Overload 31
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
ids_tab IN OUT NOCOPY INTERVAL_DAY_TO_SECOND_TABLE);
TBD
Overload 32
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
value OUT BINARY_FLOAT);
TBD
Overload 33
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
bflt_tab IN OUT NOCOPY BINARY_FLOAT_TABLE);
TBD
Overload 34
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
value OUT BINARY_DOUBLE);
TBD
Overload 35
dbms_sql.column_value(
c IN INTEGER,
position IN INTEGER,
bdbl_tab IN OUT NOCOPY BINARY_DOUBLE_TABLE);
TBD
Overload 36
dbms_sql.column_value(
c IN INTEGER,
position IN BINARY_INTEGER,
value OUT "<ADT_1>");
TBD
Overload 37
dbms_sql.column_value(
c IN INTEGER,
position IN BINARY_INTEGER,
value OUT REF "<ADT_1>");
TBD
Overload 38
dbms_sql.column_value(
c IN INTEGER,
position IN BINARY_INTEGER,
value OUT "<TABLE_1>");
TBD
Overload 39
dbms_sql.column_value(
c IN INTEGER,
position IN BINARY_INTEGER,
value OUT "<VARRAY_1>");
TBD
Overload 40
dbms_sql.column_value(
c IN INTEGER,
position IN BINARY_INTEGER,
value OUT "<OPAQUE_1>");
TBD
Overload 41
dbms_sql.column_value(
c IN INTEGER,
position IN BINARY_INTEGER,
c_tab IN OUT dbms_sql.varchar2a);
Returns value of the cursor element for a given position in a cursor
Overload 1
dbms_sql.column_value_char(
c IN INTEGER,
position IN INTEGER,
value OUT CHAR CHARACTER SET ANY_CS);
See COLUMN_VALUE In "Final" Demo At Page Bottom
Overload 2
dbms_sql.column_value_char(
c IN INTEGER,
position IN INTEGER,
value OUT CHAR CHARACTER SET ANY_CS,
column_error OUT NUMBER,
actual_length OUT INTEGER);
Defines a collection to be selected from the given cursor, used only with SELECT statements
Overload 1
dbms_sql.define_array(
c IN INTEGER,
position IN INTEGER,
n_tab IN NUMBER_TABLE,
cnt IN INTEGER,
lower_bound IN INTEGER);
DECLARE
c NUMBER;
d NUMBER;
n_tab dbms_sql.number_table;
indx NUMBER := -10;
BEGIN
c := dbms_sql.open_cursor;
dbms_sql.parse(c,'select n from t order by 1',dbms_sql.NATIVE);
dbms_sql.define_array(c, 1, n_tab, 10, indx);
d := dbms_sql.execute(c);
LOOP
d := dbms_sql.fetch_rows(c);
dbms_sql.column_value(c, 1, n_tab);
exit when d != 10;
END LOOP;
dbms_sql.close_cursor(c);
EXCEPTION
WHEN OTHERS THEN
IF dbms_sql.is_open(c) THEN
dbms_sql.close_cursor(c);
END IF;
RAISE;
END;
/
Overload 2
dbms_sql.define_array (
c IN INTEGER,
position IN INTEGER,
c_tab IN VARCHAR2_TABLE,
cnt IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 3
dbms_sql.define_array (
c IN INTEGER,
position IN INTEGER,
d_tab IN DATE_TABLE,
cnt IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 4
dbms_sql.define_array (
c IN INTEGER,
position IN INTEGER,
bl_tab IN BLOB_TABLE,
cnt IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 5
dbms_sql.define_array (
c IN INTEGER,
position IN INTEGER,
cl_tab IN CLOB_TABLE,
cnt IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 6
dbms_sql.define_array (
c IN INTEGER,
position IN INTEGER,
bf_tab IN BFILE_TABLE,
cnt IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 7
dbms_sql.define_array (
c IN INTEGER,
position IN INTEGER,
ur_tab IN UROWID_TABLE,
cnt IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 8
dbms_sql.define_array(
c IN INTEGER,
position IN INTEGER,
tm_tab IN TIME_TABLE,
cnt IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 9
dbms_sql.define_array(
c IN INTEGER,
position IN INTEGER,
tms_tab IN TIMESTAMP_TABLE,
cnt IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 10
dbms_sql.define_array(
c IN INTEGER,
position IN INTEGER,
ttz_tab IN TIME_WITH_TIME_ZONE_TABLE,
cnt IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 11
dbms_sql.define_array(
c IN INTEGER,
position IN INTEGER,
tstz_tab IN TIMESTAMP_WITH_TIME_ZONE_TABLE,
cnt IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 12
dbms_sql.define_array(
c IN INTEGER,
position IN INTEGER,
tstz_tab IN TIMESTAMP_WITH_LTZ_TABLE,
cnt IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 13
dbms_sql.define_array(
c IN INTEGER,
position IN INTEGER,
iym_tab IN INTERVAL_YEAR_TO_MONTH_TABLE,
cnt IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 14
dbms_sql.define_array(
c IN INTEGER,
position IN INTEGER,
ids_tab IN INTERVAL_DAY_TO_SECOND_TABLE,
cnt IN INTEGER,
lower_bound IN INTEGER);
TBD
Binary Float Support
Overload 15
dbms_sql.define_array(
c IN INTEGER,
position IN INTEGER,
bflt_tab IN dbms_sql.binary_float_table,
cnt IN INTEGER,
lower_bound IN INTEGER);
TBD
Binary Double Support
Overload 16
dbms_sql.define_array(
c IN INTEGER,
position IN INTEGER,
bdbl_tab IN dbms_sql.binary_double_table,
cnt IN INTEGER,
lower_bound IN INTEGER);
TBD
32K Support
Overload 17
dbms_sql.define_array(
c IN INTEGER
position IN INTEGER,
c_tab IN dbms_sql.varchar2a,
cnt IN INTEGER,
lower_bound IN INTEGER);
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);
Gets the statement of the next result returned to the caller of the recursive statement or, if this caller sets itself as the client for the recursive statement, the next result returned to this caller as client.
Overload 1
dbms_sql.get_next_result(c IN INTEGER, rc OUT SYS_REFCURSOR);
TBD
Overload 2
dbms_sql.get_next_result(
c IN INTEGER,
rc OUT INTEGER);
pragma restrict_references(get_next_result,RNDS,WNDS);
Open dynamic SQL cursor and return cursor ID number of new cursor
Overload 1
dbms_sql.open_cursor RETURN INTEGER;
See the IS_OPEN Demo Above
Overload 2
dbms_sql.open_cursor(security_level IN INTEGER) RETURN INTEGER;
Level 0 allows all DBMS_SQL operations on the cursor without any security checks. The cursor may be fetched from, and even re-bound and re-executed, by code running with a different effective userid or roles than those in effect at the time the cursor was parsed. This level of security is off by default.
Level 1 requires that the effective userid and roles of the caller to DBMS_SQL for bind and execute operations on this cursor must be the same as those of the caller of the most recent parse operation on this cursor.
Level 2 requires that the effective userid and roles of the caller to DBMS_SQL for all bind, execute, define, describe, and fetch operations on this cursor must be the same as those of the caller of the most recent parse operation on this cursor.
dbms_sql.parse(
c IN NUMBER,
statement IN VARCHAR2,
language_flag IN NUMBER);
TBD
Parse PL/SQL table containing a SQL statement
Overload 2
dbms_sql.parse(
c IN NUMBER,
statement IN dbms_sql.varchar2a,
lb IN NUMBER,
ub IN NUMBER,
lfflg IN BOOLEAN,
language_flag IN NUMBER);
TBD
Parse PL/SQL table containing a SQL statement
Overload 3
dbms_sql.parse(
c IN NUMBER,
statement IN dbms_sql.varchar2s,
lb IN NUMBER,
ub IN NUMBER,
lfflg IN BOOLEAN,
language_flag IN NUMBER);
TBD
Parse statement in CLOB
Overload 4
dbms_sql.parse(
c IN NUMBER,
statement IN CLOB,
language_flag IN NUMBER);
TBD
Parse statement in VARCHAR2 naming the edition
Overload 5
dbms_sql.parse(
c IN NUMBER,
statement IN VARCHAR2,
language_flag IN NUMBER,
edition IN VARCHAR2);
TBD
Parse statement in VARCHAR2 naming the edition and specify cross-edition trigger behavior
Overload 6
dbms_sql.parse(
c IN NUMBER,
statement IN VARCHAR2,
language_flag IN NUMBER,
edition IN VARCHAR2 DEFAULT NULL,
apply_cross_edition_trigger IN VARCHAR2,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE);
TBD
Parse statement in CLOB naming the edition
Overload 7
dbms_sql.parse(
c IN NUMBER,
statement IN CLOB,
language_flag IN NUMBER,
edition IN VARCHAR2);
TBD
Parse statement in CLOB naming the edition and specify cross-edition trigger behavior
Overload 8
dbms_sql.parse(
c IN NUMBER,
statement IN CLOB,
language_flag IN NUMBER,
edition IN VARCHAR2 DEFAULT NULL,
apply_cross_edition_trigger IN VARCHAR2,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE);
TBD
Parse PL/SQL table containing a SQL statement
Overload 9
dbms_sql.parse(
c IN NUMBER,
statement IN dbms_sql.varchar2a,
lb IN NUMBER,
ub IN NUMBER,
lfflg IN BOOLEAN,
language_flag IN NUMBER,
edition IN VARCHAR2);
TBD
Parse PL/SQL table containing a SQL statement naming the edition and specify cross-edition trigger behavior
Overload 10
dbms_sql.parse(
c IN NUMBER,
statement IN dbms_sql.varchar2a,
lb IN NUMBER,
ub IN NUMBER,
lfflg IN BOOLEAN,
language_flag IN NUMBER,
edition IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE);
TBD
Parse PL/SQL table containing a SQL statement
Overload 11
dbms_sql.parse(
c IN NUMBER,
statement IN dbms_sql.varchar2s,
lb IN NUMBER,
ub IN NUMBER,
lfflg IN BOOLEAN,
language_flag IN NUMBER,
edition IN VARCHAR2);
TBD
Parse PL/SQL table containing a SQL statement naming the edition and specify cross-edition trigger behavior
Overload 12
dbms_sql.parse(
c IN NUMBER,
statement IN dbms_sql.varchar2s,
lb IN NUMBER,
ub IN NUMBER,
lfflg IN BOOLEAN,
language_flag IN NUMBER,
edition IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE);
TBD
Overload 13
dbms_sql.parse(
c IN INTEGER,
statement IN VARCHAR2,
language_flag IN INTEGER,
edition IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE,
schema IN VARCHAR2);
dbms_sql.variable_value(c, 'bnd3', r); -- get value of outbind
dbms_sql.close_cursor(c);
END single_row_insert;
/
Overload 14
dbms_sql.parse(
c IN INTEGER,
statement IN CLOB,
language_flag IN INTEGER,
edition IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE,
schema IN VARCHAR2);
TBD
Overload 15
dbms_sql.parse(
c IN INTEGER,
statement IN VARCHAR2a,
lb IN INTEGER,
ub IN INTEGER,
lfflg IN BOOLEAN,
language_flag IN INTEGER,
edition IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE,
schema IN VARCHAR2);
TBD
Overload 16
dbms_sql.parse(
c IN INTEGER,
statement IN VARCHAR2s,
lb IN INTEGER,
ub IN INTEGER,
lfflg IN BOOLEAN,
language_flag IN INTEGER,
edition IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE,
schema IN VARCHAR2);
TBD
Overload 17
dbms_sql.parse(
c IN NUMBER,
statement IN VARCHAR2,
language_flag IN NUMBER,
edition IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE,
schema IN VARCHAR2 DEFAULT NULL,
container IN VARCHAR2);
TBD
Overload 18
dbms_sql.parse(
c IN NUMBER,
statement IN CLOB,
language_flag IN NUMBER,
edition IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE,
schema IN VARCHAR2 DEFAULT NULL,
container IN VARCHAR2);
TBD
Overload 19
dbms_sql.parse(
c IN NUMBER,
statement IN dbms_sql.varchar2a,
lb IN NUMBER,
ub IN NUMBER,
lfflg IN BOOLEAN,
language_flag IN NUMBER,
edition IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE,
schema IN VARCHAR2 DEFAULT NULL,
container IN VARCHAR2);
TBD
Overload 20
dbms_sql.parse(
c IN NUMBER,
statement IN dbms_sql.varchar2s,
lb IN NUMBER,
ub IN NUMBER,
lfflg IN BOOLEAN,
language_flag IN NUMBER,
edition IN VARCHAR2 DEFAULT NULL,
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE,
schema IN VARCHAR2 DEFAULT NULL,
container IN VARCHAR2);
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);
---------------------------
-- 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;
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;
/
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;