Oracle DBMS_SYS_SQL
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
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
DBMSOBJG DBMS_OFFLINE_UTL DBMS_REPCAT_VALIDATE
DBMSOBJG2 DBMS_PARALLEL_EXECUTE_INTERNAL DBMS_RESOURCE_MANAGER
DBMSOBJGWRAPPER DBMS_PRVTAQIM DBMS_SNAPSHOT_UTL
DBMSOBJG_DP DBMS_PRVTAQIP DBMS_SNAP_INTERNAL
DBMS_AQADM_SYS DBMS_RECO_SCRIPT_INVOK DBMS_SQL
DBMS_AUDIT_MGMT DBMS_RECTIFIER_DIFF_INTERNAL DBMS_SQL_TRANSLATOR_EXPORT
DBMS_DDL DBMS_RECTIFIER_FRIENDS DBMS_STANDARD
DBMS_DDL_INTERNAL DBMS_REDEFINITION DBMS_STATS
DBMS_DEFERGEN DBMS_REDEFINITION_INTERNAL DBMS_STATS_INTERNAL
DBMS_DEFERGEN_UTIL DBMS_REPCAT_ADD_MASTER DBMS_STREAMS_ADM_UTL
DBMS_DEFER_INTERNAL_SYS DBMS_REPCAT_DECL DBMS_STREAMS_MC
DBMS_DEFER_SYS_DEFINER DBMS_REPCAT_FLA_MAS DBMS_SUMMARY
DBMS_DEFER_SYS_PART1 DBMS_REPCAT_INTERNAL KUPD$DATA
DBMS_DST DBMS_REPCAT_MAS LOGMNR_DICT_CACHE
DBMS_EXPORT_EXTENSION DBMS_REPCAT_OUTPUT OWA_OPT_LOCK
DBMS_FILE_GROUP_UTL DBMS_REPCAT_RGT_CUST OWA_UTIL
DBMS_ITRIGGER_UTL DBMS_REPCAT_RPC PRVT_ILM
DBMS_I_INDEX_UTL DBMS_REPCAT_RQ PRVT_SYS_TUNE_MVIEW
DBMS_JOB DBMS_REPCAT_SNA_UTL SYS_STUB_FOR_PURITY_ANALYSIS
DBMS_LOGMNR_LOGREP_DICT DBMS_REPCAT_SQL_UTL UTL_IDENT
DBMS_ODCI DBMS_REPCAT_UTL WPG_DOCLOAD
DBMS_OFFLINE_INTERNAL DBMS_REPCAT_UTL2 WWV_DBMS_SQL
DBMS_OFFLINE_OG_INTERNAL DBMS_REPCAT_UTL3 XS_DATA_SECURITY_UTIL
DBMS_OFFLINE_SNAPSHOT_INTERNAL DBMS_REPCAT_UTL4 XS_DIAG_INT
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 (new 12.1 overloads)
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
Time with Timezone
Overload 19
dbms_sys_sql.bind_array(c IN NUMBER, name IN VARCHAR2, ttz_tab IN dbms_sql.time_with_time_zone_table);
TBD
Time with Timezone

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
Timestamp with Timezone

Overload 21
dbms_sys_sql.bind_array(
c        IN NUMBER,
name     IN VARCHAR2,
tstz_tab IN dbms_sql.timestamp_with_time_zone_table);
TBD
Timestamp with Timezone

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
Timestamp with Local Timezone

Overload 23
dbms_sys_sql.bind_array(
c        IN NUMBER,
name     IN VARCHAR2,
tstz_tab IN dbms_sql.timestamp_with_ltz_table);
TBD
Timestamp with Local Timezone

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
Interval Year to Month

Overload 25
dbms_sys_sql.bind_array(
c       IN NUMBER,
name    IN VARCHAR2,
iym_tab IN dbms_sql.interval_year_to_month_table);
TBD
Interval Year to Month

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
Interval Day to Second
Overload 27
dbms_sys_sql.bind_array(c IN NUMBER, name IN VARCHAR2, ids_tab IN dbms_sql.interval_day_to_second_table);
TBD
Interval Day to Second

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
Binary Float

Overload 29
dbms_sys_sql.bind_array(
c        IN NUMBER,
name     IN VARCHAR2,
bflt_tab IN dbms_sql.binary_float_table);
TBD
Binary Float

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
 
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
 
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_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 (new 12.1 overload)
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 (new 12.1 overload)
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
Describes the specified column, an alternative method dbms_sys_sql.describe_columns3(
c       IN  NUMBER,
col_cnt OUT NUMBER,
desc_t  OUT dbms_sql.desc_tab3);
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 (new 12.1)
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
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;
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
 
PARSE_AS_USER
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
 
RETURN_RESULT (new 12.1)
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.1 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
 
VARIABLE_VALUE_CHAR
Undocumented dbms_sys_sql.variable_value_char(c IN NUMBER, name IN VARCHAR2, value OUT CHAR);
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
DBMS_SQL
Native Dynamic SQL
OWA_OPT_LOCK
Packages

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-2014 Daniel A. Morgan All Rights Reserved