`

解析 dbmsSql.sql

SQLCC++C#Oracle 
阅读更多
这篇文章解析PACKAGE dbms_sql AUTHID CURRENT_USER:
文件地址:{ORACLE_HOME}/rdbms/admin/dbmssql.sql
NOTES:DMBS_SQL is the traditional form of dynamic SQL in Oracle.

For most purposes native dynamic sql (NDS) will suffice but there are some things the DBMS_SQL package does that can not be done any other way. This page emphasizes those areas where there is no substitute.

Constants:
Name Data Type Value
v6 INTEGER 0
native INTEGER         1
v7 INTEGER         2

Defined Data Types:
General Types 
TYPE desc_rec IS RECORD (
col_type binary_integer := 0,
col_max_len binary_integer := 0,
col_name varchar2(32) := '',
col_name_len binary_integer := 0,
col_schema_name varchar2(32) := '',
col_schema_name_len binary_integer := 0,
col_precision binary_integer := 0,
col_scale binary_integer := 0,
col_charsetid binary_integer := 0,
col_charsetform binary_integer := 0,
col_null_ok boolean := TRUE);

TYPE desc_rec2 IS RECORD (
col_type binary_integer := 0,
col_max_len binary_integer := 0,
col_name varchar2(32767) := '',
col_name_len binary_integer := 0,
col_schema_name varchar2(32) := '',
col_schema_name_len binary_integer := 0,
col_precision binary_integer := 0,
col_scale binary_integer := 0,
col_charsetid binary_integer := 0,
col_charsetform binary_integer := 0,
col_null_ok boolean := TRUE);

TYPE desc_tab IS TABLE OF desc_rec
INDEX BY binary_integer;

TYPE desc_tab2 IS TABLE OF desc_rec2
INDEX BY binary_integer;

TYPE varchar2a IS TABLE OF VARCHAR2(32767)
INDEX BY binary_integer;

TYPE varchar2s IS TABLE OF VARCHAR2(256)
INDEX BY binary_integer;

Bulk SQL Types
TYPE bfile_table IS TABLE OF bfile
INDEX BY binary_integer;

TYPE binary_double_table IS TABLE OF binary_double
INDEX BY binary_integer;

TYPE binary_float_table IS TABLE OF binary_float
INDEX BY binary_integer;

TYPE blob_table IS TABLE OF blob
INDEX BY binary_integer;

TYPE clob_table IS TABLE OF clob
INDEX BY binary_integer;

TYPE date_table IS TABLE OF date
INDEX BY binary_integer;

TYPE interval_day_to_second_table IS TABLE OF
dsinterval_unconstrained INDEX BY binary_integer;

TYPE interval_year_to_MONTH_Table IS TABLE OF
yminterval_unconstrained
INDEX BY binary_integer;

TYPE number_table IS TABLE OF NUMBER
INDEX BY binary_integer;

TYPE time_table IS TABLE OF time_unconstrained
INDEX BY binary_integer;

TYPE time_with_time_zone_Table IS TABLE OF TIME_TZ_UNCONSTRAINED INDEX BY binary_integer;

TYPE timestamp_table IS TABLE OF timestamp_unconstrained
INDEX BY binary_integer;

TYPE timestamp_with_ltz_table IS TABLE OF
TIMESTAMP_LTZ_UNCONSTRAINED
INDEX BY binary_integer;

TYPE urowid_table IS TABLE OF urowid
INDEX BY binary_integer;

TYPE timestamp_with_time_zone_table IS TABLE OF
TIMESTAMP_TZ_UNCONSTRAINED
INDEX BY binary_integer;

TYPE varchar2_table IS TABLE OF VARCHAR2(2000)
INDEX BY binary_integer;

Dependencies: 
SELECT name
FROM dba_dependencies
WHERE referenced_name = 'DBMS_SQL'
UNION
SELECT referenced_name
FROM dba_dependencies
WHERE name = 'DBMS_SQL';

Exceptions:
Error Code ORA-06562
Reason         Inconsistent types: Raised by procedure "column_value" or "variable_value" if the type of out argument where to put the requested value is different from the type of the value
Flow:
--
--                      -----------
--                    | open_cursor |
--                      -----------
--                           |
--                           |
--                           v
--                         -----
--          ------------>| parse |
--          |              -----
--          |                |
--          |                | ---------
--          |                v          |
--          |           --------------  |
--          |-------->| bind_variable | |
--          |     ^     -------------   |
--          |     |           |         |
--          |      -----------|         |
--          |                 |<--------
--          |                 v
--          |               query?---------- yes ---------
--          |                 |                           |
--          |                no                           |
--          |                 |                           |
--          |                 v                           v
--          |              -------                  -------------
--          |----------->| execute |            ->| define_column |
--          |              -------             |    -------------
--          |                 |------------    |          |
--          |                 |            |    ----------|
--          |                 v            |              v
--          |           --------------     |           -------
--          |       ->| variable_value |   |  ------>| execute |
--          |      |    --------------     | |         -------
--          |      |          |            | |            |
--          |       ----------|            | |            |
--          |                 |            | |            v
--          |                 |            | |        ----------
--          |                 |<-----------  |----->| fetch_rows |
--          |                 |              |        ----------
--          |                 |              |            |
--          |                 |              |            v
--          |                 |              |  -----------------
--          |                 |              | | column_value    |
--          |                 |              | | variable_value  |
--          |                 |              |  -----------------
--          |                 |              |            |
--          |                 |<--------------------------
--          |                 |
--           -----------------|
--                            |
--                            v
--                       ------------
--                     | close_cursor |
--                       ------------
--
BIND_ARRAY:
Binds a given value to a given collection: Overload 1 
dbms_sql.BIND_ARRAY(
c      IN INTEGER,
name   IN VARCHAR2,
n_tab  IN NUMBER_TABLE);
conn scott/tiger

DECLARE
 stmt          VARCHAR2(200);
 dept_no_array dbms_sql.number_table;
 c             NUMBER;
 dummy         NUMBER;
BEGIN
  dept_no_array(1) := 10; dept_no_array(2) := 20;
:
  dept_no_array(3) := 30; dept_no_array(4) := 40;
  dept_no_array(5) := 30; dept_no_array(6) := 40;
  stmt := 'delete from emp where deptno = :dept_array';
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, stmt, dbms_sql.NATIVE);
  dbms_sql.bind_array(c, ':dept_array', dept_no_array, 1, 4);
  dummy := dbms_sql.execute(c);
  dbms_sql.close_cursor(c);

EXCEPTION
  WHEN OTHERS THEN
    IF dbms_sql.is_open(c) THEN
      dbms_sql.close_cursor(c);
    END IF;
    RAISE;
END;
/

Overload 2:
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
c_tab  IN VARCHAR2_TABLE);
TBD
Overload 3:
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
d_tab  IN DATE_TABLE);
TBD
Overload 4:
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
bl_tab IN BLOB_TABLE);
TBD
Overload 5:
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
cl_tab IN CLOB_TABLE);
TBD
Overload 6:
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
bf_tab IN BFILE_TABLE);
TBD
Overload 7:
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
n_tab  IN NUMBER_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
Overload 8:
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
c_tab  IN VARCHAR2_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
Overload 9:
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
d_tab  IN DATE_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
Overload 10:
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
bl_tab  IN BLOB_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
Overload 11:
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
cl_tab  IN CLOB_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
Overload 12：
dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
bf_tab IN BFILE_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
Overload 13:
dbms_sql.bind_array(
c      IN INTEGER,
name   IN VARCHAR2,
ur_tab IN UROWID_TABLE);
TBD
Overload 14:
dbms_sql.bind_array (
c      IN INTEGER,
name   IN VARCHAR2,
ur_tab IN UROWID_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
Overload 15:
dbms_sql.bind_array (
c      IN INTEGER,
name   IN VARCHAR2,
tm_tab IN TIME_TABLE);
TBD
Overload 16	dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
tm_tab IN TIME_TABLE,
index1 IN INTEGER,
index2 IN INTEGER);
TBD
Overload 17	dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
tms_tab IN TIMESTAMP_TABLE);
TBD
Overload 18	dbms_sql.bind_array (
c       IN INTEGER,
name    IN VARCHAR2,
tms_tab IN TIMESTAMP_TABLE
index1  IN INTEGER,
index2  IN INTEGER);
TBD
Overload 19	dbms_sql.bind_array (
c        IN INTEGER,
name     IN VARCHAR2,
ttz_tab IN TIME_WITH_TIME_ZONE_TABLE);
TBD
Overload 20	dbms_sql.bind_array (
c        IN INTEGER,
name     IN VARCHAR2,
ttz_tab IN TIME_WITH_TIME_ZONE_TABLE,
index1  IN INTEGER,
index2  IN INTEGER);
TBD
Overload 21	dbms_sql.bind_array (
c         IN INTEGER,
name      IN VARCHAR2,
tstz_tab IN TIMESTAMP_WITH_TIME_ZONE_TABLE);
TBD
Overload 22	dbms_sql.bind_array (
c         IN INTEGER,
name      IN VARCHAR2,
tstz_tab IN TIMESTAMP_WITH_TIME_ZONE_TABLE,
index1   IN INTEGER,
index2   IN INTEGER);
TBD
Overload 23	
dbms_sql.bind_array (
c         IN INTEGER,
name      IN VARCHAR2,
tstz_tab IN TIMESTAMP_WITH_LTZ_TABLE);
TBD
Overload 24	
dbms_sql.bind_array (
c         IN INTEGER,
name      IN VARCHAR2,
tstz_tab IN TIMESTAMP_WITH_LTZ_TABLE,
index1   IN INTEGER,
index2   IN INTEGER);
TBD
Overload 25	
dbms_sql.bind_array (
c        IN INTEGER,
name     IN VARCHAR2,
iym_tab IN INTERVAL_YEAR_TO_MONTH_TABLE);
TBD
Overload 26	
dbms_sql.bind_array (
c        IN INTEGER,
name     IN VARCHAR2,
iym_tab IN INTERVAL_YEAR_TO_MONTH_TABLE,
index1  IN INTEGER,
index2  IN INTEGER);
TBD
Overload 27	
dbms_sql.bind_array (
c        IN INTEGER,
name     IN VARCHAR2,
ids_tab IN INTERVAL_DAY_TO_SECOND_TABLE);
TBD
Overload 28	
dbms_sql.bind_array (
c        IN INTEGER,
name     IN VARCHAR2,
ids_tab IN INTERVAL_DAY_TO_SECOND_TABLE,
index1  IN INTEGER,
index2  IN INTEGER);
TBD
Overload 29	
dbms_sql.bind_array (
c         IN INTEGER,
name      IN VARCHAR2,
bflt_tab IN BINARY_FLOAT_TABLE);
TBD
Overload 30	
dbms_sql.bind_array (
c         IN INTEGER,
name      IN VARCHAR2,
bflt_tab IN BINARY_FLOAT_TABLE,
index1  IN INTEGER,
index2  IN INTEGER);
TBD
Overload 31	
dbms_sql.bind_array (
c         IN INTEGER,
name      IN VARCHAR2,
bdbl_tab IN BINARY_DOUBLE_TABLE);
TBD
Overload 32	
dbms_sql.bind_array (
c         IN INTEGER,
name      IN VARCHAR2,
bdbl_tab IN BINARY_DOUBLE_TABLE,
index1  IN INTEGER,
index2  IN INTEGER);
TBD
BIND_VARIABLE

Binds a given value to a given variable

Overload 1	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN NUMBER);
conn scott/tiger
SELECT COUNT(*) FROM emp;
CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS
 cursor_name    INTEGER;
 rows_processed INTEGER;
BEGIN
  cursor_name := dbms_sql.open_cursor;
  dbms_sql.parse(cursor_name, 'DELETE FROM emp WHERE sal > :x',
dbms_sql.NATIVE);
  dbms_sql.bind_variable(cursor_name, ':x', salary);
  rows_processed := dbms_sql.execute(cursor_name);
  dbms_sql.close_cursor(cursor_name);
EXCEPTION
  WHEN OTHERS THEN
    dbms_sql.close_cursor(cursor_name);
END demo;
/
exec demo(30001)
SELECT COUNT(*) FROM emp;
rollback;
Overload 2	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN VARCHAR2 CHARACTER SET ANY_CS);
TBD
Overload 3	
dbms_sql.bind_variable(
c              IN INTEGER,
name           IN VARCHAR2,
value          IN VARCHAR2 CHARACTER SET ANY_CS,
out_value_size IN INTEGER);
TBD
Overload 4	
dbms_sql.bind_variable (
c     IN INTEGER,
name  IN VARCHAR2,
value IN DATE);
TBD
Overload 5	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN BLOB);
TBD
Overload 6	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN CLOB CHARACTER SET ANY_CS);
TBD
Overload 7	
dbms_sql.bind_variable (
c     IN INTEGER,
name  IN VARCHAR2,
value IN BFILE);
TBD
Overload 8	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN UROWID);
TBD
Overload 9	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN TIME_UNCONSTRAINED);
TBD
Overload 10	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN TIMESTAMP_UNCONSTRAINED);
TBD
Overload 11	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN TIME_TZ_UNCONSTRAINED);
TBD
Overload 12	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN TIMESTAMP_TZ_UNCONSTRAINED);
TBD
Overload 13	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN TIMESTAMP_LTZ_UNCONSTRAINED);
TBD
Overload 14	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN YMINTERVAL_UNCONSTRAINED);
TBD
Overload 15	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN DSINTERVAL_UNCONSTRAINED);
TBD
Overload 16	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN BINARY_FLOAT);
TBD
Overload 17	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN BINARY_DOUBLE);
TBD
Overload 18	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN <ADT_1>);
TBD
Overload 19	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN REF <ADT_1>);
TBD
Overload 20	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN <TABLE_1>);
TBD
Overload 21	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN <VARRAY_1>);
TBD
Overload 22	
dbms_sql.bind_variable(
c     IN INTEGER,
name  IN VARCHAR2,
value IN <OPAQUE_1>);
TBD
BIND_VARIABLE_CHAR
Binds a given value to a given variable

Overload 1	dbms_sql.bind_variable_char(
c     IN INTEGER,
name  IN VARCHAR2,
value IN CHAR CHARACTER SET ANY_CS);
See bind_variable demo
Overload 2	dbms_sql.bind_variable_char(
c              IN INTEGER,
name           IN VARCHAR2,
value          IN CHAR CHARACTER SET ANY_CS,
out_value_size IN INTEGER);
TBD
 
BIND_VARIABLE_RAW
Binds a given value to a given variable

Overload 1	dbms_sql.bind_variable_raw(
c     IN INTEGER,
name  IN VARCHAR2,
value IN RAW);
See bind_variable demo
Overload 2	dbms_sql.bind_variable_raw(
c              IN INTEGER,
name           IN VARCHAR2,
value          IN RAW,
out_value_size IN INTEGER);
TBD
 
BIND_VARIABLE_ROWID
Binds a given value to a given variable

Overload 1	dbms_sql.bind_variable_rowid(
c     IN INTEGER,
name  IN VARCHAR2,
value IN ROWID);
See bind_variable demo
Overload 2	dbms_sql.bind_variable_rowid(
c              IN INTEGER,
name           IN VARCHAR2,
value          IN ROWID,
out_value_size IN INTEGER);
TBD
 
CLOSE_CURSOR
Closes cursor and free memory	dbms_sql.close_cursor(c IN OUT INTEGER);
See is_open demo
 
COLUMN_VALUE
Returns value of the cursor element for a given position in a cursor

Overload 1	dbms_sql.column_value
c        IN  INTEGER,
position IN  INTEGER,
value    OUT NUMBER);
See final demo
Overload 2	dbms_sql.column_value(
c         IN  INTEGER,
position  IN  INTEGER,
value     OUT VARCHAR2 CHARACTER SET ANY_CS);
TBD
Overload 3	dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT DATE);
TBD
Overload 4	dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT BLOB);
TBD
Overload 5	dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT CLOB CHARACTER SET ANY_CS);
TBD
Overload 6	dbms_sql.column_value (
c        IN  INTEGER,
position IN  INTEGER,
value    OUT BFILE);
TBD
Overload 7	dbms_sql.column_value(
c             IN  INTEGER,
position      IN  INTEGER,
value         OUT NUMBER,
column_error  OUT NUMBER,
actual_length OUT INTEGER);
TBD
Overload 8	dbms_sql.column_value(
c             IN  INTEGER,
position      IN  INTEGER,
value         OUT VARCHAR2 CHARACTER SET ANY_CS,
column_error  OUT NUMBER,
actual_length OUT INTEGER);
TBD
Overload 9	dbms_sql.column_value(
c             IN  INTEGER,
position      IN  INTEGER,
value         OUT DATE,
column_error  OUT NUMBER,
actual_length OUT INTEGER);
TBD
Overload 10	dbms_sql.column_value(
c        IN     INTEGER,
position  IN     INTEGER,
n_tab    IN OUT NOCOPY NUMBER_TABLE);
TBD
Overload 11	dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
c_tab    IN OUT NOCOPY VARCHAR2_TABLE);
TBD
Overload 12	dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
d_tab    IN OUT NOCOPY DATE_TABLE);
TBD
Overload 13	dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
bl_tab   IN OUT NOCOPY BLOB_TABLE);
TBD
Overload 14	dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
cl_tab   IN OUT NOCOPY CLOB_TABLE);
TBD
Overload 15	dbms_sql.column_value (
c        IN     INTEGER,
position IN     INTEGER,
bf_tab   IN OUT NOCOPY BFILE_TABLE);
TBD
Overload 16	dbms_sql.column_value (
c        IN  INTEGER,
position IN  INTEGER,
value    OUT UROWID);
TBD
Overload 17	dbms_sql.column_value (
c        IN     INTEGER,
position IN     INTEGER,
ur_tab   IN OUT NOCOPY UROWID_TABLE);
TBD
Overload 18	dbms_sql.column_value (
c        IN  INTEGER,
position IN  INTEGER,
value    OUT TIME_UNCONSTRAINED);
TBD
Overload 19	dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
tm_tab   IN OUT NOCOPY TIME_TABLE);
TBD
Overload 20	dbms_sql.column_value (
c        IN  INTEGER,
position IN  INTEGER,
value    OUT TIMESTAMP_UNCONSTRAINED);
TBD
Overload 21	dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
tms_tab  IN OUT NOCOPY TIMESTAMP_TABLE);
TBD
Overload 22	dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT TIME_TZ_UNCONSTRAINED);
TBD
Overload 23	dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
ttz_tab  IN OUT NOCOPY TIME_WITH_TIME_ZONE_TABLE);
TBD
Overload 24	dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT TIMESTAMP_TZ_UNCONSTRAINED);
TBD
Overload 25	dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
tstz_tab IN OUT NOCOPY TIMESTAMP_WITH_TIME_ZONE_TABLE);
TBD
Overload 26	dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT TIMESTAMP_LTZ_UNCONSTRAINED);
TBD
Overload 27	dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
tstz_tab IN OUT NOCOPY TIMESTAMP_WITH_LTZ_TABLE);
TBD
Overload 28	dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT YMINTERVAL_UNCONSTRAINED);
TBD
Overload 29	dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
iym_tab  IN OUT NOCOPY INTERVAL_YEAR_TO_MONTH_TABLE);
TBD
Overload 30	dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT DSINTERVAL_UNCONSTRAINED);
TBD
Overload 31	dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
ids_tab  IN OUT NOCOPY INTERVAL_DAY_TO_SECOND_TABLE);
TBD
Overload 32	dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER, 
value    OUT BINARY_FLOAT);
TBD
Overload 33	dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
bflt_tab IN OUT NOCOPY BINARY_FLOAT_TABLE);
TBD
Overload 34	dbms_sql.column_value(
c        IN  INTEGER,
position IN  INTEGER, 
value    OUT BINARY_DOUBLE);
TBD
Overload 35	dbms_sql.column_value(
c        IN     INTEGER,
position IN     INTEGER,
bdbl_tab IN OUT NOCOPY BINARY_DOUBLE_TABLE);
TBD
Overload 36	dbms_sql.column_value(
c        IN  INTEGER,
position IN  BINARY_INTEGER,
value    OUT "<ADT_1>");
TBD
Overload 37	dbms_sql.column_value(
c        IN  INTEGER,
position IN  BINARY_INTEGER,
value    OUT REF "<ADT_1>");
TBD
Overload 38	dbms_sql.column_value(
c        IN  INTEGER,
position IN  BINARY_INTEGER,
value    OUT "<TABLE_1>");
TBD
Overload 39	dbms_sql.column_value(
c        IN  INTEGER,
position IN  BINARY_INTEGER,
value    OUT "<VARRAY_1>");
TBD
Overload 40	dbms_sql.column_value(
c        IN  INTEGER,
position IN  BINARY_INTEGER,
value    OUT "<OPAQUE_1>");
TBD
 
COLUMN_VALUE_CHAR
Returns value of the cursor element for a given position in a cursor

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

Overload 1	dbms_sql.column_value_raw(
c        IN  INTEGER,
position IN  INTEGER,
value    OUT RAW);
See column_value in final demo
Overload 2	dbms_sql.column_value_raw(
c             IN  INTEGER,
position      IN  INTEGER,
value         OUT RAW,
column_error  OUT NUMBER,
actual_length OUT INTEGER);
TBD
 
COLUMN_VALUE_ROWID
Undocumented

Overload 1	dbms_sql.column_value_rowid(
c               IN  INTEGER,
position        IN  INTEGER,
value           OUT ROWID);
See column_value in final demo
Overload 2	dbms_sql.column_value_rowid(
c             IN  INTEGER,
position      IN  INTEGER,
value         OUT ROWID,
column_error  OUT NUMBER,
actual_length OUT INTEGER);
TBD
 
DEFINE_ARRAY

Defines a collection to be selected from the given cursor, used only with SELECT statements

Overload 1	dbms_sql.define_array(
c         IN INTEGER,
position  IN INTEGER,
n_tab     IN NUMBER_TABLE,
cnt       IN INTEGER,
lower_bnd IN INTEGER);
DECLARE
 c     NUMBER;
 d     NUMBER;
 n_tab dbms_sql.number_table;
 indx  NUMBER := -10;
BEGIN
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,'select n from t order by 1',dbms_sql.NATIVE);

  dbms_sql.define_array(c, 1, n_tab, 10, indx);

  d := dbms_sql.execute(c);

  LOOP
    d := dbms_sql.fetch_rows(c);
    dbms_sql.column_value(c, 1, n_tab);
    exit when d != 10;
  END LOOP;

  dbms_sql.close_cursor(c);
EXCEPTIONS
  WHEN OTHERS THEN
    IF dbms_sql.is_open(c) THEN
      dbms_sql.close_cursor(c);
    END IF;
    RAISE;
END;
/
Overload 2	dbms_sql.define_array (
c           IN INTEGER,
position    IN INTEGER,
c_tab       IN VARCHAR2_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 3	dbms_sql.define_array (
c           IN INTEGER,
position    IN INTEGER,
d_tab       IN DATE_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 4	dbms_sql.define_array (
c           IN INTEGER,
position    IN INTEGER,
bl_tab      IN BLOB_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 5	dbms_sql.define_array (
c           IN INTEGER,
position    IN INTEGER,
cl_tab      IN CLOB_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 6	dbms_sql.define_array (
c           IN INTEGER,
position    IN INTEGER,
bf_tab      IN BFILE_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 7	dbms_sql.define_array (
c           IN INTEGER,
position    IN INTEGER,
ur_tab      IN UROWID_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 8	dbms_sql.define_array(
c           IN INTEGER,
position    IN INTEGER,
tm_tab      IN TIME_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 9	dbms_sql.define_array(
c           IN INTEGER,
position    IN INTEGER,
tms_tab     IN TIMESTAMP_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 10	dbms_sql.define_array(
c           IN INTEGER,
position    IN INTEGER,
ttz_tab     IN TIME_WITH_TIME_ZONE_TABLE,
cnt         IN INTEGER,
lower_bound IN INTEGER);
TBD
Overload 11	dbms_sql.define_array(
TBD
Overload 12	dbms_sql.define_array(
TBD
Overload 13	dbms_sql.define_array(
TBD
Overload 14	dbms_sql.define_array(
TBD
Overload 15	dbms_sql.define_array(
TBD
Overload 16	dbms_sql.define_array(
TBD
 
DEFINE_COLUMN
Defines a column to be selected from the given cursor, used only with SELECT statements

Overload 1	dbms_sql.define_column (
c        IN INTEGER,
position IN INTEGER,
column   IN NUMBER)
See final demo
Overload 2	dbms_sql.define_column (
c           IN INTEGER,
position    IN INTEGER,
column      IN VARCHAR2 CHARACTER SET ANY_CS,
column_size IN INTEGER);
TBD
Overload 3	dbms_sql.define_column (
c           IN INTEGER,
position    IN INTEGER,
column      IN DATE);
TBD
Overload 4	dbms_sql.define_column (
c           IN INTEGER,
position    IN INTEGER,
column      IN BLOB);
TBD
Overload 5	dbms_sql.define_column (
c           IN INTEGER,
position    IN INTEGER,
column      IN CLOB);
TBD
Overload 6	dbms_sql.define_column (
c           IN INTEGER,
position    IN INTEGER,
column      IN BFILE);
TBD
Overload 7	dbms_sql.define_column (
c           IN INTEGER,
position    IN INTEGER,
column      IN UROWID);
TBD
Overload 8	dbms_sql.define_column(
c           IN INTEGER,
position    IN INTEGER,
column      IN TIME_UNCONSTRAINED);
TBD
Overload 9	dbms_sql.define_column(
c           IN INTEGER,
position    IN INTEGER,
column      IN TIMESTAMP_UNCONSTRAINED);
TBD
Overload 10	dbms_sql.define_column(
c         IN INTEGER,
position  IN INTEGER,
column    IN TIME_TZ_UNCONSTRAINED);
TBD
Overload 11	dbms_sql.define_column(
TBD
Overload 12	dbms_sql.define_column(
TBD
Overload 13	dbms_sql.define_column(
TBD
Overload 14	dbms_sql.define_column(
TBD
Overload 15	dbms_sql.define_column(
TBD
Overload 16	dbms_sql.define_column(
TBD
Overload 17	dbms_sql.define_column(
TBD
Overload 18	dbms_sql.define_column(
TBD
Overload 19	dbms_sql.define_column(
TBD
Overload 20	dbms_sql.define_column(
TBD
Overload 21	dbms_sql.define_column(
TBD
 
DEFINE_COLUMN_CHAR
Undocumented	dbms_sql.define_column_char(
c           IN INTEGER,
position    IN INTEGER,
column      IN CHAR CHARACTER SET ANY_CS,
column_size IN INTEGER);
See define_column in final demo
 
DEFINE_COLUMN_LONG
Defines a LONG column to be selected from the given cursor, used only with SELECT statements	dbms_sql.define_column_long(c IN INTEGER, position IN INTEGER);
See define_column in final demo
 
DEFINE_COLUMN_RAW
Undocumented	dbms_sql.define_column_raw(
c           IN INTEGER,
position    IN INTEGER,
column      IN RAW,
column_size IN INTEGER);
See define_column in final demo
 
DEFINE_COLUMN_ROWID
Undocumented	dbms_sql.define_column_rowid(
c        IN INTEGER,
position IN INTEGER,
column   IN ROWID);
See define_column in final demo
 
DESCRIBE_COLUMNS

Describes the columns for a cursor opened and parsed through DBMS_SQL	dbms_sql.describe_columns(
c       IN  INTEGER,
col_cnt OUT INTEGER,
desc_t  OUT DESC_TAB);
DECLARE
 c       NUMBER;
 d       NUMBER;
 col_cnt PLS_INTEGER;
 f       BOOLEAN;
 rec_tab dbms_sql.desc_tab;
 col_num NUMBER;

PROCEDURE print_rec(rec in dbms_sql.desc_rec) IS
BEGIN
  dbms_output.new_line;
  dbms_output.put_line('col_type = ' || rec.col_type);
  dbms_output.put_line('col_maxlen = ' || rec.col_max_len);
  dbms_output.put_line('col_name = ' || rec.col_name);
  dbms_output.put_line('col_name_len = ' || rec.col_name_len);
  dbms_output.put_line('col_schema_name= ' || rec.col_schema_name);
  dbms_output.put_line('col_schema_name_len= ' || rec.col_schema_name_len);
  dbms_output.put_line('col_precision = ' || rec.col_precision);
  dbms_output.put_line('col_scale = ' || rec.col_scale);
  dbms_output.put('col_null_ok = ');

  IF (rec.col_null_ok) THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;

BEGIN
  c := dbms_sql.open_cursor; 
  dbms_sql.parse(c,'select * from scott.bonus',dbms_sql.NATIVE); 
  d := dbms_sql.execute(c); 
  dbms_sql.describe_columns(c, col_cnt, rec_tab);

/*
Following loop could simply be for j in 1..col_cnt loop.
Here we are simply illustrating some of the PL/SQL table features.
*/
  col_num := rec_tab.first;

  IF (col_num IS NOT NULL) THEN
    LOOP
      print_rec(rec_tab(col_num));
      col_num := rec_tab.next(col_num);
    EXIT WHEN (col_num is null);
    END LOOP;
  END IF;

  dbms_sql.close_cursor(c);
END;
/
 
DESCRIBE_COLUMNS2
Describes the specified column, an alternative method fixing a bug	dbms_sql.describe_columns2(
c         IN  INTEGER,
col_cnt   OUT INTEGER,
desc_tab2 OUT DESC_TAB2);
TBD
 
DESCRIBE_COLUMNS3 (new 11g)
Describes the specified column, an alternative method	dbms_sql.describe_columns2(
c       IN  INTEGER,
col_cnt OUT INTEGER,
desc_t  OUT DESC_TAB3);
TBD
 
EXECUTE

Execute dynamic SQL cursor	dbms_sql.execute(c IN INTEGER) RETURN INTEGER;
DECLARE
 sqlstr  VARCHAR2(50);
 tCursor PLS_INTEGER;
 RetVal  NUMBER;
BEGIN
  sqlstr := 'DROP SYNONYM my_synonym';
  tCursor := dbms_sql.open_cursor;
  dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
  RetVal := dbms_sql.execute(tCursor);
  dbms_sql.close_cursor(tCursor);
END;
/
 
EXECUTE_AND_FETCH
Executes a given cursor and fetch rows	dbms_sql.execute_and_fetch(
c     IN INTEGER,
exact IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;
Combine demo w/ last_row_count and last_row_id demos
 
FETCH_ROWS
Fetches a row from a given cursor	dbms_sql.fetch_rows(c IN INTEGER) RETURN INTEGER;
See final demo
 
IS_OPEN

Determine whether a  cursor is open	dbms_sql.is_open(c IN INTEGER) RETURN BOOLEAN;
set serveroutput on

DECLARE
 tCursor PLS_INTEGER;
BEGIN
  tCursor := dbms_sql.open_cursor;

  IF dbms_sql.is_open(tCursor) THEN
    dbms_output.put_line('1-OPEN');
  ELSE
    dbms_output.put_line('1-CLOSED');
  END IF;

  dbms_sql.close_cursor(tCursor);

  IF dbms_sql.is_open(tCursor) THEN
    dbms_output.put_line('2-OPEN');
  ELSE
    dbms_output.put_line('2-CLOSED');
  END IF;
END;
/
 
LAST_ERROR_POSITION
Returns byte offset in the SQL statement text where the error occurred	dbms_sql.last_error_position RETURN INTEGER;
TBD
 
LAST_ROW_COUNT
Returns cumulative count of the number of rows fetched	dbms_sql.last_row_count RETURN INTEGER;
TBD
 
LAST_ROW_ID
Returns ROWID of last row processed	dbms_sql.last_row_id RETURN ROWID;
TBD
 
LAST_SQL_FUNCTION_CODE
Returns SQL function code for statement	dbms_sql.last_sql_function_code RETURN INTEGER;
TBD
 
OPEN_CURSOR
Open dynamic SQL cursor and return cursor ID number of new cursor

Overload 1	dbms_sql.open_cursor RETURN INTEGER;
See is_open demo
Overload 2	dbms_sql.open_cursor ??? HELP
TBD
 
PARSE

Parse statement

Overload 1	dbms_sql.parse(
c             IN INTEGER,
statement     IN VARCHAR2,
language_flag IN INTEGER);
CREATE SYNONYM test_syn FOR dual;

SELECT *
FROM test_syn;

SELECT synonym_name
FROM user_synonyms;

DECLARE
  sqlstr  VARCHAR2(50);
  tCursor PLS_INTEGER;
BEGIN
  sqlstr := 'DROP SYNONYM test_syn';
  tCursor := dbms_sql.open_cursor;
  dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
  dbms_sql.close_cursor(tCursor);
END;
/

SELECT synonym_name
FROM user_synonyms;
-- with returning clause

CREATE OR REPLACE PROCEDURE single_row_insert(c1 NUMBER, c2 NUMBER, r OUT NUMBER) IS
 c NUMBER;
 n NUMBER;
BEGIN
  c := dbms_sql.open_cursor;

  dbms_sql.parse(c, 'INSERT INTO tab VALUES (:bnd1, :bnd2) ' ||
  'returning c1*c2 into :bnd3', 2);

  dbms_sql.bind_variable(c, 'bnd1', c1);
  dbms_sql.bind_variable(c, 'bnd2', c2);
  dbms_sql.bind_variable(c, 'bnd3', r);

  n := dbms_sql.execute(c);

  dbms_sql.variable_value(c, 'bnd3', r); -- get value of outbind
  dbms_sql.close_cursor(c);
END single_row_insert;
/
Overload 2	dbms_sql.parse(
c             IN INTEGER,
statement     IN VARCHAR2A,
lb            IN INTEGER,
ub            IN INTEGER,
lfflg         IN BOOLEAN,
language_flag IN INTEGER);
TBD
Overload 3	dbms_sql.parse(
c             IN INTEGER,
statement     IN VARCHAR2S,
lb            IN INTEGER,
ub            IN INTEGER,
lfflg         IN BOOLEAN,
language_flag IN INTEGER); 
TBD
Overload 4	dbms_sql.parse(
TBD
Overload 5	dbms_sql.parse(
TBD
Overload 6	dbms_sql.parse(
TBD
Overload 7	dbms_sql.parse(
TBD
Overload 8	dbms_sql.parse(
TBD
Overload 9	dbms_sql.parse(
TBD
Overload 10	dbms_sql.parse(
TBD
Overload 11	dbms_sql.parse(
TBD
Overload 12	dbms_sql.parse(
TBD
 
TO_CURSOR_NUMBER
Takes a REF CURSOR generated by TO_REFCURSOR and returns its handle.	dbms_sql.rc in out sys_refcursor) RETURN INTEGER;
TBD
 
TO_REFCURSOR
Takes a DBMS_SQL OPENed, PARSEd, and EXECUTEd cursor
and transforms/migrates it into a  PL/SQL manageable REF CURSOR.	dbms_sql.cursor_number IN OUT INTEGER) RETURN SYS_REFCURSOR;
TBD
 
VARIABLE_VALUE
Returns value of named variable for given cursor

Overload 1	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT NUMBER);
TBD
Overload 2	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT VARCHAR2 CHARACTER SET ANY_CS);
TBD
Overload 3	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT DATE);
TBD
Overload 4	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT BLOB);
TBD
Overload 5	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT CLOB CHARACTER SET ANY_CS);
TBD
Overload 6	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT BFILE);
TBD
Overload 7	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN VARCHAR2,
value IN NUMBER_TABLE);
TBD
Overload 8	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN VARCHAR2,
value IN VARCHAR2_TABLE);
TBD
Overload 9	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN VARCHAR2,
value IN DATE_TABLE);
TBD
Overload 10	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN VARCHAR2,
value IN BLOB_TABLE);
TBD
Overload 11	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN VARCHAR2,
value IN CLOB_TABLE);
TBD
Overload 12	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN VARCHAR2,
value IN BFILE_TABLE);
TBD
Overload 13	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT UROWID);
TBD
Overload 14	dbms_sql.variable_value(
c     IN         INTEGER,
name  IN         VARCHAR2,
value OUT NOCOPY UROWID_TABLE);
TBD
Overload 15	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT TIME_UNCONSTRAINED);
TBD
Overload 16	dbms_sql.variable_value(
c     IN         INTEGER,
name  IN         VARCHAR2,
value OUT NOCOPY TIME_TABLE);
TBD
Overload 17	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT TIMESTAMP_TABLE);
TBD
Overload 18	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT NOCOPY TIMESTAMP_TABLE);
TBD
Overload 19	dbms_sql.variable_value(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT TIME_TZ_UNCONSTRAINED);
TBD
Overload 20	dbms_sql.variable_value(
TBD
Overload 21	dbms_sql.variable_value(
TBD
Overload 22	dbms_sql.variable_value(
TBD
Overload 23	dbms_sql.variable_value(
TBD
Overload 24	dbms_sql.variable_value(
TBD
Overload 25	dbms_sql.variable_value(
TBD
Overload 26	dbms_sql.variable_value(
TBD
Overload 27	dbms_sql.variable_value(
TBD
Overload 28	dbms_sql.variable_value(
TBD
Overload 29	dbms_sql.variable_value(
TBD
Overload 30	dbms_sql.variable_value(
TBD
Overload 31	dbms_sql.variable_value(
TBD
Overload 32	dbms_sql.variable_value(
TBD
Overload 33	dbms_sql.variable_value(
TBD
Overload 34	dbms_sql.variable_value(
TBD
Overload 35	dbms_sql.variable_value(
TBD
Overload 36	dbms_sql.variable_value(
TBD
Overload 37	dbms_sql.variable_value(
TBD
 
VARIABLE_VALUE_CHAR
Undocumented	dbms_sql.variable_value_char(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT CHAR CHARACTER SET ANY_CS);
TBD
 
VARIABLE_VALUE_RAW
Undocumented	dbms_sql.variable_value_raw(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT RAW);
TBD
 
VARIABLE_VALUE_ROWID
Undocumented	dbms_sql.variable_value_rowid(
c     IN  INTEGER,
name  IN  VARCHAR2,
value OUT ROWID);
TBD
 
Demos

Drop Synonym Demo	SELECT synonym_name 
FROM user_synonyms;

CREATE SYNONYM d FOR dept;
CREATE SYNONYM e FOR emp;

SELECT synonym_name 
FROM user_synonyms;
DECLARE
 CURSOR syn_cur IS
 SELECT synonym_name
 FROM user_synonyms;

 RetVal  NUMBER;
 sqlstr  VARCHAR2(200);
 tCursor PLS_INTEGER;
BEGIN
  FOR syn_rec IN syn_cur
  LOOP
    sqlstr := 'DROP SYNONYM ' || syn_rec.synonym_name;
    tCursor := dbms_sql.open_cursor;
    dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE);
    RetVal := dbms_sql.execute(tCursor);
    dbms_sql.close_cursor(tCursor);
  END LOOP;
END dropsyn;
/
SELECT synonym_name 
FROM user_synonyms;

Executing CLOBS Demo Tables	CREATE TABLE workstations (
srvr_id     NUMBER(10),
ws_id       NUMBER(10),
location_id NUMBER(10),
cust_id     VARCHAR2(15),
status      VARCHAR2(1),
latitude    FLOAT(20),
longitude   FLOAT(20),
netaddress  VARCHAR2(15));

CREATE TABLE test (test VARCHAR2(50));

Demonstration dynamic SQL	CREATE OR REPLACE PROCEDURE execute_plsql_block(plsql_code_block CLOB) IS

ds_cur    PLS_INTEGER := dbms_sql.open_cursor;
sql_table dbms_sql.VARCHAR2S;

c_buf_len CONSTANT BINARY_INTEGER := 256;
v_accum   INTEGER := 0;
v_beg     INTEGER := 1;
v_end     INTEGER := 256;
v_loblen  PLS_INTEGER;
v_RetVal  PLS_INTEGER;

---------------------------
-- local function to the execute_plsql_block procedure
FUNCTION next_row(
 clob_in IN CLOB,
 len_in IN INTEGER,
 off_in IN INTEGER) RETURN VARCHAR2 IS
BEGIN
  RETURN DBMS_LOB.SUBSTR(clob_in, len_in, off_in);
END next_row;
---------------------------

BEGIN
  v_loblen := DBMS_LOB.GETLENGTH(plsql_code_block);
  INSERT INTO test
  (test)
  VALUES
  ('Demo block is ' || TO_CHAR(v_loblen) || ' bytes in length');
  COMMIT;

  LOOP
    -- Set the length to the remaining size 
    -- if there are < c_buf_len characters remaining.
    IF v_accum + c_buf_len > v_loblen THEN
      v_end := v_loblen - v_accum;
    END IF;

    sql_table(NVL(sql_table.LAST, 0) + 1) :=
    next_row(plsql_code_block, v_end, v_beg);

    v_beg := v_beg + c_BUF_LEN;
    v_accum := v_accum + v_end;

    IF v_accum >= v_loblen THEN
      EXIT;
    END IF;
  END LOOP;

  -- Parse the pl/sql and execute it
   dbms_sql.parse(ds_cur, sql_table, sql_table.FIRST, sql_table.LAST,
  FALSE, dbms_sql.NATIVE);

  v_RetVal := dbms_sql.execute(ds_cur);

   dbms_sql.close_cursor(ds_cur); 
END execute_plsql_block;
/

Executing CLOBS Demo Data	SQL> commit;

SQL> ed

-- delete "commit" and paste in colored section
DECLARE
  clob_in CLOB;
BEGIN
  clob_in := CAST( 'BEGIN
INSERT INTO WORKSTATIONS VALUES (1,1,20075,'''',''Y'',32.97948,-117.2569,'''');
INSERT INTO WORKSTATIONS VALUES (1,10,20077,'''',''N'',32.97125,-117.2675,'''');
INSERT INTO WORKSTATIONS VALUES (1,11,20078,'''',''N'',33.03865,-96.83579,'''');
INSERT INTO WORKSTATIONS VALUES (1,12,20079,'''',''Y'',32.97413,-117.2694,''10.128.48.121'');
INSERT INTO WORKSTATIONS VALUES (1,2,20081,'''',''N'',32.97948,-117.2569,'''');
INSERT INTO WORKSTATIONS VALUES (1,3,20082,'''',''Y'',32.97948,-117.2569,''10.128.0.1'');
INSERT INTO WORKSTATIONS VALUES (1,4,20083,''15689'',''N'',32.98195,-117.2636,'''');
INSERT INTO WORKSTATIONS VALUES (1,5,20085,'''',''Y'',32.98195,-117.2636,''10.128.16.105'');
INSERT INTO WORKSTATIONS VALUES (1,6,20086,'''',''N'',32.97096,-117.2689,'''');
INSERT INTO WORKSTATIONS VALUES (1,7,20077,'''',''Y'',32.97125,-117.2675,''10.128.48.105'');
INSERT INTO WORKSTATIONS VALUES (1,8,20090,'''',''N'',32.97124,-117.2676,'''');
INSERT INTO WORKSTATIONS VALUES (1,9,20092,'''',''N'',32.97023,-117.2688,'''');
INSERT INTO WORKSTATIONS VALUES (10,1,20094,'''',''Y'',61.2224,-149.8047,''10.128.112.1'');
INSERT INTO WORKSTATIONS VALUES (10,2,20095,'''',''N'',61.2224,-149.8047,'''');
INSERT INTO WORKSTATIONS VALUES (10,3,20096,'''',''Y'',61.2224,-149.8047,''10.128.112.113'');
INSERT INTO WORKSTATIONS VALUES (10,4,13545,'''',''Y'',61.14104,-149.9519,''10.128.112.121'');
INSERT INTO WORKSTATIONS VALUES (10,5,20104,'''',''N'',61.2224,-149.8047,'''');
INSERT INTO WORKSTATIONS VALUES (10,6,20106,'''',''Y'',61.21685,-149.8002,''10.128.80.113'');
INSERT INTO WORKSTATIONS VALUES (11,1,20110,'''',''N'',61.137,-149.9395,'''');
INSERT INTO WORKSTATIONS VALUES (11,10,20113,'''',''N'',61.14104,-149.9519,'''');
INSERT INTO WORKSTATIONS VALUES (11,11,20116,'''',''N'',61.14104,-149.9519,'''');
INSERT INTO WORKSTATIONS VALUES (11,12,20117,'''',''Y'',61.137,-149.9395,''10.128.32.193'');
INSERT INTO WORKSTATIONS VALUES (11,13,20118,'''',''Y'',61.137,-149.9395,''10.128.16.129'');
INSERT INTO WORKSTATIONS VALUES (11,14,20119,'''',''Y'',61.137,-149.9395,'''');
INSERT INTO WORKSTATIONS VALUES (11,15,20121,'''',''N'',61.13695,-149.9396,'''');
INSERT INTO WORKSTATIONS VALUES (11,16,20122,'''',''N'',61.13695,-149.9396,'''');
INSERT INTO WORKSTATIONS VALUES (11,17,13545,'''',''Y'',61.14104,-149.9519,'''');
INSERT INTO WORKSTATIONS VALUES (11,18,19922,'''',''Y'',61.13549,-149.959,''10.128.48.153'');
INSERT INTO WORKSTATIONS VALUES (11,19,19923,'''',''N'',61.13422,-149.962,'''');
INSERT INTO WORKSTATIONS VALUES (11,2,19924,'''',''N'',61.137,-149.9395,'''');
INSERT INTO WORKSTATIONS VALUES (11,20,19925,'''',''N'',61.146,-149.9799,'''');
INSERT INTO WORKSTATIONS VALUES (11,21,19926,'''',''N'',61.146,-149.9799,'''');
INSERT INTO WORKSTATIONS VALUES (11,22,19927,'''',''N'',61.13695,-149.9396,'''');
INSERT INTO WORKSTATIONS VALUES (11,23,19928,'''',''N'',61.14104,-149.9519,'''');
INSERT INTO WORKSTATIONS VALUES (11,24,19930,'''',''N'',61.13422,-149.962,'''');
INSERT INTO WORKSTATIONS VALUES (11,25,19931,'''',''N'',61.13678,-149.9644,'''');
INSERT INTO WORKSTATIONS VALUES (11,26,20033,'''',''N'',61.14477,-149.9586,'''');
INSERT INTO WORKSTATIONS VALUES (11,27,20034,'''',''N'',61.13466,-149.975,'''');
INSERT INTO WORKSTATIONS VALUES (11,28,20035,'''',''N'',61.14142,-149.9668,'''');
INSERT INTO WORKSTATIONS VALUES (11,29,20036,'''',''N'',61.13695,-149.9396,'''');
INSERT INTO WORKSTATIONS VALUES (11,3,20037,'''',''Y'',61.137,-149.9395,''10.128.16.105''); 
INSERT INTO WORKSTATIONS VALUES (11,30,20038,'''',''N'',61.13695,-149.9396,'''');
INSERT INTO WORKSTATIONS VALUES (11,31,20039,'''',''N'',61.13695,-149.9396,'''');
INSERT INTO WORKSTATIONS VALUES (11,32,20040,'''',''N'',61.13695,-149.9396,'''');
INSERT INTO WORKSTATIONS VALUES (11,33,20042,'''',''N'',61.12887,-149.9578,'''');
INSERT INTO WORKSTATIONS VALUES (11,4,20043,'''',''N'',61.137,-149.9395,'''');
INSERT INTO WORKSTATIONS VALUES (11,5,20044,'''',''Y'',61.137,-149.9395,''10.128.32.129''); 
INSERT INTO WORKSTATIONS VALUES (11,6,20045,'''',''N'',61.137,-149.9395,'''');
INSERT INTO WORKSTATIONS VALUES (11,7,20046,'''',''N'',61.137,-149.9395,'''');
INSERT INTO WORKSTATIONS VALUES (11,8,20047,'''',''N'',61.137,-149.9395,'''');
--====
INSERT INTO WORKSTATIONS VALUES (11,9,20048,'''',''Y'',61.137,-149.9395,''10.128.32.169''); 
INSERT INTO WORKSTATIONS VALUES (12,1,20051,''15706'',''Y'',32.75604,-117.1201,''''); 
INSERT INTO WORKSTATIONS VALUES (12,10,20053,'''',''N'',32.75689,-117.12,''''); 
INSERT INTO WORKSTATIONS VALUES (12,100,20054,'''',''N'',32.7596,-117.124,'''');
INSERT INTO WORKSTATIONS VALUES (12,101,20056,'''',''N'',32.75689,-117.129,'''');
INSERT INTO WORKSTATIONS VALUES (12,102,20057,'''',''Y'',32.75677,-117.1241,''10.129.112.25''); 
INSERT INTO WORKSTATIONS VALUES (12,103,20058,'''',''Y'',32.75662,-117.124,''10.129.112.33'');
INSERT INTO WORKSTATIONS VALUES (12,104,20060,'''',''N'',32.7571,-117.1242,'''');
INSERT INTO WORKSTATIONS VALUES (12,105,20061,'''',''N'',32.75316,-117.1253,'''');
INSERT INTO WORKSTATIONS VALUES (12,106,20063,'''',''N'',32.76154,-117.1251,'''');
--====
COMMIT;
END;' AS CLOB);

execute_plsql_block(clob_in);

END;
/
set linesize 121

SELECT * FROM workstations;


Final Demo	CREATE TABLE one (
id    NUMBER(5),
name  VARCHAR2(30),
dob   DATE);

CREATE TABLE two (
id    NUMBER(5),
name  VARCHAR2(30),
dob   DATE);

INSERT INTO one
(id, name, dob)
VALUES
(100, 'Dan Morgan', SYSDATE+10);

INSERT INTO one
(id, name, dob)
VALUES
(200, 'Joze Senegacnik', SYSDATE-10);
COMMIT;


CREATE OR REPLACE PROCEDURE copy_proc(src_tab VARCHAR2,
dest_tab VARCHAR2) IS

col1     NUMBER(5);
col2     VARCHAR2(30);
col3     DATE;
src_cur  INTEGER;
dest_cur INTEGER;
ignore   INTEGER;

BEGIN
  -- open cursor on source table
  src_cur := dbms_sql.open_cursor;

  -- parse the SELECT statement
  dbms_sql.parse(src_cur, 'SELECT id, name, dob FROM ' || src_tab,
  dbms_sql.NATIVE);

  -- define the column type 
  dbms_sql.define_column(src_cur, 1, col1);
  dbms_sql.define_column(src_cur, 2, col2, 30);
  dbms_sql.define_column(src_cur, 3, col3);

  ignore := dbms_sql.execute(src_cur);

  -- open cursor on destination table
  dest_cur := dbms_sql.open_cursor;

  -- parse the INSERT statement
  dbms_sql.parse(dest_cur, 'INSERT INTO ' || dest_tab || ' VALUES
  (:n_bind, :c_bind, :d_bind)', dbms_sql.NATIVE);

    LOOP
      -- Fetch a row from the source table
      IF dbms_sql.fetch_rows(src_cur) > 0 THEN
        -- get column values of the row
        dbms_sql.column_value(src_cur, 1, col1);
        dbms_sql.column_value(src_cur, 2, col2);
        dbms_sql.column_value(src_cur, 3, col3);

        -- bind in the values to be inserted
        dbms_sql.bind_variable(dest_cur, ':n_bind', col1);
        dbms_sql.bind_variable(dest_cur, ':c_bind', col2);
        dbms_sql.bind_variable(dest_cur, ':d_bind', col3);
        ignore := dbms_sql.execute(dest_cur);
      ELSE
        -- No more rows to copy
        EXIT;
      END IF;
    END LOOP;

  -- Commit and close all cursors
  COMMIT;
  dbms_sql.close_cursor(src_cur);
  dbms_sql.close_cursor(dest_cur);
EXCEPTION
  WHEN OTHERS THEN
    IF dbms_sql.is_open(src_cur) THEN
      dbms_sql.close_cursor(src_cur);
    END IF; 
    IF dbms_sql.is_open(dest_cur) THEN
      dbms_sql.close_cursor(dest_cur);
    END IF;
    RAISE;
END copy_proc;
/

exec copy_proc('ONE', 'TWO');

SELECT * FROM one;
SELECT * FROM two;

Bulk Insert Demo	CREATE TABLE obj (
objno   INTEGER,
objname VARCHAR2(30));


CREATE OR REPLACE FUNCTION objname(rnum INTEGER) RETURN VARCHAR2 IS
 retval VARCHAR2(30);
BEGIN
  SELECT object_name
  INTO retval
  FROM (
    SELECT rownum X, object_name
    FROM all_objects
    WHERE rownum < 11)
  WHERE x = rnum+1;
  RETURN retval;
END objname;
/


DECLARE
 sqlstr        VARCHAR2(200);
 objno_array   dbms_sql.number_table;
 objname_array dbms_sql.varchar2_table;
 handle        NUMBER;
 dummy         NUMBER;
BEGIN
  FOR i IN 0..9
  LOOP
    objno_array(i) := 1000 + i;
    objname_array(i) := objname(i);
  END LOOP;

  sqlstr := 'INSERT INTO obj VALUES (:num_array, :name_array)';
  handle := dbms_sql.open_cursor;
  dbms_sql.parse(handle, sqlstr, dbms_sql.NATIVE);
  dbms_sql.bind_array(handle, ':num_array', objno_array);
  dbms_sql.bind_array(handle, ':name_array', objname_array);
  dummy := dbms_sql.execute(handle);
  dbms_sql.close_cursor(handle);

EXCEPTION
  WHEN OTHERS THEN
    IF dbms_sql.is_open(handle) THEN
      dbms_sql.close_cursor(handle);
    END IF;
    RAISE;
END;
/

SELECT * FROM obj;

0
顶
0
踩
分享到：
| ORA-02449错误
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    db_mysql.class.zip_SQL_Server_PHP_

    6. **预处理语句**：使用预处理语句可以防止SQL注入，类库可能提供`prepare()`和`execute()`方法来创建和执行预编译的SQL语句。 7. **连接池**：如果应用需要频繁地打开和关闭数据库连接，类库可能会实现连接池，以...

    ThinkPHP2.0读取MSSQL提示Incorrect syntax near the keyword 'AS'的解决方法

    具体而言，需要修改框架中的DbMssql.class.php文件，这是ThinkPHP对MSSQL数据库操作的驱动文件。修改的步骤包括在驱动文件的第25行添加注释符号'//'来禁用有问题的查询语句生成部分，并且修改parseLimit方法，将第...

    外加热强制循环蒸发器装配图（CAD).rar

    外加热强制循环蒸发器装配图（CAD).rar

    数控车床纵向进给系统设计.zip

    数控车床纵向进给系统设计.zip

    vault_side_off_ominous.png

    j

    爬虫 bangumi名称和评论数

    爬虫 bangumi名称和评论数

    基于SpringBoot的垃圾分类回收系统(源码+数据库+万字文档)526

    基于SpringBoot的垃圾分类回收系统，系统包含两种角色：管理员、用户主要功能如下。 【用户功能】 首页：浏览垃圾分类回收系统信息。 个人中心：管理个人信息，查看历史记录和订单状态。 运输管理：查看运输信息，垃圾回收的时间和地点。 公告管理：阅读系统发布的相关通知和公告。 垃圾回收管理：查看垃圾回收的信息，回收类型和进度。 垃圾出库申请管理：提交和查看垃圾出库申请的状态。 【管理员功能】 首页：查看垃圾分类回收系统。 个人中心：管理个人信息。 管理员管理：审核和管理注册管理员用户的信息。 用户管理：审核和管理注册用户的信息。 运输管理：监管和管理系统中的运输信息。 公告管理：发布、编辑和删除系统的通知和公告。 垃圾回收管理：监管和管理垃圾回收的信息。 垃圾出库申请管理：审批和管理用户提交的垃圾出库申请。 基础数据管理：管理系统的基础数据，运输类型、公告类型和垃圾回收类型。 二、项目技术 编程语言：Java 数据库：MySQL 项目管理工具：Maven 前端技术：Vue 后端技术：SpringBoot 三、运行环境 操作系统：Windows、macOS都可以 JDK版本：JDK1.8以上都可以 开发工具：IDEA、Ecplise、Myecplise都可以 数据库: MySQL5.7以上都可以 Maven：任意版本都可以

    这篇文章是台湾大学（NTU）计算机科学与信息工程系（CSIE）2021年秋季学期算法设计与分析课程的第一份作业（Homework#1）的具体要求和题目描述 以下是主要内容的总结：

    内容概要：本文档是台湾大学计算机科学与信息工程系2021年秋季学期《算法设计与分析》课程的第一次作业（Homework#1）。作业包含四道编程题和三道手写题，旨在考察学生对算法设计和分析的理解与应用能力。编程题涉及汉诺塔、数组计算、矩形点对、糖果分配等问题；手写题涵盖渐近符号证明、递归方程求解、幽灵腿游戏优化、不公平的卢卡斯问题等。文档详细描述了每个问题的具体要求、输入输出格式、测试用例以及评分标准。此外，还提供了编程技巧和注意事项，如避免延迟提交、正确引用资料、处理大输入文件等。 适合人群：具备一定编程基础的本科生或研究生，特别是修读过或正在修读算法设计与分析相关课程的学生。 使用场景及目标：①帮助学生巩固课堂所学的算法理论知识；②通过实际编程练习提高解决复杂问题的能力；③为后续更深入的学习和研究打下坚实的基础。 其他说明：此作业强调团队合作和个人独立思考相结合的重要性，鼓励学生在讨论后用自己的语言表达解决方案，并注明参考资料。对于编程题，特别提醒学生注意输入文件可能较大，建议采取适当的优化措施以确保程序运行效率。

    基于SpringBoot的铁路订票管理系统(源码+数据库+万字文档+ppt)528

    基于SpringBoot的铁路订票管理系统，系统包含两种角色：管理员、用户主要功能如下。 【用户功能】 首页：浏览铁路订票管理系统的主要信息。 火车信息：查看火车的相关信息，包括车次、出发地、目的地和票价等。 公告资讯：阅读系统发布的相关通知和资讯。 后台管理：进行系统首页、个人中心、车票预订管理、车票退票管理等操作。 个人中心：管理个人信息，查看订单历史记录等。 【管理员功能】 首页：查看铁路订票管理系统。 个人中心：修改密码、管理个人信息。 用户管理：审核和管理注册用户的信息。 火车类型管理：管理系统中的火车类型信息。 火车信息管理：监管和管理系统中的火车信息，添加、编辑、删除等。 车票预订管理：处理用户的车票预订请求。 车票退票管理：处理用户的车票退票请求。 系统管理：管理系统的基本设置，公告资讯、关于我们、系统简介和轮播图管理。 二、项目技术 编程语言：Java 数据库：MySQL 项目管理工具：Maven 前端技术：Vue 后端技术：SpringBoot 三、运行环境 操作系统：Windows、macOS都可以 JDK版本：JDK1.8以上都可以 开发工具：IDEA、Ecplise、Myecplise都可以 数据库: MySQL5.7以上都可以 Maven：任意版本都可以

    塑料架注射模具设计.rar

    塑料架注射模具设计.rar

    基于json文件数据驱动的的接口测试框架.zip

    基于json文件数据驱动的的接口测试框架

    铁丝缠绕包装机设计-缠绕盘设计.rar

    铁丝缠绕包装机设计-缠绕盘设计.rar

    Linux操作系统及常用命令详解.zip

    linux

    圆柱体相贯线焊接专机工作台设计.rar

    圆柱体相贯线焊接专机工作台设计.rar

    硬币分拣机设计.rar

    硬币分拣机设计.rar

    【机器学习与数据挖掘】行业级机器学习软件开发经验与教训：从LIBSVM和LIBLINEAR看算法部署及软件设计挑战

    内容概要：本文探讨了开发行业级机器学习和数据挖掘软件的经验与教训，指出当前研究界与工业界之间的脱节问题。作者分享了开发LIBSVM和LIBLINEAR的经验，强调了用户需求的重要性。大多数用户并非机器学习专家，期望简单易用的工具来获得良好结果。文章还详细介绍了支持向量机（SVM）的实际应用案例，包括数据预处理（如特征缩放）、参数选择等步骤，并提出了为初学者设计的简易流程。此外，作者讨论了在设计机器学习软件时应考虑的功能选择、选项数量、性能优化与数值稳定性等问题，强调了软件开发与实验代码的区别以及鼓励研究人员参与高质量软件开发的重要性。 适合人群：对机器学习软件开发感兴趣的科研人员、工程师及从业者，尤其是那些希望了解如何将学术研究成果转化为实际可用工具的人士。 使用场景及目标：①帮助非机器学习专家的用户更好地理解和使用机器学习方法；②指导开发者在设计机器学习软件时考虑用户需求、功能选择、性能优化等方面的问题；③促进学术界与工业界之间的合作，推动高质量机器学习软件的发展。 其他说明：本文不仅提供了具体的开发经验和技巧，还呼吁建立激励机制，鼓励更多研究人员投入到机器学习软件的开发中，以解决当前存在的研究与应用脱节的问题。

    pandas学习代码，jypyter格式

    一天入门pandas代码

    joblib-0.12.0-py2.py3-none-any.whl

    该资源为joblib-0.12.0-py2.py3-none-any.whl，欢迎下载使用哦！

    深度学习基于PyTorch==2.6.0和Transformers==4.48.0的XTuner环境配置：AI模型开发与优化依赖库列表

    内容概要：本文档《xtuner_requirements.txt》列出了用于支持特定项目（可能是机器学习或深度学习项目）运行所需的所有Python包及其版本。其中不仅包括常见的数据处理和科学计算库如numpy、pandas，还包括了与深度学习密切相关的库如torch、transformers等。值得注意的是，文档中还特别指定了NVIDIA CUDA相关组件的具体版本，确保了GPU加速环境的一致性和兼容性。此外，文档中也包含了从GitHub直接安装的xtuner库，明确了具体的提交哈希值，保证了代码来源的精确性。 适合人群：对机器学习、深度学习领域有一定了解并需要搭建相应开发环境的研发人员，尤其是那些希望复现特定实验结果或基于已有模型进行二次开发的研究者和技术爱好者。 使用场景及目标：①帮助开发者快速搭建完整的开发环境，确保所有依赖项正确无误；②为研究人员提供一个稳定的实验平台，以便于重复实验和验证结果；③作为项目协作的基础，确保团队成员之间的环境一致性，减少因环境差异带来的问题。 阅读建议：由于该文档主要为技术性依赖列表，在阅读时应重点关注所需安装的库及其版本号，特别是CUDA相关组件和自定义库（如xtuner）的安装方式。对于非技术人员而言，可能需要额外查阅相关资料来理解各库的作用。同时，在实际操作过程中，建议按照文档中的顺序逐一安装依赖，避免版本冲突等问题的发生。

Magicbox
Global site tag (gtag.js) - Google Analytics