Oracle DBMS_SYS_SQL
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose 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
DBMS_AQADM_SYS DBMS_REDEFINITION DBMS_STREAMS_ADM_UT
DBMS_DDL DBMS_REDEFINITION_INTERNAL DBMS_SYS_SQL
DBMS_DST DBMS_RESOURCE_MANAGER EXECASUSER
DBMS_EXPORT_EXTENSION DBMS_SNAPSHOT_UTL KUPD$DATA
DBMS_FILE_GROUP_UTL DBMS_SNAP_INTERNAL LOGMNR_DICT_CACHE
DBMS_IREFRESH DBMS_SQL OLS_ENFORCEMENT
DBMS_LOGMNR_LOGREP_DICT DBMS_SQL_TRANSLATOR_EXPORT PRVT_ILM
DBMS_LOGREP_UTIL DBMS_STANDARD UTL_IDENT
DBMS_PRVTAQIP DBMS_STATS XS_DATA_SECURITY_UTIL_INT
DBMS_RECO_SCRIPT_INVOK DBMS_STATS_INTERNAL  
Documented No
First Available Not known but 2000, or earlier
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/prvthssq.plb
Subprograms
BIND_ARRAY DEFINE_COLUMN_LONG LAST_ROW_COUNT
BIND_VARIABLE DEFINE_COLUMN_RAW LAST_ROW_ID
BIND_VARIABLE_CHAR DEFINE_COLUMN_ROWID LAST_SQL_FUNCTION_CODE
BIND_VARIABLE_PKG DESCRIBE_COLUMNS OPEN_CURSOR
BIND_VARIABLE_RAW DESCRIBE_COLUMNS2 PARSE
BIND_VARIABLE_ROWID DESCRIBE_COLUMNS3 PARSE_AS_USER
CLOSE_CURSOR DUMP_ALL_OPEN_CURSORS RETURN_RESULT
COLUMN_VALUE EXECUTE TO_CURSOR_NUMBER
COLUMN_VALUE_CHAR EXECUTE_AND_FETCH TO_REFCURSOR
COLUMN_VALUE_LONG FETCH_ROWS VARIABLE_VALUE
COLUMN_VALUE_RAW GET_NEXT_RESULT VARIABLE_VALUE_CHAR
COLUMN_VALUE_ROWID GET_RPI_CURSOR VARIABLE_VALUE_PKG
DEFINE_ARRAY INIT VARIABLE_VALUE_RAW
DEFINE_COLUMN IS_OPEN VARIABLE_VALUE_ROWID
DEFINE_COLUMN_CHAR LAST_ERROR_POSITION  
 
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
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
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
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
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
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)
AUTHID DEFINER 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
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
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 21c
What's New In 23c

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