Oracle DBMS_SYS_SQL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Internal package supporting and extending the functionality of the DBMS_SQL package.

Oracle code using this package can be found in {ORACLE_HOME}/rdbms/admin/wpgdocb.sql
AUTHID CURRENT_USER
Dependencies (updated to 12.2.0.1)
DBMS_AQADM_SYS DBMS_PARALLEL_EXECUTE_INTERNAL DBMS_STATS
DBMS_AUDIT_MGMT DBMS_PRVTAQIM DBMS_STATS_INTERNAL
DBMS_DDL DBMS_PRVTAQIP DBMS_STREAMS_ADM_UTL
DBMS_DST DBMS_RECO_SCRIPT_INVOK EXECASUSER
DBMS_EXPORT_EXTENSION DBMS_REDEFINITION KUPD$DATA
DBMS_FILE_GROUP_UTL DBMS_REDEFINITION_INTERNAL LOGMNR_DICT_CACHE
DBMS_GSM_UTILITY DBMS_RESOURCE_MANAGER PRVT_ILM
DBMS_IREFRESH DBMS_SNAPSHOT_UTL SYS_STUB_FOR_PURITY_ANALYSIS
DBMS_I_INDEX_UTL DBMS_SNAP_INTERNAL UTL_IDENT
DBMS_LOGMNR_LOGREP_DICT DBMS_SQL XS_DATA_SECURITY_UTIL_INT
DBMS_LOGREP_UTIL DBMS_SQL_TRANSLATOR_EXPORT XS_DIAG_INT
DBMS_ODCI DBMS_STANDARD  
Documented No
First Available Not known but 2008 or earlier
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvthssq.plb
Subprograms
 
BIND_ARRAY
Binds a given value to a given collection

Overload 1
dbms_sys_sql.bind_array(
c IN NUMBER, name IN VARCHAR2,
n_tab             IN dbms_sql.number_table);
conn / as sysdba

CREATE TABLE scott.empbak AS
SELECT * FROM scott.emp;

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 scott.empbak where deptno = :dept_array';
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, stmt, dbms_sql.NATIVE);
  dbms_sys_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;
/

DROP TABLE scott.empbak;
Overload 2 dbms_sys_sql.bind_array(
c     IN NUMBER,
name  IN VARCHAR2,
c_tab IN dbms_sql.varchar2_table);
TBD
Overload 3 dbms_sys_sql.bind_array(
c     IN NUMBER,
name  IN VARCHAR2,
d_tab IN dbms_sql.date_table);
TBD
Overload 4 dbms_sys_sql.bind_array(
c      IN NUMBER,
name   IN VARCHAR2,
bl_tab IN dbms_sql.blob_table);
TBD
Overload 5 dbms_sys_sql.bind_array(
c      IN NUMBER,
name   IN VARCHAR2,
cl_tab IN dbms_sql.clob_table);
TBD
Overload 6 dbms_sys_sql.bind_array(
c      IN NUMBER,
name   IN VARCHAR2,
bf_tab IN dbms_sql.bfile_table);
TBD
Overload 7 dbms_sys_sql.bind_array(
c      IN NUMBER,
name   IN VARCHAR2,
n_tab  IN dbms_sql.number_table,
index1 IN NUMBER,
index2 IN NUMBER);
TBD
Overload 8 dbms_sys_sql.bind_array(
c      IN NUMBER,
name   IN VARCHAR2,
c_tab  IN dbms_sql.varchar2_table,
index1 IN NUMBER,
index2 IN NUMBER);
TBD
Overload 9 dbms_sys_sql.bind_array(
c      IN NUMBER,
name   IN VARCHAR2,
d_tab  IN dbms_sql.date_table,
index1 IN NUMBER,
index2 IN NUMBER);
TBD
Overload 10 dbms_sys_sql.bind_array(
c      IN NUMBER,
name   IN VARCHAR2,
bl_tab IN dbms_sql.blob_table,
index1 IN NUMBER,
index2 IN NUMBER);
TBD
Overload 11 dbms_sys_sql.bind_array(
c      IN NUMBER,
name   IN VARCHAR2,
cl_tab IN dbms_sql.clob_table,
index1 IN NUMBER,
index2 IN NUMBER);
TBD
Overload 12 dbms_sys_sql.bind_array(
c      IN NUMBER,
name   IN VARCHAR2,
bf_tab IN dbms_sql.bfile_table,
index1 IN NUMBER,
index2 IN NUMBER);
TBD
Overload 13 dbms_sys_sql.bind_array(
c      IN NUMBER,
name   IN VARCHAR2,
ur_tab IN dbms_sql.urowid_table);
TBD
Overload 14 dbms_sys_sql.bind_array(
c      IN NUMBER,
name   IN VARCHAR2,
ur_tab IN dbms_sql.ur_table,
index1 IN NUMBER,
index2 IN NUMBER);
TBD
Overload 15 dbms_sys_sql.bind_array(
c      IN NUMBER,
name   IN VARCHAR2,
tm_tab IN dbms_sql.time_table);
TBD
Overload 16 dbms_sys_sql.bind_array(
c      IN NUMBER,
name   IN VARCHAR2,
tm_tab IN dbms_sql.time_table,
index1 IN NUMBER,
index2 IN NUMBER);
TBD
Overload 17 dbms_sys_sql.bind_array(
c       IN NUMBER,
name    IN VARCHAR2,
tms_tab IN dbms_sql.time_table);
TBD
Overload 18 dbms_sys_sql.bind_array(
c       IN NUMBER,
name    IN VARCHAR2,
tms_tab IN dbms_sql.time_table,
index1  IN NUMBER,
index2  IN NUMBER);
TBD
Overload 19 dbms_sys_sql.bind_array(
c       IN NUMBER,
name    IN VARCHAR2,
ttz_tab IN dbms_sql.time_with_time_zone_table);
TBD
Overload 20 dbms_sys_sql.bind_array(
c       IN NUMBER,
name    IN VARCHAR2,
ttz_tab IN dbms_sql.time_with_time_zone_table,
index1  IN NUMBER,
index2  IN NUMBER);
TBD
Overload 21 dbms_sys_sql.bind_array(
c        IN NUMBER,
name     IN VARCHAR2,
tstz_tab IN dbms_sql.timestamp_with_time_zone_table);
TBD
Overload 22 dbms_sys_sql.bind_array(
c        IN NUMBER,
name     IN VARCHAR2,
tstz_tab IN dbms_sql.timestamp_with_time_zone_table,
index1   IN NUMBER,
index2   IN NUMBER);
TBD
Overload 23 dbms_sys_sql.bind_array(
c        IN NUMBER,
name     IN VARCHAR2,
tstz_tab IN dbms_sql.timestamp_with_ltz_table);
TBD
Overload 24 dbms_sys_sql.bind_array(
c        IN NUMBER,
name     IN VARCHAR2,
tstz_tab IN dbms_sql.timestamp_with_ltz_table,
index1   IN NUMBER,
index2   IN NUMBER);
TBD
Overload 25 dbms_sys_sql.bind_array(
c       IN NUMBER,
name    IN VARCHAR2,
iym_tab IN dbms_sql.interval_year_to_month_table);
TBD
Overload 26 dbms_sys_sql.bind_array(
c       IN NUMBER,
name    IN VARCHAR2,
iym_tab IN dbms_sql.interval_year_to_month_table,
index1  IN NUMBER,
index2  IN NUMBER);
TBD
Overload 27 dbms_sys_sql.bind_array(
c       IN NUMBER,
name    IN VARCHAR2,
ids_tab IN dbms_sql.interval_day_to_second_table);
TBD
Overload 28 dbms_sys_sql.bind_array(
c       IN NUMBER,
name    IN VARCHAR2,
ids_tab IN dbms_sql.interval_day_to_second_table,
index1  IN NUMBER,
index2  IN NUMBER);
TBD
Overload 29 dbms_sys_sql.bind_array(
c        IN NUMBER,
name     IN VARCHAR2,
bflt_tab IN dbms_sql.binary_float_table);
TBD
Overload 30 dbms_sys_sql.bind_array(
c        IN NUMBER,
name     IN VARCHAR2,
bflt_tab IN dbms_sql.binary_float_table,
index1   IN NUMBER,
index2   IN NUMBER);
TBD
Overload 31 dbms_sys_sql.bind_array(
c        IN NUMBER,
name     IN VARCHAR2,
bdbl_tab IN dbms_sql.binary_double_table);
TBD
Overload 32 dbms_sys_sql.bind_array(
c        IN NUMBER,
name     IN VARCHAR2,
bdbl_tab IN dbms_sql.binary_double_table,
index1   IN NUMBER,
index2   IN NUMBER);
TBD
Overload 33 dbms_sys_sql.bind_array(
c     IN NUMBER,
name  IN VARCHAR2,
c_tab IN dbms_sql.varchar2a);
TBD
Overload 34 dbms_sys_sql.bind_array(
c      IN NUMBER,
name   IN VARCHAR2,
c_tab  IN dbms_sql.varchar2a,
index1 IN NUMBER,
index2 IN NUMBER);
TBD
 
BIND_VARIABLE (new 12.2 overload 24)
Binds a given value to a given variable

Overload 1
dbms_sys_sql.bind_variable(
c     IN NUMBER,
name  IN VARCHAR2,
value IN NUMBER);
conn / as sysdba

SELECT COUNT(*) FROM scott.emp;

CREATE OR REPLACE PROCEDURE demo(pSalary IN NUMBER) AUTHID DEFINER AS
 cursor_name    INTEGER;
 rows_processed INTEGER;
BEGIN
  cursor_name := dbms_sql.open_cursor;
  dbms_sql.parse(cursor_name, 'DELETE FROM scott.emp WHERE sal > :x', dbms_sql.NATIVE);
  dbms_sys_sql.bind_variable(cursor_name, ':x', pSalary);
  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(3000);

SELECT COUNT(*) FROM scott.emp;

ROLLBACK;
Overload 2 dbms_sys_sql.bind_variable(
c     IN NUMBER,
name  IN VARCHAR2,
value IN VARCHAR2);
TBD
Overload 3 dbms_sys_sql.bind_variable(
c              IN NUMBER,
name           IN VARCHAR2,
value          IN VARCHAR2,
out_value_size IN NUMBER);
TBD
Overload 4 dbms_sys_sql.bind_variable(c IN NUMBER, name IN VARCHAR2, value IN DATE);
TBD
Overload 5 dbms_sys_sql.bind_variable(c IN NUMBER, name IN VARCHAR2);
TBD
Overload 6 dbms_sys_sql.bind_variable(c IN NUMBER, name IN VARCHAR2, value IN BLOB);
TBD
Overload 7 dbms_sys_sql.bind_variable(c IN NUMBER, name IN VARCHAR2, value IN CLOB);
TBD
Overload 8 dbms_sys_sql.bind_variable(c IN NUMBER, name IN VARCHAR2, value IN BFILE);
TBD
Overload 9 dbms_sys_sql.bind_variable(c IN NUMBER, name IN VARCHAR2, value IN UROWID);
TBD
Overload 10 dbms_sys_sql.bind_variable(c IN NUMBER, name IN VARCHAR2, value IN TIME);
TBD
Overload 11 dbms_sys_sql.bind_variable(c IN NUMBER, name IN VARCHAR2, value IN TIMESTAMP);
TBD
Overload 12 dbms_sys_sql.bind_variable(c IN NUMBER, name IN VARCHAR2, value IN TIME WITH TIME ZONE);
TBD
Overload 13 dbms_sys_sql.bind_variable(c IN NUMBER, name IN VARCHAR2, value IN TIMESTAMP WITH TIME ZONE);
TBD
Overload 14 dbms_sys_sql.bind_variable(
c     IN NUMBER,
name  IN VARCHAR2,
value IN TIMESTAMP WITH LOCAL TIME ZONE);
TBD
Overload 15 dbms_sys_sql.bind_variable(
c     IN NUMBER,
name  IN VARCHAR2,
value IN INTERVAL YEAR TO MONTH);
TBD
Overload 16 dbms_sys_sql.bind_variable(
c     IN NUMBER,
name  IN VARCHAR2,
value IN INTERVAL DAY TO SECOND);
TBD
Overload 17 dbms_sys_sql.bind_variable(
c     IN NUMBER,
name  IN VARCHAR2,
value IN BINARY_FLOAT);
TBD
Overload 18 dbms_sys_sql.bind_variable(
c     IN NUMBER,
name  IN VARCHAR2,
value IN BINARY_DOUBLE);
TBD
Overload 19 dbms_sys_sql.bind_variable(
c     IN NUMBER,
name  IN VARCHAR2,
value IN standard.<ADT_1>);
TBD
Overload 20 dbms_sys_sql.bind_variable(
c     IN NUMBER,
name  IN VARCHAR2,
value IN REF);
TBD
Overload 21 dbms_sys_sql.bind_variable(
c     IN NUMBER,
name  IN VARCHAR2,
value IN STANDARD.<TABLE_1>);
TBD
Overload 22 dbms_sys_sql.bind_variable(
c     IN NUMBER,
name  IN VARCHAR2,
value IN STANDARD.<VARRAY_1>);
TBD
Overload 23 dbms_sys_sql.bind_variable(
c     IN NUMBER,
name  IN VARCHAR2,
value IN STANDARD.<OPAQUE_1>);
TBD
Overload 24 dbms_sys_sql.bind_variable(
c     IN NUMBER,
name  IN VARCHAR2,
value IN BOOLEAN);
TBD
 
BIND_VARIABLE_CHAR
Binds a given value to a given variable

Overload 1
dbms_sys_sql.bind_variable_char(
c     IN NUMBER,
name  IN VARCHAR2,
value IN CHAR);
TBD
Overload 2 dbms_sys_sql.bind_variable_char(
c              IN NUMBER,
name           IN VARCHAR2,
value          IN CHAR,
out_value_size IN NUMBER);
TBD
 
BIND_VARIABLE_PKG (new 12.2)
Overload 1 dbms_sys_sql.bind_variable_pkg(
c     IN NUMBER,
name  IN VARCHAR2,
value IN standard.<record_1>);
TBD
Overload 2 dbms_sys_sql.bind_variable_pkg(
c     IN NUMBER,
name  IN VARCHAR2,
value IN standard.<v2_table_1>);
TBD
 
BIND_VARIABLE_RAW
Binds a given value to a given variable

Overload 1
dbms_sys_sql.bind_variable_raw(
c     IN NUMBER,
name  IN VARCHAR2,
value IN RAW);
TBD
Overload 2 dbms_sys_sql.bind_variable_raw(
c              IN NUMBER,
name           IN VARCHAR2,
value          IN RAW,
out_value_size IN NUMBER);
TBD
 
BIND_VARIABLE_ROWID
Binds a given value to a given variable dbms_sys_sql.bind_variable_rowid(
c     IN NUMBER,
name  IN VARCHAR2,
value IN ROWID);
TBD
 
CLOSE_CURSOR
Closes cursor and frees associated memory dbms_sys_sql.close_cursor(c IN OUT NUMBER);
TBD
 
COLUMN_VALUE
Returns value of the cursor element for a given position in a cursor

Overload 1
dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT NUMBER);
TBD
Overload 2 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT VARCHAR2);
TBD
Overload 3 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT DATE);
TBD
Overload 4 dbms_sys_sql.column_value(
c        IN NUMBER,
position IN NUMBER);
TBD
Overload 5 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT BLOB);
TBD
Overload 6 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT CLOB);
TBD
Overload 7 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT BFILE);
TBD
Overload 8 dbms_sys_sql.column_value(
c             IN  NUMBER,
position      IN  NUMBER,
value         OUT NUMBER,
column_error  OUT NUMBER,
actual_length OUT NUMBER);
TBD
Overload 9 dbms_sys_sql.column_value(
c             IN  NUMBER,
position      IN  NUMBER,
value         OUT VARCHAR2,
column_error  OUT NUMBER,
actual_length OUT NUMBER);
TBD
Overload 10 dbms_sys_sql.column_value(
c             IN  NUMBER,
position      IN  NUMBER,
value         OUT DATE,
column_error  OUT NUMBER,
actual_length OUT NUMBER);
TBD
Overload 11 dbms_sys_sql.column_value(
c             IN  NUMBER,
position      IN  NUMBER,
column_error  OUT NUMBER,
actual_length OUT NUMBER);
TBD
Overload 12 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
n_tab    IN OUT dbms_sql.number_table);
TBD
Overload 13 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
c_tab    IN OUT dbms_sql.varchar2_table);
TBD
Overload 14 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
d_tab    IN OUT dbms_sql.date_table);
TBD
Overload 15 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
bl_tab   IN OUT dbms_sql.blob_table);
TBD
Overload 16 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
cl_tab   IN OUT dbms_sql.clob_table);
TBD
Overload 17 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
bf_tab   IN OUT dbms_sql.bfile_table);
TBD
Overload 18 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT UROWID);
TBD
Overload 19 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
ur_tab   IN OUT dbms_sql.urowid_table);
TBD
Overload 20 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT TIME);
TBD
Overload 21 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
tm_tab   IN OUT dbms_sql.time_table);
TBD
Overload 22 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT TIMESTAMP);
TBD
Overload 23 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
tms_tab  IN OUT dbms_sql.timestamp_table);
TBD
Overload 24 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT TIME WITH TIME ZONE);
TBD
Overload 25 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
ttz_tab  IN OUT dbms_sql.time_with_time_zone_table);
TBD
Overload 26 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT TIMESTAMP WITH TIME ZONE);
TBD
Overload 27 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
tstz_tab IN OUT dbms_sql.timestamp_with_time_zone_table);
TBD
Overload 28 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT TIMESTAMP WITH LOCAL TIMEZONE);
TBD
Overload 29 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
tstz_tab IN OUT dbms_sql.timestamp_with_ltz_table);
TBD
Overload 30 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT INTERVAL YEAR TO MONTH);
TBD
Overload 31 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
iym_tab  IN OUT dbms_sql.interval_year_to_month_table);
TBD
Overload 32 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT INTERVAL DAY TO SECOND);
TBD
Overload 33 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
ids_tab  IN OUT dbms_sql.interval_day_to_second_table);
TBD
Overload 34 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT BINARY_FLOAT);
TBD
Overload 35 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
bflt_tab IN OUT dbms_sql.binary_float_table);
TBD
Overload 36 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT BINARY_DOUBLE);
TBD
Overload 37 dbms_sys_sql.column_value(
c        IN     NUMBER,
position IN     NUMBER,
bdbl_tab IN OUT dbms_sql.binary_double_table);
TBD
Overload 38 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  BINARY_INTEGER,
value    OUT standard.<ADT_1>);
TBD
Overload 39 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  BINARY_INTEGER,
value    OUT REF);
TBD
Overload 40 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  BINARY_INTEGER,
value    OUT standard.<TABLE_1>);
TBD
Overload 41 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  BINARY_INTEGER,
value    OUT standard.<VARRAY_1>);
TBD
Overload 42 dbms_sys_sql.column_value(
c        IN  NUMBER,
position IN  BINARY_INTEGER,
value    OUT standard.<OPAQUE_1>);
TBD
Overload 43 dbms_sys_sql.column_value(
c        IN NUMBER,
position IN BINARY_INTEGER,
c_tab    IN dbms_sql.varchar2a);
TBD
 
COLUMN_VALUE_CHAR
Returns value of the cursor element for a given position in a cursor

Overload 1
dbms_sys_sql.column_value_char(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT CHAR);
TBD
Overload 2 dbms_sys_sql.column_value_char(
c             IN  NUMBER,
position      IN  NUMBER,
value         OUT CHAR,
column_error  OUT NUMBER,
actual_length OUT NUMBER);
TBD
 
COLUMN_VALUE_LONG
Returns a selected part of a LONG column, that has been defined using dbms_sys_sql.column_value_long(
c IN NUMBER, position IN NUMBER,
length                IN  NUMBER,
offset                IN  NUMBER,
value                 OUT VARCHAR2,
value_length          OUT NUMBER);
TBD
 
COLUMN_VALUE_RAW
Returns value of the cursor element for a given position in a cursor

Overload 1
dbms_sys_sql.column_value_raw(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT RAW);
TBD
Overload 2 dbms_sys_sql.column_value_raw(
c             IN  NUMBER,
position      IN  NUMBER,
value         OUT RAW,
column_error  OUT NUMBER,
actual_length OUT NUMBER);
TBD
 
COLUMN_VALUE_ROWID
Undocumented

Overload 1
dbms_sys_sql.column_value_rowid(
c        IN  NUMBER,
position IN  NUMBER,
value    OUT ROWID);
TBD
Overload 2 dbms_sys_sql.column_value_rowid(
c             IN  NUMBER,
position      IN  NUMBER,
value         OUT ROWID,
column_error  OUT NUMBER,
actual_length OUT NUMBER);
TBD
 
DEFINE_ARRAY
Defines a collection to be selected from the given cursor, used only with SELECT statements

Overload 1
dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
n_tab       IN dbms_sql.number_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
conn / as sysdba

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 count(*) from scott.emp order by 1', dbms_sql.NATIVE);

  dbms_sys_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_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
c_tab       IN dbms_sql.varchar2_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
Overload 3 dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
d_tab       IN dbms_sql.date_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
Overload 4 dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
bl_tab      IN dbms_sql.blob_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
Overload 5 dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
cl_tab      IN dbms_sql.clob_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
Overload 6 dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
bf_tab      IN dbms_sql.bile_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
Overload 7 dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
ur_tab      IN dbms_sql.urowid_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
Overload 8 dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
tm_tab      IN dbms_sql.time_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
Overload 9 dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
tms_tab     IN dbms_sql.timestamp_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
Overload 10 dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
ttz_tab     IN dbms_sql.time_with_time_zone_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
Overload 11 dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
tstz_tab    IN dbms_sql.timestamp_with_time_zone_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
Overload 12 dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
tstz_tab    IN dbms_sql.timestamp_with_ltz_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
Overload 13 dbms_sys_sql.define_array(dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
iym_tab     IN dbms_sql.interval_year_to_month_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
Overload 14 dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
ids_tab     IN dbms_sql.interval_day_to_second_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
Overload 15 dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
bflt_tab    IN dbms_sql.binary_float_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
Overload 16 dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
bdbl_tab    IN dbms_sql.binary_double_table,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
Overload 17 dbms_sys_sql.define_array(
c           IN NUMBER,
position    IN NUMBER,
c_tab       IN dbms_sql.varchar2a,
cnt         IN NUMBER,
lower_bound IN NUMBER);
TBD
 
DEFINE_COLUMN
Defines a column to be selected from the given cursor, used only with SELECT

Overload 1
dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN NUMBER);
TBD
Overoad 2 dbms_sys_sql.define_column(
c           IN NUMBER,
position    IN NUMBER,
column      IN NUMBER,
column_size IN NUMBER);
TBD
Overoad 3 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN DATE);
TBD
Overoad 4 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER);
TBD
Overoad 5 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN BLOB);
TBD
Overoad 6 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN CLOB);
TBD
Overoad 7 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN BFILE);
TBD
Overoad 8 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN UROWID);
TBD
Overoad 9 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN TIME);
TBD
Overoad 10 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN TIMESTAMP);
TBD
Overoad 11 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN TIME WITH TIME ZONE);
TBD
Overoad 12 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN TIMESTAMP WITH TIME ZONE);
TBD
Overoad 13 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN TIMESTAMP WITH LOCAL TIME ZONE);
TBD
Overoad 14 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN INTERVAL YEAR TO MONTH);
TBD
Overoad 15 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN INTERVAL DAY TO SECOND);
TBD
Overoad 16 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN BINARY_FLOAT);
TBD
Overoad 17 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN BINARY_DOUBLE);
TBD
Overoad 18 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN standard.<ADT_1>);
TBD
Overoad 19 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN REF);
TBD
Overoad 20 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN standard.<TABLE_1>);
TBD
Overoad 21 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN standard.<VARRAY_1>);
TBD
Overoad 22 dbms_sys_sql.define_column(
c        IN NUMBER,
position IN NUMBER,
column   IN standard.<OPAQUE_1>);
TBD
 
DEFINE_COLUMN_CHAR
Undocumented dbms_sys_sql.define_column_char(
c           IN NUMBER,
position    IN NUMBER,
column      IN CHAR,
column_size IN NUMBER);
TBD
 
DEFINE_COLUMN_LONG
Defines a LONG column to be selected from the given cursor, used only with SELECT dbms_sys_sql.define_column_long(c IN NUMBER, position IN NUMBER);
TBD
 
DEFINE_COLUMN_RAW
Undocumented dbms_sys_sql.define_column_raw(
c           IN NUMBER,
position    IN NUMBER,
column      IN RAW,
column_size IN NUMBER);
TBD
 
DEFINE_COLUMN_ROWID
Undocumented dbms_sys_sql.define_column_rowid(
c        IN NUMBER,
position IN NUMBER,
column   IN ROWID);
TBD
 
DESCRIBE_COLUMNS
Describes the columns for a cursor opened and parsed through DBMS_SQL dbms_sys_sql.describe_columns(
c       IN  NUMBER,
col_cnt OUT NUMBER,
desc_t  OUT dbms_sql.desc_tab);
TBD
 
DESCRIBE_COLUMNS2
Describes the specified column, an alternative method fixing a bug dbms_sys_sql.describe_columns2(
c       IN  NUMBER,
col_cnt OUT NUMBER,
desc_t  OUT dbms_sql.desc_tab2);
TBD
 
DESCRIBE_COLUMNS3 (new 12.2 overload 2)
Describes the specified column, an alternative method

Overload 1
dbms_sys_sql.describe_columns3(
c       IN  NUMBER,
col_cnt OUT NUMBER,
desc_t  OUT dbms_sql.desc_tab3);
TBD
Overload 2 dbms_sys_sql.describe_columns3(
c       IN  NUMBER,
col_cnt OUT NUMBER,
desc_t  OUT dbms_sql.desc_tab4);
TBD
 
DUMP_ALL_OPEN_CURSORS
Dumps all open dynamic SQL cursors dbms_sys_sql.dump_all_open_cursors RETURN BOOLEAN;
See OPEN_CURSOR Demo Below
 
EXECUTE
Execute the dynamic SQL identified by the cursor dbms_sys_sql.execute(c IN NUMBER) RETURN NUMBER;
Essentially identical with DBMS_SQL.EXECUTE as demonstrated on this page
 
EXECUTE_AND_FETCH
Execute the statement and fetch rows dbms_sys_sql.execute_and_fetch(c IN NUMBER, exact IN BOOLEAN) RETURN NUMBER;
TBD
 
FETCH_ROWS
Fetch rows for an opena dn executed cursor dbms_sys_sql.fetch_rows(c IN NUMBER) RETURN NUMBER;
TBD
 
GET_NEXT_RESULT
Undocumented

Overload 1
dbms_sys_sql.get_next_result(
c  IN  NUMBER,
rc OUT REF CURSOR);
TBD
Overload 2 dbms_sys_sql.get_next_result(
c  IN  NUMBER,
rc OUT NUMBER);
TBD
 
GET_RPI_CURSOR
Undocumented dbms_sys_sql.get_rpi_cursor(c IN NUMBER) RETURN NUMBER;
TBD
 
INIT
Undocumented dbms_sys_sql.init;
exec dbms_sys_sql.init;
 
IS_OPEN
Returns TRUE if the cursor is open otherwise FALSE dbms_sys_sql.is_open(c IN NUMBER) RETURN BOOLEAN;
See OPEN_CURSOR Demo Below
 
LAST_ERROR_POSITION
Returns the position of the last execution error dbms_sys_sql.last_error_position RETURN NUMBER;
set serveroutput on

BEGIN
  dbms_output.put_line(dbms_sys_sql.last_error_position);
END;
/
 
LAST_ROW_COUNT
Returns cumulative count of the number of rows fetched dbms_sys_sql.last_row_count RETURN NUMBER;
SELECT dbms_sys_sql.last_row_count
FROM dual;
 
LAST_ROW_ID
Returns ROWID of last row processed dbms_sys_sql.last_row_id RETURN ROWID;
TBD
 
LAST_SQL_FUNCTION_CODE
Returns SQL function code for statement dbms_sys_sql.last_sql_function_code RETURN NUMBER;
TBD
 
OPEN_CURSOR (new 12.2 overloads 3-4)
Opens a cursor for dynamic SQL

Overload 1
dbms_sys_sql.open_cursor RETURN NUMBER;
set serveroutput on

SELECT user_name, count(*)
FROM v$open_cursor
GROUP by user_name;

DECLARE
 c INTEGER;
BEGIN
  c := sys.dbms_sys_sql.open_cursor;
  dbms_output.put_line(TO_CHAR(c));

  IF dbms_sys_sql.is_open(c) THEN
    dbms_output.put_line('Cursor ' || TO_CHAR(c) || ' is open');
  END IF;

  sys.dbms_sys_sql.parse_as_user(c, 'SELECT COUNT(*) FROM dual', dbms_sql.NATIVE);

  IF dbms_sys_sql.dump_all_open_cursors THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

SELECT user_name, count(*)
FROM v$open_cursor
GROUP by user_name;
Overload 2 dbms_sys_sql.open_cursor(security_level IN NUMBER)
RETURN NUMBER;
DECLARE
 retVal NUMBER;
BEGIN
  retVal := dbms_sys_sql.open_cursor(1);
  dbms_output.put_line(retVal);
END;
/
854028694
Overload 3 dbms_sys_sql.open_cursor(treat_as_client_for_results IN BOOLEAN)
RETURN NUMBER;
DECLARE
 retVal NUMBER;
BEGIN
  retVal := dbms_sys_sql.open_cursor(TRUE);
  dbms_output.put_line(retVal);
END;
/
1162876943
Overload 4 dbms_sys_sql.open_cursor(
security_level              IN NUMBER,
treat_as_client_for_results IN BOOLEAN)
RETURN NUMBER;
TBD
 
PARSE
Parse statement

Overoad 1
dbms_sys_sql.parse(
c             IN NUMBER,
statement     IN VARCHAR2,
language_flag IN NUMBER);
TBD
Overload 2 dbms_sys_sql.parse(
c             IN NUMBER,
statement     IN dbms_sql.varchar2a,
lb            IN NUMBER,
ub            IN NUMBER,
lfflg         IN BOOLEAN,
language_flag IN NUMBER);
TBD
Overload 3 dbms_sys_sql.parse(
c             IN NUMBER,
statement     IN dbms_sql.varchar2s,
lb            IN NUMBER,
ub            IN NUMBER,
lfflg         IN BOOLEAN,
language_flag IN NUMBER);
TBD
Overload 4 dbms_sys_sql.parse(
c             IN NUMBER,
statement     IN CLOB,
language_flag IN NUMBER);
TBD
Overload 5 dbms_sys_sql.parse(
c             IN NUMBER,
statement     IN VARCHAR2,
language_flag IN NUMBER,
edition       IN VARCHAR2);
TBD
Overload 6 dbms_sys_sql.parse(
c                          IN NUMBER,
statement                  IN VARCHAR2,
language_flag              IN NUMBER,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN);
TBD
Overload 7 dbms_sys_sql.parse(
c             IN NUMBER,
statement     IN CLOB,
language_flag IN NUMBER,
edition       IN VARCHAR2);
TBD
Overload 8 dbms_sys_sql.parse(
c                          IN NUMBER,
statement                  IN CLOB,
language_flag              IN NUMBER,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN);
TBD
Overload 9 dbms_sys_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
Overload 10 dbms_sys_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,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN);
TBD
Overload 11 dbms_sys_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
Overload 12 dbms_sys_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,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN);
TBD
Overload 13 dbms_sys_sql.parse(
c                          IN NUMBER,
statement                  IN VARCHAR2,
language_flag              IN NUMBER,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2);
TBD
Overload 14 dbms_sys_sql.parse(
c                          IN NUMBER,
statement                  IN CLOB,
language_flag              IN NUMBER,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2);
TBD
Overload 15 dbms_sys_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,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2);
TBD
Overload 16 dbms_sys_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,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2);
TBD
Overload 17 dbms_sys_sql.parse(
c                          IN NUMBER,
statement                  IN VARCHAR2,
language_flag              IN NUMBER,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2,
container                  IN VARCHAR2);
TBD
Overload 18 dbms_sys_sql.parse(
c                          IN NUMBER,
statement                  IN CLOB,
language_flag              IN NUMBER,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2,
container                  IN VARCHAR2);
TBD
Overload 19 dbms_sys_sql.parse(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2a,
lb                         IN NUMBER,
ub                         IN NUMBER,
lefflg                     IN BOOLEAN,
language_flag              IN NUMBER,
edition                    IN VARCHAR2,
apply_crosseidtion_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2,
container                  IN VARCHAR2);
TBD
Overload 20 dbms_sys_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,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2,
container                  IN VARCHAR2);
TBD
 
PARSE_AS_USER (new 12.2 overloads 17-32)
Parse statement as the named user

Overload 1
dbms_sys_sql.parse_as_user(
c             IN NUMBER,
statement     IN VARCHAR2,
language_flag IN NUMBER,
userid        IN NUMBER);
-- source code from OWA_OPT_LOCK (reformatted by Morgan for clarity)
PROCEDURE validate_object_name(p_owner IN VARCHAR2, p_object IN VARCHAR2) IS
 stmt_cursor NUMBER;
 rc          NUMBER;
 found       NUMBER := 0;
 p_schema    VARCHAR2(30) := UPPER(p_owner);
 p_obj       VARCHAR2(30) := UPPER(p_object);
BEGIN
  stmt_cursor := dbms_sql.open_cursor;
  sys.dbms_sys_sql.parse_as_user(stmt_cursor,
      'BEGIN SELECT COUNT(*) INTO :found
             FROM all_objects
             WHERE owner = :p_schema AND object_name = :p_obj;
       EXCEPTION WHEN OTHERS THEN :found := 0;
       END;', dbms_sql.v7);
  dbms_sql.bind_variable(stmt_cursor, ':p_schema', p_schema);
  dbms_sql.bind_variable(stmt_cursor, ':p_obj', p_obj);
  dbms_sql.bind_variable(stmt_cursor, ':found', found);
  rc := dbms_sql.execute(stmt_cursor);
  dbms_sql.variable_value(stmt_cursor, ':found', found);
  dbms_sql.close_cursor(stmt_cursor);

  IF (found = 0) THEN
    raise_application_error(-20001, 'Cannot resolve object');
  END IF;
END; /
Overload 2 dbms_sys_sql.parse_as_user(
c             IN NUMBER,
statement     IN dbms_sql.varchar2a,
lb            IN NUMBER,
ub            IN NUMBER,
lfflg         IN BOOLEAN,
language_flag IN NUMBER,
userid        IN NUMBER);
TBD
Overload 3 dbms_sys_sql.parse_as_user(
c             IN NUMBER,
statement     IN dbms_sql.varchar2s,
lb            IN NUMBER,
ub            IN NUMBER,
lfflg         IN BOOLEAN,
language_flag IN NUMBER,
userid        IN NUMBER);
TBD
Overload 4

Note the use in this demo of DBMS_ASSERT
dbms_sys_sql.parse_as_user(
c             IN NUMBER,
statement     IN CLOB,
language_flag IN NUMBER,
userid        IN NUMBER);
conn / as sysdba

CREATE OR REPLACE PROCEDURE create_sequence(seqname IN VARCHAR2, uname IN VARCHAR2)
AUTHID DEFINER IS
 c      NUMBER;
 DDLStr CLOB := 'CREATE SEQUENCE ';
 retVal NUMBER;
 uid    dba_users.user_id%TYPE;
BEGIN
  c := dbms_sql.open_cursor;

  DDLStr := DDLStr || seqname;

  SELECT user_id
  INTO uid
  FROM dba_users
  WHERE username = dbms_assert.schema_name(uname);

  dbms_sys_sql.parse_as_user(c, DDLStr, dbms_sql.NATIVE, uid);
  retVal := dbms_sql.execute(c);
  dbms_sql.close_cursor(c);
END create_sequence;
/

exec create_sequence('MORGAN', 'SCOTT');

SELECT sequence_name
FROM dba_sequences
WHERE sequence_owner = 'SCOTT';

DROP SEQUENCE scott.morgan;
DROP PROCEDURE create_sequence;
Overload 5 dbms_sys_sql.parse_as_user(
c              IN NUMBER,
statement      IN VARCHAR2,
language_flag  IN NUMBER,
userid         IN NUMBER,
userlogonroles IN BOOLEAN);
TBD
Overload 6 dbms_sys_sql.parse_as_user(
c              IN NUMBER,
statement      IN VARCHAR2,
language_flag  IN NUMBER,
userid         IN NUMBER,
userlogonroles IN BOOLEAN,
edition        IN VARCHAR2);
TBD
Overload 7 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN VARCHAR2,
language_flag              IN NUMBER,
userid                     IN NUMBER,
userlogonroles             IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN);
TBD
Overload 8 dbms_sys_sql.parse_as_user(
c              IN NUMBER,
statement      IN CLOB,
language_flag  IN NUMBER,
userid         IN NUMBER,
userlogonroles IN BOOLEAN);
TBD
Overload 9 dbms_sys_sql.parse_as_user(
c              IN NUMBER,
statement      IN CLOB,
language_flag  IN NUMBER,
userid         IN NUMBER,
userlogonroles IN BOOLEAN,
edition        IN VARCHAR2);
TBD
Overload 10 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN CLOB,
language_flag              IN NUMBER,
userid                     IN NUMBER,
userlogonroles             IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN);
TBD
Overload 11 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2a,
language_flag              IN NUMBER,
userid                     IN NUMBER,
userlogonroles             IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN);
TBD
Overload 12 dbms_sys_sql.parse_as_user(
c              IN NUMBER,
statement      IN dbms_sql.varchar2a,
lb             IN NUMBER,
ub             IN NUMBER,
lfflg          IN BOOLEAN,
language_flag  IN NUMBER,
userid         IN NUMBER,
userlogonroles IN BOOLEAN,
edition        IN VARCHAR2);
TBD
Overload 13 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2a,
lb                         IN NUMBER,
ub                         IN NUMBER,
lfflg                      IN BOOLEAN,
language_flag              IN NUMBER,
userid                     IN NUMBER,
userlogonroles             IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN);
TBD
Overload 14 dbms_sys_sql.parse_as_user(
c              IN NUMBER,
statement      IN dbms_sql.varchar2s,
lb             IN NUMBER,
ub             IN NUMBER,
lfflg          IN BOOLEAN,
language_flag  IN NUMBER,
userid         IN NUMBER,
userlogonroles IN BOOLEAN);
TBD
Overload 15 dbms_sys_sql.parse_as_user(
c              IN NUMBER,
statement      IN dbms_sql.varchar2s,
lb             IN NUMBER,
ub             IN NUMBER,
lfflg          IN BOOLEAN,
language_flag  IN NUMBER,
userid         IN NUMBER,
userlogonroles IN BOOLEAN,
edition        IN VARCHAR2);
TBD
Overload 16 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2s,
lb                         IN NUMBER,
ub                         IN NUMBER,
lfflg                      IN BOOLEAN,
language_flag              IN NUMBER,
userid                     IN NUMBER,
userlogonroles             IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN);
TBD
Overload 17 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN VARCHAR2,
language_flag              IN NUMBER,
USERID                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2);
TBD
Overload 18 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN CLOB,
language_flag              IN NUMBER,
userid                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2);
TBD
Overload 19 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2a,
lb                         IN NUMBER,
ub                         IN NUMBER,
lfflg                      IN BOOLEAN,
language_flag              IN NUMBER,
userid                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2);
TBD
Overload 20 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2s,
lb                         IN NUMBER,
ub                         IN NUMBER,
lfflg                      IN BOOLEAN,
language_flag              IN NUMBER,
userid                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2);
TBD
Overload 21 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN VARCHAR2,
language_flag              IN NUMBER,
userid                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2,
container                  IN VARCHAR2);
TBD
Overload 22 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN CLOB,
language_flag              IN NUMBER,
userid                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2,
container                  IN VARCHAR2);
TBD
Overload 23 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2a,
lb                         IN NUMBER,
ub                         IN NUMBER,
lfflg                      IN BOOLEAN,
language_flag              IN NUMBER,
userid                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2,
container                  IN VARCHAR2);
TBD
Overload 24 dbms_sys_sql.parse_as_user(
C                          IN NUMBER,
statement                  IN dbms_sql.varchar2s,
lb                         IN NUMBER,
ub                         IN NUMBER,
lfflg                      IN BOOLEAN,
language_flag              IN NUMBER,
USERID                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2,
container                  IN VARCHAR2);
TBD
Overload 25 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN VARCHAR2,
language_flag              IN NUMBER,
USERID                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2,
container                  IN VARCHAR2,
xs_sessionid               IN RAW);
TBD
Overload 26 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN CLOB,
language_flag              IN NUMBER,
userid                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2,
container                  IN VARCHAR2,
xs_sessionid               IN RAW);
TBD
Overload 27 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2a,
lb                         IN NUMBER,
ub                         IN NUMBER,
lfflg                      IN BOOLEAN,
language_flag              IN NUMBER,
userid                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2
container                  IN VARCHAR2
xs_sessionid               IN RAW);
TBD
Overload 28 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2s,
lb                         IN NUMBER,
ub                         IN NUMBER,
lfflg                      IN BOOLEAN,
language_flag              IN NUMBER,
USERID                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2,
container                  IN VARCHAR2,
xs_sessionid               IN RAW);
TBD
Overload 29 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN VARCHAR2,
language_flag              IN NUMBER,
USERID                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2,
container                  IN VARCHAR2,
xs_sessionid               IN RAW,
username                   IN VARCHAR2);
TBD
Overload 30 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN CLOB,
language_flag              IN NUMBER,
userid                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2,
container                  IN VARCHAR2,
xs_sessionid               IN RAW,
username                   IN VARCHAR2);
TBD
Overload 31 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2a,
lb                         IN NUMBER,
ub                         IN NUMBER,
lfflg                      IN BOOLEAN,
language_flag              IN NUMBER,
userid                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2,
container                  IN VARCHAR2,
xs_sessionid               IN RAW,
username                   IN VARCHAR2);
TBD
Overload 32 dbms_sys_sql.parse_as_user(
c                          IN NUMBER,
statement                  IN dbms_sql.varchar2s,
lb                         IN NUMBER,
UB                         IN NUMBER,
LFFLG                      IN BOOLEAN,
language_flag              IN NUMBER,
userid                     IN NUMBER,
uselogonroles              IN BOOLEAN,
edition                    IN VARCHAR2,
apply_crossedition_trigger IN VARCHAR2,
fire_apply_trigger         IN BOOLEAN,
schema                     IN VARCHAR2,
container                  IN VARCHAR2,
xs_sessionid               IN RAW,
username                   IN VARCHAR2);
TBD
 
RETURN_RESULT
Undocumented

Overload 1
dbms_sys_sql.return_result(
rc        IN OUT REF CURSOR,
to_client IN     BOOLEAN);
TBD
Overload 2 dbms_sys_sql.return_result(
rc        IN OUT NUMBER,
to_client IN     BOOLEAN);
TBD
 
TO_CURSOR_NUMBER
Converts a ref cursor to a Dynamic SQL cursor dbms_sys_sql.to_cursor_number(rc IN OUT REF CURSOR) RETURN NUMBER;
TBD
 
TO_REFCURSOR
Converts a Dynamic SQL cursor to a ref cursor dbms_sys_sql.to_refcursor(cursor_number IN OUT NUMBER) RETURN REF CURSOR;
TBD
 
VARIABLE_VALUE (new 12.2 overload)
Returns value of named variable for given cursor
Overload 1
dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT NUMBER);
TBD
Overload 2 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT VARCHAR2);
TBD
Overload 3 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT DATE);
TBD
Overload 4 dbms_sys_sql.variable_value(
c    IN NUMBER,
name IN VARCHAR2);
TBD
Overload 5 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT BLOB);
TBD
Overload 6 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT CLOB);
TBD
Overload 7 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT BFILE);
TBD
Overload 8 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
n_tab OUT dbms_sql.number_table);
TBD
Overload 9 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
c_tab OUT dbms_sql.varchar2_table);
TBD
Overload 10 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
d_tab OUT dbms_sql.date_table);
TBD
Overload 11 dbms_sys_sql.variable_value(
c      IN  NUMBER,
name   IN  VARCHAR2,
bl_tab OUT dbms_sql.blob_table);
TBD
Overload 12 dbms_sys_sql.variable_value(
c      IN  NUMBER,
name   IN  VARCHAR2,
cl_tab OUT dbms_sql.clob_table);
TBD
Overload 13 dbms_sys_sql.variable_value(
c      IN  NUMBER,
name   IN  VARCHAR2,
bf_tab OUT dbms_sql.bfile_table);
TBD
Overload 14 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT UROWID);
TBD
Overload 15 dbms_sys_sql.variable_value(
c      IN  NUMBER,
name   IN  VARCHAR2,
ur_tab OUT dbms_sql.urowid_table);
TBD
Overload 16 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT TIME);
TBD
Overload 17 dbms_sys_sql.variable_value(
c      IN  NUMBER,
name   IN  VARCHAR2,
tm_tab OUT dbms_sql.time_table);
TBD
Overload 18 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT TIMESTAMP);
TBD
Overload 19 dbms_sys_sql.variable_value(
c       IN  NUMBER,
name    IN  VARCHAR2,
tms_tab OUT dbms_sql.timestamp_table);
TBD
Overload 20 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT TIME WITH TIME ZONE);
TBD
Overload 21 dbms_sys_sql.variable_value(
c       IN  NUMBER,
name    IN  VARCHAR2,
ttz_tab OUT dbms_sql.time_with_time_zone_table);
TBD
Overload 22 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT TIMESTAMP WITH TIME ZONE);
TBD
Overload 23 dbms_sys_sql.variable_value(
c        IN  NUMBER,
name     IN  VARCHAR2,
tstz_tab OUT dbms_sql.timestamp_with_time_zone_table);
TBD
Overload 24 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT TIMESTAMP WITH LOCAL TIME ZONE);
TBD
Overload 25 dbms_sys_sql.variable_value(
c        IN  NUMBER,
name     IN  VARCHAR2,
tstz_tab OUT dbms_sql.timestamp_with_ltz_table);
TBD
Overload 26 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT INTERVAL YEAR TO MONTH);
TBD
Overload 27 dbms_sys_sql.variable_value(
c       IN  NUMBER,
name    IN  VARCHAR2,
iym_tab OUT dbms_sql.interval_year_to_month_table);
TBD
Overload 28 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT INTERVAL DAY TO SECOND);
TBD
Overload 29 dbms_sys_sql.variable_value(
c       IN  NUMBER,
name    IN  VARCHAR2,
ids_tab OUT dbms_sql.interval_day_to_second_table);
TBD
Overload 30 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT BINARY_FLOAT);
TBD
Overload 31 dbms_sys_sql.variable_value(
c        IN  NUMBER,
name     IN  VARCHAR2,
bflt_tab OUT dbms_sql.binary_float_table);
TBD
Overload 32 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT BINARY_DOUBLE);
TBD
Overload 33 dbms_sys_sql.variable_value(
c        IN  NUMBER,
name     IN  VARCHAR2,
bdbl_tab OUT dbms_sql.binary_double_table);
TBD
Overload 34 dbms_sys_sql.variable_value(
c     IN NUMBER,
name  IN VARCHAR2,
value OUT standard.<ADT_1>);
TBD
Overload 35 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT REF);
TBD
Overload 36 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT standard.<TABLE_1>);
TBD
Overload 37 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT standard.<VARRAY_1>);
TBD
Overload 38 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT standard.<OPAQUE_1>);
TBD
Overload 39 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT dbms_sql.varchar2a);
TBD
Overload 40 dbms_sys_sql.variable_value(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT BOOLEAN);
TBD
 
VARIABLE_VALUE_CHAR
Undocumented dbms_sys_sql.variable_value_char(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT CHAR);
TBD
 
VARIABLE_VALUE_PKG (new 12.2)
Undocumented
Overload 1
dbms_sys_sql.variable_value_pkg(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT staandard.<RECORD_1>);
TBD
Overload 2 dbms_sys_sql.variable_value_pkg(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT standard.<V2_TABLE_1>);
TBD
Overload 3 dbms_sys_sql.variable_value_pkg(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT standard.<TABLE_1>);
TBD
Overload 4 dbms_sys_sql.variable_value_pkg(
c     IN NUMBER,
name  IN VARCHAR2,
value OUT standard.<VARRAY_1>);
TBD
 
VARIABLE_VALUE_RAW
Undocumented dbms_sys_sql.variable_value_raw(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT RAW);
TBD
 
VARIABLE_VALUE_ROWID
Undocumented dbms_sys_sql.variable_value_rowid(
c     IN  NUMBER,
name  IN  VARCHAR2,
value OUT ROWID);
set serveroutput on

DECLARE
 c      INTEGER;
 rid    VARCHAR2(20);
 retval ROWID;
BEGIN
  SELECT rowid
  INTO rid
  FROM uwclass.servers
  WHERE rownum = 1;

  dbms_output.put_line(rid);

  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, 'DELETE FROM uwclass.servers WHERE rowid = :x', dbms_sql.NATIVE);
  dbms_sql.bind_variable_rowid(c, ':x', rid);
  dbms_sys_sql.variable_value_rowid(c, ':x', retval);
  dbms_sql.close_cursor(c);

  dbms_output.put_line(retval);
END;
/

Related Topics
Built-in Functions
Built-in Packages
DBMS_SQL
Native Dynamic SQL
OWA_OPT_LOCK
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved