152 DBMS_SQL
152.9 Summary of DBMS_SQL Subprograms
This table lists the subprograms and briefly describes them.
Table 152-6 DBMS_SQL Package Subprograms
Subprogram
Description
BIND_ARRAY Procedures
Binds a given value to a given collection.
BIND_VARIABLE Procedures
Binds a given value to a given variable.
BIND_VARIABLE_PKG Procedure
Binds a given value to a given package variable.
CLOSE_CURSOR Procedure
Closes given cursor and frees memory.
COLUMN_VALUE Procedure
Returns value of the cursor element for a given position in a cursor.
COLUMN_VALUE_LONG Procedure
Returns a selected part of a column, that has been defined using .
DEFINE_ARRAY Procedure
Defines a collection to be selected from the given cursor, used only with statements.
DEFINE_COLUMN Procedures
Defines a column to be selected from the given cursor, used only with statements.
DEFINE_COLUMN_CHAR Procedure
Defines a column of type to be selected from the given cursor, used only with statements.
DEFINE_COLUMN_LONG Procedure
Defines a column to be selected from the given cursor, used only with statements.
DEFINE_COLUMN_RAW Procedure
Defines a column of type to be selected from the given cursor, used only with statements.
DEFINE_COLUMN_ROWID Procedure
Defines a column of type to be selected from the given cursor, used only with statements.
DESCRIBE_COLUMNS Procedure
Describes the columns for a cursor opened and parsed through .
DESCRIBE_COLUMNS2 Procedure
Describes the specified column, an alternative to DESCRIBE_COLUMNS Procedure.
DESCRIBE_COLUMNS3 Procedure
Describes the specified column, an alternative to DESCRIBE_COLUMNS Procedure.
EXECUTE Function
Executes a given cursor.
EXECUTE_AND_FETCH Function
Executes a given cursor and fetch rows.
FETCH_ROWS Function
Fetches a row from a given cursor.
GET_NEXT_RESULT Procedures
Gets the statement of the next result returned to the caller of the recursive statement or, if this caller sets itself as the client for the recursive statement, the next result returned to this caller as client.
IS_OPEN Function
Returns if given cursor is open.
LAST_ERROR_POSITION Function
Returns byte offset in the SQL statement text where the error occurred.
LAST_ROW_COUNT Function
Returns cumulative count of the number of rows fetched
LAST_ROW_ID Function
Returns of last row processed.
LAST_SQL_FUNCTION_CODE Function
Returns SQL function code for statement.
OPEN_CURSOR Functions
Returns cursor ID number of new cursor.
PARSE Procedures
Parses given statement.
RETURN_RESULT Procedures
Returns the result of an executed statement to the client application.
TO_CURSOR_NUMBER Function
Takes an ed strongly or weakly-typed ref cursor and transforms it into a cursor number.
TO_REFCURSOR Function
Takes an ed, d, and d cursor and transforms/migrates it into a PL/SQL manageable (a weakly-typed cursor) that can be consumed by PL/SQL native dynamic SQL switched to use native dynamic SQL.
VARIABLE_VALUE Procedures
Returns value of named variable for given cursor.
VARIABLE_VALUE_PKG Procedure
Returns value of named variable for given cursor. It is used to return the values of bind variables inside PL/SQL blocks or DML statements with returning clause for a declared package. The type of the variable must be declared in the package specification.
152.9.1 BIND_ARRAY Procedures
This procedure binds a given value or set of values to a given variable in a cursor, based on the name of the variable in the statement.
Syntax
DBMS_SQL.BIND_ARRAY (
c IN INTEGER,
name IN VARCHAR2,
<table_variable> IN <datatype>
[,index1 IN INTEGER,
index2 IN INTEGER)] );
Where the <> and its corresponding <> can be any one of the following matching pairs:
<clob_tab> Clob_Table
<bflt_tab> Binary_Float_Table
<bdbl_tab> Binary_Double_Table
<blob_tab> Blob_Table
<bfile_tab> Bfile_Table
<date_tab> Date_Table
<num_tab> Number_Table
<urowid_tab> Urowid_Table
<vchr2_tab> Varchar2_Table
<tm_tab> Time_Table
<ttz_tab> Time_With_Time_Zone_Table
<tms_tab> Timestamp_Table
<tstz_tab> Timestamp_With_ltz_Table
<tstz_tab> Timestamp_With_Time_Zone_Table
<ids_tab> Interval_Day_To_Second_Table
<iym_tab> Interval_Year_To_Month_Table
Notice that the procedure is overloaded to accept different datatype.
Parameters
Table 152-7 BIND_ARRAY Procedure Parameters
Parameter
Description
ID number of the cursor to which you want to bind a value.
Name of the collection in the statement.
Local variable that has been declared as <>.
Index for the table element that marks the lower bound of the range.
Index for the table element that marks the upper bound of the range.
Usage Notes
For binding a range, the table must contain the elements that specify the range — tab(index1) and tab(index2) — but the range does not have to be dense. Index1 must be less than or equal to index2. All elements between tab(index1) and tab(index2) are used in the bind.
If you do not specify indexes in the bind call, and two different binds in a statement specify tables that contain a different number of elements, then the number of elements actually used is the minimum number between all tables. This is also the case if you specify indexes — the minimum range is selected between the two indexes for all tables.
Not all bind variables in a query have to be array binds. Some can be regular binds and the same value are used for each element of the collections in expression evaluations (and so forth).
Bulk Array Binds
Bulk selects, inserts, updates, and deletes can enhance the performance of applications by bundling many calls into one. The package lets you work on collections of data using the PL/SQL table type.
Table items are unbounded homogeneous collections. In persistent storage, they are like other relational tables and have no intrinsic ordering. But when a table item is brought into the workspace (either by querying or by navigational access of persistent data), or when it is created as the value of a PL/SQL variable or parameter, its elements are given subscripts that can be used with array-style syntax to get and set the values of elements.
The subscripts of these elements need not be dense, and can be any number including negative numbers. For example, a table item can contain elements at locations -10, 2, and 7 only.
When a table item is moved from transient workspace to persistent storage, the subscripts are not stored; the table item is unordered in persistent storage.
At bind time the table is copied out from the PL/SQL buffers into local buffers (the same as for all scalar types) and then the table is manipulated from the local buffers. Therefore, if you change the table after the bind call, then that change does not affect the way the execute acts.
Types for Scalar and LOB Collections
You can declare a local variable as one of the following table-item types, which are defined as public types in .
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 bfile_table IS TABLE OF BFILE 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 timestamp_with_time_zone_Table
IS TABLE OF timestamp_tz_unconstrained
INDEX BY BINARY_INTEGER;
TYPE urowid_table IS TABLE OF UROWID INDEX BY BINARY_INTEGER;
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
Example 152-1 Examples Using Bulk DML
This series of examples shows how to use bulk array binds (table items) in the SQL DML statements , and .
Here is an example of a bulk statement that demonstrates adding seven new employees to the table:
DECLARE
stmt VARCHAR2(200);
empno_array DBMS_SQL.NUMBER_TABLE;
empname_array DBMS_SQL.VARCHAR2_TABLE;
jobs_array DBMS_SQL.VARCHAR2_TABLE;
mgr_array DBMS_SQL.NUMBER_TABLE;
hiredate_array DBMS_SQL.VARCHAR2_TABLE;
sal_array DBMS_SQL.NUMBER_TABLE;
comm_array DBMS_SQL.NUMBER_TABLE;
deptno_array DBMS_SQL.NUMBER_TABLE;
c NUMBER;
dummy NUMBER;
BEGIN
empno_array(1):= 9001;
empno_array(2):= 9002;
empno_array(3):= 9003;
empno_array(4):= 9004;
empno_array(5):= 9005;
empno_array(6):= 9006;
empno_array(7):= 9007;
empname_array(1) := ‘Dopey’;
empname_array(2) := ‘Grumpy’;
empname_array(3) := ‘Doc’;
empname_array(4) := ‘Happy’;
empname_array(5) := ‘Bashful’;
empname_array(6) := ‘Sneezy’;
empname_array(7) := ‘Sleepy’;
jobs_array(1) := ‘Miner’;
jobs_array(2) := ‘Miner’;
jobs_array(3) := ‘Miner’;
jobs_array(4) := ‘Miner’;
jobs_array(5) := ‘Miner’;
jobs_array(6) := ‘Miner’;
jobs_array(7) := ‘Miner’;
mgr_array(1) := 9003;
mgr_array(2) := 9003;
mgr_array(3) := 9003;
mgr_array(4) := 9003;
mgr_array(5) := 9003;
mgr_array(6) := 9003;
mgr_array(7) := 9003;
hiredate_array(1) := ’06-DEC-2006′;
hiredate_array(2) := ’06-DEC-2006′;
hiredate_array(3) := ’06-DEC-2006′;
hiredate_array(4) := ’06-DEC-2006′;
hiredate_array(5) := ’06-DEC-2006′;
hiredate_array(6) := ’06-DEC-2006′;
hiredate_array(7) := ’06-DEC-2006′;
sal_array(1):= 1000;
sal_array(2):= 1000;
sal_array(3):= 1000;
sal_array(4):= 1000;
sal_array(5):= 1000;
sal_array(6):= 1000;
sal_array(7):= 1000;
comm_array(1):= 0;
comm_array(2):= 0;
comm_array(3):= 0;
comm_array(4):= 0;
comm_array(5):= 0;
comm_array(6):= 0;
comm_array(7):= 0;
deptno_array(1):= 11;
deptno_array(2):= 11;
deptno_array(3):= 11;
deptno_array(4):= 11;
deptno_array(5):= 11;
deptno_array(6):= 11;
deptno_array(7):= 11;
stmt := ‘INSERT INTO emp VALUES(
:num_array, :name_array, :jobs_array, :mgr_array, :hiredate_array,
:sal_array, :comm_array, :deptno_array)’;
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_ARRAY(c, ‘:num_array’, empno_array);
DBMS_SQL.BIND_ARRAY(c, ‘:name_array’, empname_array);
DBMS_SQL.BIND_ARRAY(c, ‘:jobs_array’, jobs_array);
DBMS_SQL.BIND_ARRAY(c, ‘:mgr_array’, mgr_array);
DBMS_SQL.BIND_ARRAY(c, ‘:hiredate_array’, hiredate_array);
DBMS_SQL.BIND_ARRAY(c, ‘:sal_array’, sal_array);
DBMS_SQL.BIND_ARRAY(c, ‘:comm_array’, comm_array);
DBMS_SQL.BIND_ARRAY(c, ‘:deptno_array’, deptno_array);
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;
/
SHOW ERRORS;
Here is an example of a bulk statement that demonstrates updating salaries for four existing employees in the table:
DECLARE
stmt VARCHAR2(200);
empno_array DBMS_SQL.NUMBER_TABLE;
salary_array DBMS_SQL.NUMBER_TABLE;
c NUMBER;
dummy NUMBER;
BEGIN
empno_array(1):= 7369;
empno_array(2):= 7876;
empno_array(3):= 7900;
empno_array(4):= 7934;
salary_array(1) := 10000;
salary_array(2) := 10000;
salary_array(3) := 10000;
salary_array(4) := 10000;
stmt := ‘update emp set sal = :salary_array
WHERE empno = :num_array’;
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_ARRAY(c, ‘:num_array’, empno_array);
DBMS_SQL.BIND_ARRAY(c, ‘:salary_array’, salary_array);
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;
/
In a statement, for example, you could bind an array in the clause and have the statement be run for each element in the array:
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;
/
In the preceding example, only elements 1 through 4 are used as specified by the call. Each element of the array potentially deletes a large number of employees from the database.
152.9.2 BIND_VARIABLE Procedures
These procedures bind a given value or set of values to a given variable in a cursor, based on the name of the variable in the statement.
Syntax
DBMS_SQL.BIND_VARIABLE (
c IN INTEGER,
name IN VARCHAR2,
value IN <datatype>);
Where <datatype> can be any one of the following types:
ADT (user-defined object types)
BINARY_DOUBLE
BINARY_FLOAT
BFILE
BLOB
BOOLEAN
CLOB CHARACTER SET ANY_CS
DATE
DSINTERVAL_UNCONSTRAINED
NESTED table
NUMBER
OPAQUE types
REF
TIME_UNCONSTRAINED
TIME_TZ_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_UNCONSTRAINED
UROWID
VARCHAR2 CHARACTER SET ANY_CS
VARRAY
YMINTERVAL_UNCONSTRAINED
Notice that is overloaded to accept different datatype.
The following syntax is also supported for . The square brackets [] indicate an optional parameter for the procedure.
DBMS_SQL.BIND_VARIABLE (
c IN INTEGER,
name IN VARCHAR2,
value IN VARCHAR2 CHARACTER SET ANY_CS [,out_value_size IN INTEGER]);
To bind , , and data, you can use the following variations on the syntax:
DBMS_SQL.BIND_VARIABLE_CHAR (
c IN INTEGER,
name IN VARCHAR2,
value IN CHAR CHARACTER SET ANY_CS [,out_value_size IN INTEGER]);
DBMS_SQL.BIND_VARIABLE_RAW (
c IN INTEGER,
name IN VARCHAR2,
value IN RAW [,out_value_size IN INTEGER]);
DBMS_SQL.BIND_VARIABLE_ROWID (
c IN INTEGER,
name IN VARCHAR2,
value IN ROWID);
Pragmas
pragma restrict_references(bind_variable,WNDS);
Parameters
Table 152-8 BIND_VARIABLE Procedures Parameters
Parameter
Description
ID number of the cursor to which you want to bind a value.
Name of the variable in the statement.
The length of the bind variable name must be <=30 bytes.
Value that you want to bind to the variable in the cursor.
For and / variables, the value has the same type as the type of the value being passed in for this parameter.
Maximum expected value size, in bytes, for the , , or / variable.
If no size is given, then the length of the current value is used. This parameter must be specified if the parameter is not initialized.
Usage Notes
If the variable is an or / variable or an collection, then the given bind value must be valid for the variable or array type. Bind values for variables are ignored.
The bind variables or collections of a SQL statement are identified by their names. When binding a value to a bind variable or bind array, the string identifying it in the statement must contain a leading colon, as shown in the following example:
SELECT emp_name FROM emp WHERE SAL > :X;
For this example, the corresponding bind call would look similar to
BIND_VARIABLE(cursor_name, ‘:X’, 3500);
or
BIND_VARIABLE (cursor_name, ‘X’, 3500);
152.9.3 BIND_VARIABLE_PKG Procedure
This procedures binds a variable given value or set of values to a given variable in a cursor, based on the name of the variable in the statement. The type of the variable must be declared in the package specification. Bulk operations are not supported for these types.
Syntax
DBMS_SQL.BIND_VARIABLE_PKG (
c IN INTEGER,
name IN VARCHAR2,
value IN <datatype>);
Where <datatype> can be any one of the following data types:
RECORD
VARRAY
NESTED TABLE
INDEX BY PLS_INTEGER TABLE
INDEX BY BINARY_INTEGER TABLE
Table 152-9 BIND_VARIABLE_PKG Parameters
Parameter
Description
ID number of the cursor from which to get the values.
Name of the variable in the statement for which you are retrieving the value.
Single row option: Returns the value of the variable for the specified position. Oracle raises the exception, , if the type of this output parameter differs from the actual type of the value, as defined by the call to .
Array option: Local variable that has been declared <>
Example 152-2 Dynamic SQL using DBMS_SQL.BIND_VARIABLE_PKG to Bind a Package Variable
The variables types are declared in the package specification. The is used to bind the variable v1 in the cursor SQL statement.
152.9.4 CLOSE_CURSOR Procedure
This procedure closes a given cursor.
Syntax
DBMS_SQL.CLOSE_CURSOR (
c IN OUT INTEGER);
Pragmas
pragma restrict_references(close_cursor,RNDS,WNDS);
Parameters
Table 152-10 CLOSE_CURSOR Procedure Parameters
Parameter
Mode
Description
ID number of the cursor that you want to close.
Cursor is set to null.
After you call , the memory allocated to the cursor is released and you can no longer fetch from that cursor.
152.9.5 COLUMN_VALUE Procedure
This procedure returns the value of the cursor element for a given position in a given cursor. This procedure is used to access the data fetched by calling .
Syntax
DBMS_SQL.COLUMN_VALUE (
c IN INTEGER,
position IN INTEGER,
value OUT <datatype>
[,column_error OUT NUMBER]
[,actual_length OUT INTEGER]);
Where square brackets [ ] indicate optional parameters and <> can be any one of the following types:
BINARY_DOUBLE
BINARY_FLOAT
BFILE
BLOB
CLOB CHARACTER SET ANY_CS
DATE
DSINTERVAL_UNCONSTRAINED
NUMBER
TIME_TZ_UNCONSTRAINED
TIME_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_UNCONSTRAINED
UROWID
VARCHAR2 CHARACTER SET ANY_CS
YMINTERVAL_UNCONSTRAINED
user-defined object types
collections (VARRAYs and nested tables)
REFs
Opaque types
For variables containing , , and data, you can use the following variations on the syntax:
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]);
DBMS_SQL.COLUMN_VALUE_RAW (
c IN INTEGER,
position IN INTEGER,
value OUT RAW
[,column_error OUT NUMBER]
[,actual_length OUT INTEGER]);
DBMS_SQL.COLUMN_VALUE_ROWID (
c IN INTEGER,
position IN INTEGER,
value OUT ROWID
[,column_error OUT NUMBER]
[,actual_length OUT INTEGER]);
The following syntax enables the procedure to accommodate bulk operations:
DBMS_SQL.COLUMN_VALUE(
c IN INTEGER,
position IN INTEGER,
<param_name> IN OUT NOCOPY <table_type>);
Where the <> and its corresponding <> can be any one of these matching pairs:
bdbl_tab Binary_Double_Table
bflt_tab Binary_Float_Table
bf_tab Bfile_Table
bl_tab Blob_Table
cl_tab Clob_Table
d_tab Date_Table
ids_tab Interval_Day_To_Second_Table
iym_tab Interval_Year_To_Month_Table
n_tab Number_Table
tm_tab Time_Table
ttz_tab Time_With_Time_Zone_Table
tms_tab Timestamp_Table
tstz_tab Timestamp_With_ltz_Table
tstz_tab Timestamp_With_Time_Zone_Table
ur_tab Urowid_Table
c_tab Varchar2_Table
Pragmas
pragma restrict_references(column_value,RNDS,WNDS);
Parameters
Table 152-11 COLUMN_VALUE Procedure Parameters (Single Row)
Parameter
Description
ID number of the cursor from which you are fetching the values.
Relative position of the column in the cursor.
The first column in a statement has position 1.
Returns the value at the specified column.
Oracle raises exception , , if the type of this output parameter differs from the actual type of the value, as defined by the call to .
Returns any error code for the specified column value.
The actual length, before any truncation, of the value in the specified column.
Table 152-12 COLUMN_VALUE Procedure Parameters (Bulk)
Parameter
Description
ID number of the cursor from which you are fetching the values.
Relative position of the column in the cursor.
The first column in a statement has position 1.
Local variable that has been declared <>. <> is an parameter for bulk operations.
For bulk operations, the subprogram appends the new elements at the appropriate (implicitly maintained) index. For instance if on utilizing the DEFINE_ARRAY Procedure a batch size (the parameter) of 10 rows was specified and a start index () of 1 was specified, then the first call to this subprogram after calling the FETCH_ROWS Function will populate elements at index 1..10, and the next call will populate elements 11..20, and so on.
Exceptions
(-) is raised if the type of the given parameter is different from the actual type of the value. This type was the given type when the column was defined by calling procedure .
152.9.6 COLUMN_VALUE_LONG Procedure
This procedure gets part of the value of a long column.
Syntax
DBMS_SQL.COLUMN_VALUE_LONG (
c IN INTEGER,
position IN INTEGER,
length IN INTEGER,
offset IN INTEGER,
value OUT VARCHAR2,
value_length OUT INTEGER);
Pragmas
pragma restrict_references(column_value_long,RNDS,WNDS);
Parameters
Table 152-13 COLUMN_VALUE_LONG Procedure Parameters
Parameter
Description
Cursor ID number of the cursor from which to get the value.
Position of the column of which to get the value.
Number of bytes of the long value to fetch.
Offset into the long field for start of fetch.
Value of the column as a .
Number of bytes actually returned in value.
152.9.7 DEFINE_ARRAY Procedure
This procedure defines the collection for column into which you want to fetch rows (with a call). This procedure lets you do batch fetching of rows from a single statement. A single fetch call brings over a number of rows into the PL/SQL aggregate object.
When you fetch the rows, they are copied into buffers until you run a call, at which time the rows are copied into the table that was passed as an argument to the call.
Scalar and LOB Types for Collections
You can declare a local variable as one of the following table-item types, and then fetch any number of rows into it using . (These are the same types as you can specify for the procedure.)
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 bfile_table IS TABLE OF BFILE 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 timestamp_with_time_zone_Table
IS TABLE OF timestamp_tz_unconstrained
INDEX BY BINARY_INTEGER;
TYPE urowid_table IS TABLE OF UROWID INDEX BY BINARY_INTEGER;
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
Syntax
DBMS_SQL.DEFINE_ARRAY (
c IN INTEGER,
position IN INTEGER,
<table_variable> IN <datatype>
cnt IN INTEGER,
lower_bnd IN INTEGER);
Where <> and its corresponding <datatype> can be any one of the following matching pairs, being overloaded to accept different datatypes:
<clob_tab> Clob_Table
<bflt_tab> Binary_Float_Table
<bdbl_tab> Binary_Double_Table
<blob_tab> Blob_Table
<bfile_tab> Bfile_Table
<date_tab> Date_Table
<num_tab> Number_Table
<urowid_tab> Urowid_Table
<vchr2_tab> Varchar2_Table
<tm_tab> Time_Table
<ttz_tab> Time_With_Time_Zone_Table
<tms_tab> Timestamp_Table
<tstz_tab> Timestamp_With_ltz_Table
<tstz_tab> Timestamp_With_Time_Zone_Table
<ids_tab> Interval_Day_To_Second_Table
<iym_tab> Interval_Year_To_Month_Table
Pragmas
pragma restrict_references(define_array,RNDS,WNDS);
The subsequent call fetch “count” rows. When the call is made, these rows are placed in positions , +1, +2, and so on. While there are still rows coming, the user keeps issuing / calls. The rows keep accumulating in the table specified as an argument in the call.
Parameters
Table 152-14 DEFINE_ARRAY Procedure Parameters
Parameter
Description
ID number of the cursor to which you want to bind an array.
Relative position of the column in the array being defined.
The first column in a statement has position 1.
Local variable that has been declared as <>.
Number of rows that must be fetched.
Results are copied into the collection, starting at this lower bound index.
Usage Notes
The count must be an integer greater than zero; otherwise an exception is raised. The can be positive, negative, or zero. A query on which a call was issued cannot contain array binds.
Examples
PROCEDURE BULK_PLSQL(deptid NUMBER)
TYPE namelist IS TABLE OF employees.last_name%TYPE;
TYPE sallist IS TABLE OF employees.salary%TYPE;
names namelist;
sals sallist;
c NUMBER;
r NUMBER;
sql_stmt VARCHAR2(32767) :=
‘SELECT last_name, salary FROM employees WHERE department_id = :b1’;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, sql_stmt, dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(c, ‘b1’, deptid);
DBMS_SQL.DEFINE_ARRAY(c, 1, names, 5);
DBMS_SQL.DEFINE_ARRAY(c, 2, sals, 5);
r := DBMS_SQL.EXECUTE(c);
LOOP
r := DBMS_SQL.FETCH_ROWS(c);
DBMS_SQL.COLUMN_VALUE(c, 1, names);
DBMS_SQL.COLUMN_VALUE(c, 2, sals);
EXIT WHEN r != 5;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c);
— loop through the names and sals collections
FOR i IN names.FIRST .. names.LAST LOOP
DBMS_OUTPUT.PUT_LINE(‘Name = ‘ || names(i) || ‘, salary = ‘ || sals(i));
END LOOP;
END;
/
Example 152-3 Example: Defining an Array
The following examples show how to use the procedure:
declare
c NUMBER;
d NUMBER;
n_tab DBMS_SQL.NUMBER_TABLE;
indx NUMBER := -10;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
dBMS_SQL.PARSE(c, ‘select n from t order by 1’, DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_ARRAY(c, 1, n_tab, 10, indx);
d := DBMS_SQL.EXECUTE(c);
loop
d := DBMS_SQL.FETCH_ROWS(c);
DBMS_SQL.COLUMN_VALUE(c, 1, n_tab);
EXIT WHEN d != 10;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c);
EXCEPTION WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(c) THEN
DBMS_SQL.CLOSE_CURSOR(c);
END IF;
RAISE;
END;
/
Each time the preceding example calls FETCH_ROWS Function, it fetches 10 rows that are kept in buffers. When the COLUMN_VALUE Procedure is called, those rows move into the PL/SQL table specified (in this case ), at positions -10 to -1, as specified in the statements. When the second batch is fetched in the loop, the rows go to positions 0 to 9; and so on.
A current index into each array is maintained automatically. This index is initialized to “indx” at time and is updated every time is called. If you reexecute at any point, then the current index for each is reinitialized to “indx”.
In this way the entire result of the query is fetched into the table. When cannot fetch 10 rows, it returns the number of rows actually fetched (if no rows could be fetched, then it returns zero) and exits the loop.
Here is another example of using the procedure:
Consider a table defined as:
CREATE TABLE multi_tab (num NUMBER,
dat1 DATE,
var VARCHAR2(24),
dat2 DATE)
To select everything from this table and move it into four PL/SQL tables, you could use the following simple program:
DECLARE
c NUMBER;
d NUMBER;
n_tab DBMS_SQL.NUMBER_TABLE;
d_tab1 DBMS_SQL.DATE_TABLE;
v_tab DBMS_SQL.VARCHAR2_TABLE;
d_tab2 DBMS_SQL.DATE_TABLE;
indx NUMBER := 10;
BEGIN
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, ‘select * from multi_tab order by 1’, DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_ARRAY(c, 1, n_tab, 5, indx);
DBMS_SQL.DEFINE_ARRAY(c, 2, d_tab1, 5, indx);
DBMS_SQL.DEFINE_ARRAY(c, 3, v_tab, 5, indx);
DBMS_SQL.DEFINE_ARRAY(c, 4, d_tab2, 5, indx);
d := DBMS_SQL.EXECUTE(c);
LOOP
d := DBMS_SQL.FETCH_ROWS(c);
DBMS_SQL.COLUMN_VALUE(c, 1, n_tab);
DBMS_SQL.COLUMN_VALUE(c, 2, d_tab1);
DBMS_SQL.COLUMN_VALUE(c, 3, v_tab);
DBMS_SQL.COLUMN_VALUE(c, 4, d_tab2);
EXIT WHEN d != 5;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(c);
/*
The four tables can be used for anything. One usage might be to use to move the rows to another table by using a statement such as ‘ into values (:a, :b, :c, :d);
*/
EXCEPTION WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(c) THEN
DBMS_SQL.CLOSE_CURSOR(c);
END IF;
RAISE;
END;
/
152.9.8 DEFINE_COLUMN Procedures
This procedure defines a column to be selected from the given cursor. This procedure is only used with cursors.
The column being defined is identified by its relative position in the list of the statement in the given cursor. The type of the value determines the type of the column being defined.
See also the DEFINE_COLUMN_CHAR Procedure, DEFINE_COLUMN_LONG Procedure, DEFINE_COLUMN_RAW Procedure and DEFINE_COLUMN_ROWID Procedure.
Syntax
DBMS_SQL.DEFINE_COLUMN (
c IN INTEGER,
position IN INTEGER,
column IN <datatype>);
Where <> can be any one of the following types:
BINARY_DOUBLE
BINARY_FLOAT
BFILE
BLOB
CLOB CHARACTER SET ANY_CS
DATE
DSINTERVAL_UNCONSTRAINED
NUMBER
TIME_UNCONSTRAINED
TIME_TZ_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_UNCONSTRAINED
UROWID
YMINTERVAL_UNCONSTRAINED
user-defined object types
collections (VARRAYs and nested tables)
REFs
Opaque types
Note that is overloaded to accept different datatypes.
The following syntax is also supported for the procedure:
DBMS_SQL.DEFINE_COLUMN (
c IN INTEGER,
position IN INTEGER,
column IN VARCHAR2 CHARACTER SET ANY_CS,
column_size IN INTEGER);
Pragmas
pragma restrict_references(define_column,RNDS,WNDS);
Parameters
Table 152-15 DEFINE_COLUMN Procedure Parameters
Parameter
Description
ID number of the cursor for the row being defined to be selected
Relative position of the column in the row being defined.The first column in a statement has position 1.
Value of the column being defined. The type of this value determines the type for the column being defined.
Maximum expected size of the column value in bytes for columns of type .
Usage Notes
When using character length semantics the maximum number of bytes that can be returned for a column value of type is calculated as: maximum character byte size for the current character set. For example, specifying the as 10 means that a maximum of 30 (10*3) bytes can be returned when using character length semantics with a UTF8 character set regardless of the number of characters this represents.
152.9.9 DEFINE_COLUMN_CHAR Procedure
This procedure defines a column with data to be selected from the given cursor. This procedure is only used with cursors.
The column being defined is identified by its relative position in the list of the statement in the given cursor. The type of the value determines the type of the column being defined.
See also the DEFINE_COLUMN Procedures, DEFINE_COLUMN_LONG Procedure, DEFINE_COLUMN_RAW Procedure and DEFINE_COLUMN_ROWID Procedure.
Syntax
DBMS_SQL.DEFINE_COLUMN_CHAR (
c IN INTEGER,
position IN INTEGER,
column IN CHAR CHARACTER SET ANY_CS,
column_size IN INTEGER);
Pragmas
pragma restrict_references(define_column,RNDS,WNDS);
Parameters
Table 152-16 DEFINE_COLUMN_CHAR Procedure Parameters
Parameter
Description
ID number of the cursor for the row being defined to be selected
Relative position of the column in the row being defined.The first column in a statement has position 1.
Value of the column being defined. The type of this value determines the type for the column being defined.
Maximum expected size of the column value in characters for columns of type .
152.9.10 DEFINE_COLUMN_LONG Procedure
This procedure defines a column for a cursor. The column being defined is identified by its relative position in the list of the statement for the given cursor. The type of the value determines the type of the column being defined.
See also the DEFINE_COLUMN Procedures, DEFINE_COLUMN_CHAR Procedure, DEFINE_COLUMN_RAW Procedure and DEFINE_COLUMN_ROWID Procedure.
Syntax
DBMS_SQL.DEFINE_COLUMN_LONG (
c IN INTEGER,
position IN INTEGER);
Parameters
Table 152-17 DEFINE_COLUMN_LONG Procedure Parameters
Parameter
Description
ID number of the cursor for the row being defined to be selected.
Relative position of the column in the row being defined.
The first column in a statement has position 1.
152.9.11 DEFINE_COLUMN_RAW Procedure
This procedure defines a column of type to be selected from the given cursor.
This procedure is only used with cursors.
The column being defined is identified by its relative position in the list of the statement in the given cursor. The type of the value determines the type of the column being defined.
See also the DEFINE_COLUMN Procedures, DEFINE_COLUMN_CHAR Procedure, DEFINE_COLUMN_LONG Procedure and DEFINE_COLUMN_ROWID Procedure.
Syntax
DBMS_SQL.DEFINE_COLUMN_RAW (
c IN INTEGER,
position IN INTEGER,
column IN RAW,
column_size IN INTEGER);
Pragmas
pragma restrict_references(define_column,RNDS,WNDS);
Parameters
Table 152-18 DEFINE_COLUMN_RAW Procedure Parameters
Parameter
Description
ID number of the cursor for the row being defined to be selected.
Relative position of the column in the row being defined.The first column in a statement has position 1.
Value of the column being defined. The type of this value determines the type for the column being defined.
Maximum expected size of the column value in bytes for columns of type.
152.9.12 DEFINE_COLUMN_ROWID Procedure
This procedure defines a column of type to be selected from the given cursor. This procedure is only used with cursors.
The column being defined is identified by its relative position in the list of the statement in the given cursor. The type of the value determines the type of the column being defined.
See also the DEFINE_COLUMN Procedures, DEFINE_COLUMN_CHAR Procedure, DEFINE_COLUMN_LONG Procedure and DEFINE_COLUMN_RAW Procedure.
Syntax
DBMS_SQL.DEFINE_COLUMN_ROWID (
c IN INTEGER,
position IN INTEGER,
column IN ROWID);
Pragmas
pragma restrict_references(define_column,RNDS,WNDS);
Parameters
Table 152-19 DEFINE_COLUMN_ROWID Procedure Parameters
Parameter
Description
ID number of the cursor for the row being defined to be selected
Relative position of the column in the row being defined.The first column in a statement has position 1.
Value of the column being defined. The type of this value determines the type for the column being defined.
152.9.13 DESCRIBE_COLUMNS Procedure
This procedure describes the columns for a cursor opened and parsed through .
Syntax
DBMS_SQL.DESCRIBE_COLUMNS (
c IN INTEGER,
col_cnt OUT INTEGER,
desc_t OUT DESC_TAB);
Parameters
Table 152-20 DESCRIBE_COLUMNS Procedure Parameters
Parameter
Description
ID number of the cursor for the columns being described
Number of columns in the select list of the query
Describe table to fill in with the description of each of the columns of the query
Example 152-4 Describe Columns
This code can be used as a substitute to the SQL*Plus call by using a * query on the table that you want to describe.
152.9.14 DESCRIBE_COLUMNS2 Procedure
This procedure describes the specified column. This is an alternative to DESCRIBE_COLUMNS Procedure.
Syntax
DBMS_SQL.DESCRIBE_COLUMNS2 (
c IN INTEGER,
col_cnt OUT INTEGER,
desc_t OUT DESC_TAB2);
Pragmas
PRAGMA RESTRICT_REFERENCES(describe_columns2,WNDS);
Parameters
Table 152-21 DESCRIBE_COLUMNS2 Procedure Parameters
Parameter
Description
ID number of the cursor for the columns being described.
Number of columns in the select list of the query.
Describe table to fill in with the description of each of the columns of the query. This table is indexed from one to the number of elements in the select list of the query.
Related Topics
DESCRIBE_COLUMNS Procedure
152.9.15 DESCRIBE_COLUMNS3 Procedure
This procedure describes the specified column. This is an alternative to DESCRIBE_COLUMNS Procedure.
Syntax
DBMS_SQL.DESCRIBE_COLUMNS3 (
c IN INTEGER,
col_cnt OUT INTEGER,
desc_t OUT DESC_TAB3);
BMS_SQL.DESCRIBE_COLUMNS3 (
c IN INTEGER,
col_cnt OUT INTEGER,
desc_t OUT DESC_TAB4);
Pragmas
PRAGMA RESTRICT_REFERENCES(describe_columns3,WNDS);
Parameters
Table 152-22 DESCRIBE_COLUMNS3 Procedure Parameters
Parameter
Description
ID number of the cursor for the columns being described.
Number of columns in the select list of the query.
Describe table to fill in with the description of each of the columns of the query. This table is indexed from one to the number of elements in the select list of the query.
Usage Notes
The cursor passed in by the cursor ID has to be ed and d, otherwise an “invalid cursor id” error is raised.
Examples
CREATE TYPE PROJECT_T AS OBJECT
( projname VARCHAR2(20),
mgr VARCHAR2(20))
/
CREATE TABLE projecttab(deptno NUMBER, project HR.PROJECT_T)
/
DECLARE
curid NUMBER;
desctab DBMS_SQL.DESC_TAB3;
colcnt NUMBER;
sql_stmt VARCHAR2(200) := ‘select * from projecttab’;
BEGIN
curid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS3(curid, colcnt, desctab);
FOR i IN 1 .. colcnt LOOP
IF desctab(i).col_type = 109 THEN
DBMS_OUTPUT.PUT(desctab(i).col_name || ‘ is user-defined type: ‘);
DBMS_OUTPUT.PUT_LINE(desctab(i).col_schema_name || ‘.’ ||
desctab(i).col_type_name);
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(curid);
END;
/
Output:
PROJECT is user-defined type: HR.PROJECT_T
Related Topics
DESCRIBE_COLUMNS Procedure
152.9.16 EXECUTE Function
This function executes a given cursor. This function accepts the number of the cursor and returns the number of rows processed.
The return value is only valid for , , and statements; for other types of statements, including DDL, the return value is undefined and must be ignored.
Syntax
DBMS_SQL.EXECUTE (
c IN INTEGER)
RETURN INTEGER;
Parameters
Table 152-23 EXECUTE Function Parameters
Parameter
Description
Cursor ID number of the cursor to execute.
Return Values
Returns number of rows processed
Usage Notes
The cursor that is returned by the TO_CURSOR_NUMBER Function performs in the same way as a cursor that has already been executed. Consequently, calling for this cursor will cause an error.
152.9.17 EXECUTE_AND_FETCH Function
This function executes the given cursor and fetches rows.
This function provides the same functionality as calling and then calling . Calling instead, however, may reduce the number of network round-trips when used against a remote database.
The function returns the number of rows actually fetched.
Syntax
DBMS_SQL.EXECUTE_AND_FETCH (
c IN INTEGER,
exact IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;
Pragmas
pragma restrict_references(execute_and_fetch,WNDS);
Parameters
Table 152-24 EXECUTE_AND_FETCH Function Parameters
Parameter
Description
ID number of the cursor to execute and fetch.
Set to to raise an exception if the number of rows actually matching the query differs from one.
Note: Oracle does not support the exact fetch TRUE option with LONG columns.
Even if an exception is raised, the rows are still fetched and available.
Return Values
Returns designated rows
152.9.18 FETCH_ROWS Function
This function fetches a row from a given cursor.
You can call repeatedly as long as there are rows remaining to be fetched. These rows are retrieved into a buffer, and must be read by calling , for each column, after each call to .
The function accepts the ID number of the cursor to fetch, and returns the number of rows actually fetched.
Syntax
DBMS_SQL.FETCH_ROWS (
c IN INTEGER)
RETURN INTEGER;
Pragmas
pragma restrict_references(fetch_rows,WNDS);
Parameters
Table 152-25 FETCH_ROWS Function Parameters
Parameter
Description
ID number.
Return Values
Returns a row from a given cursor
152.9.19 GET_NEXT_RESULT Procedures
This procedure gets the statement of the next result returned to the caller of the recursive statement or, if this caller sets itself as the client for the recursive statement, the next result returned to this caller as client.
The statements are returned in same order as they are returned by the RETURN_RESULT Procedures.
Syntax
DBMS_SQL.GET_NEXT_RESULT(
c IN INTEGER,
rc OUT SYS_REFCURSOR);
DBMS_SQL.GET_NEXT_RESULT(
c IN INTEGER,
rc OUT INTEGER);
Parameters
Table 152-26 GET_NEXT_RESULT Procedure Parameters
Parameter
Description
Recursive statement cursor
Cursor or ref cursor of the statement of the next returned result
Exceptions
This is raised when there is no further returned statement result.
Usage Notes
After the cursor of a statement result is retrieved, the caller must close the cursor properly when it is no longer needed.
The cursors for all unretrieved returned statements will be closed after the cursor of the recursive statement is closed.
Examples
DECLARE
c INTEGER;
rc SYS_REFCURSOR;
BEGIN
c := DBMS_SQL.OPEN_CURSOR(treat_as_client_for_results => TRUE);
DBMS_SQL.PARSE(c => c,
statement => ‘begin proc; end;’);
DBMS_SQL.EXECUTE(c);
LOOP
BEGIN
DBMS_SQL.GET_NEXT_RESULT(c, rc);
EXCEPTIONS
WHEN no_data_found THEN
EXIT;
END;
LOOP
FETCH rc INTO …
…
END LOOP;
END LOOP;
END;
152.9.20 IS_OPEN Function
This function checks to see if the given cursor is currently open.
Syntax
DBMS_SQL.IS_OPEN (
c IN INTEGER)
RETURN BOOLEAN;
Pragmas
pragma restrict_references(is_open,RNDS,WNDS);
Parameters
Table 152-27 IS_OPEN Function Parameters
Parameter
Description
Cursor ID number of the cursor to check.
Return Values
Returns for any cursor number that has been opened but not closed, and for a cursor number. Note that the CLOSE_CURSOR Procedure Procedure s out the cursor variable passed to it.
Exceptions
This is raised if an invalid cursor ID number is detected. Once a session has encountered and reported this error, every subsequent DBMS_SQL call in the same session will raise this error, meaning that DBMS_SQL is non-operational for this session.
152.9.21 LAST_ERROR_POSITION Function
This function returns the byte offset in the SQL statement text where the error occurred. The first character in the SQL statement is at position 0.
Syntax
DBMS_SQL.LAST_ERROR_POSITION
RETURN INTEGER;
Pragmas
pragma restrict_references(last_error_position,RNDS,WNDS);
Return Values
Returns the byte offset in the SQL statement text where the error occurred
Usage Notes
Call this function after a call, before any other procedures or functions are called.
152.9.22 LAST_ROW_COUNT Function
This function returns the cumulative count of the number of rows fetched.
Syntax
DBMS_SQL.LAST_ROW_COUNT
RETURN INTEGER;
Pragmas
pragma restrict_references(last_row_count,RNDS,WNDS);
Return Values
Returns the cumulative count of the number of rows fetched
Usage Notes
Call this function after a or an call. If called after an call, then the value returned is zero.
152.9.23 LAST_ROW_ID Function
This function returns the of the last row processed.
Syntax
DBMS_SQL.LAST_ROW_ID
RETURN ROWID;
Pragmas
pragma restrict_references(last_row_id,RNDS,WNDS);
Return Values
Returns the of the last row processed
Usage Notes
Call this function after a or an call.
152.9.24 LAST_SQL_FUNCTION_CODE Function
This function returns the SQL function code for the statement.
These codes are listed in the Oracle Call Interface Programmer’s Guide.
Syntax
DBMS_SQL.LAST_SQL_FUNCTION_CODE
RETURN INTEGER;
Pragmas
pragma restrict_references(last_sql_function_code,RNDS,WNDS);
Return Values
Returns the SQL function code for the statement
Usage Notes
You must call this function immediately after the SQL statement is run; otherwise, the return value is undefined.
152.9.25 OPEN_CURSOR Functions
This function opens a new cursor.
The parameter allows for application of fine-grained control to the security of the opened cursor.
Syntax
DBMS_SQL.OPEN_CURSOR (
treat_as_client_for_results IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;
DBMS_SQL.OPEN_CURSOR (
security_level IN INTEGER,
treat_as_client_for_results IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;
Parameters
Table 152-28 OPEN_CURSOR Function Parameters
Parameter
Description
Specifies the level of security protection to enforce on the opened cursor. Valid security level values are , , and . When a argument value is provided to this overload, as well as for cursors opened using the overload of open_cursor without the parameter, the default security level value will be enforced on the opened cursor.
- allows all operations on the cursor without any security checks. The cursor may be fetched from, and even re-bound and re-executed, by code running with a different effective userid or roles than those in effect at the time the cursor was parsed. This level of security is off by default.
- requires that the referenced container, effective userid, and roles of the caller to for bind and execute operations on this cursor must be the same as those of the caller of the most recent parse operation on this cursor.
- requires that the referenced container, effective userid, and roles of the caller to for all bind, execute, define, describe, and fetch operations on this cursor must be the same as those of the caller of the most recent parse operation on this cursor.
Allows the caller of the recursive statement to set itself as the client to receive the statement results returned from the recursive statement to client. The statement results returned may be retrieved by the GET_NEXT_RESULT Procedures.
Pragmas
pragma restrict_references(open_cursor,RNDS,WNDS);
Return Values
Returns the cursor ID number of the new cursor
Usage Notes
When you no longer need this cursor, you must close it explicitly by calling the CLOSE_CURSOR Procedure.
You can use cursors to run the same SQL statement repeatedly or to run a new SQL statement. When a cursor is reused, the contents of the corresponding cursor data area are reset when the new SQL statement is parsed. It is never necessary to close and reopen a cursor before reusing it.
152.9.26 PARSE Procedures
This procedure parses the given statement in the given cursor. All statements are parsed immediately. In addition, DDL statements are run immediately when parsed.
There are multiple versions of the procedure:
Taking a statement as an argument
Taking a segmented string, one taking , a , and another, taking , a , as argument. These overloads concatenate elements of a PL/SQL table statement and parse the resulting string. You can use these procedures to parse a statement that is longer than the limit for a single variable by splitting up the statement.
Taking a statement as an argument. You can use the overload version of the parse procedure to parse a SQL statement larger than 32K bytes.
Syntax
Each version has multiple overloads.
DBMS_SQL.PARSE (
c IN INTEGER,
statement IN VARCHAR2,
language_flag IN INTEGER[
[,edition IN VARCHAR2 DEFAULT NULL],
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE]
[,schema IN VARCHAR2 DEFAULT NULL]
[,container IN VARCHAR2)];
DBMS_SQL.PARSE (
c IN INTEGER,
statement IN CLOB,
language_flag IN INTEGER[
[,edition IN VARCHAR2 DEFAULT NULL],
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE]
[,schema IN VARCHAR2 DEFAULT NULL]
[,container IN VARCHAR2)];
DBMS_SQL.PARSE (
c IN INTEGER,
statement IN VARCHAR2A,
lb IN INTEGER,
ub IN INTEGER,
lfflg IN BOOLEAN,
language_flag IN INTEGER[
[,edition IN VARCHAR2 DEFAULT NULL],
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE]
[,schema IN VARCHAR2 DEFAULT NULL]
[,container IN VARCHAR2)];
DBMS_SQL.PARSE (
c IN INTEGER,
statement IN VARCHAR2s,
lb IN INTEGER,
ub IN INTEGER,
lfflg IN BOOLEAN,
language_flag IN INTEGER[
[,edition IN VARCHAR2 DEFAULT NULL],
apply_crossedition_trigger IN VARCHAR2 DEFAULT NULL,
fire_apply_trigger IN BOOLEAN DEFAULT TRUE]
[,schema IN VARCHAR2 DEFAULT NULL]
[,container IN VARCHAR2)];
Parameters
Table 152-29 PARSE Procedure Parameters
Parameter
Description
ID number of the cursor in which to parse the statement.
SQL statement to be parsed. SQL statements larger than 32K that may be stored in s.
Unlike a PL/SQL statement, your SQL statement must not include a final semicolon. For example:
(cursor1, ‘ proc; ;’, 2);
(cursor1, ‘ tab (1)’, 2);
Lower bound for elements in the statement
Upper bound for elements in the statement
If , then insert a linefeed after each element on concatenation.
Specifies the behavior for the SQL statement. For more information about the possible values and its corresponding behaviors, see DBMS_SQL Constants
Specifies the edition in which to run the statement under the following conditions:
If and is , the statement will be run in the current edition.
If a valid container is specified, passing indicates the statement is to run in the target container’s default edition.
Given the user and the edition with which the statement is to be executed, the user must have privilege on the edition.
The following general conditions apply. The contents of the string are processed as a SQL identifier; double quotation marks must surround the remainder of the string if special characters or lowercase characters are present in the edition’s actual name, and if double quotation marks are not used the contents will be uppercased.
Specifies the unqualified name of a forward crossedition trigger that is to be applied to the specified SQL. The name is resolved using the edition and setting in which the statement is to be executed. The trigger must be owned by the user that will execute the statement.If a non- value is specified, the specified crossedition trigger will be executed assuming is , the trigger is enabled, the trigger is defined on the table which is the target of the statement, the type of the statement matches the trigger’s , any effective and restrictions are satisfied, and so on. Other forward crossedition triggers may also be executed, selected using the “crossedition trigger DML rules” applied as if the specified trigger was doing a further DML to the table that is the target of the statement. Non-crossedition triggers and reverse crossedition triggers will not be executed.The contents of the string are processed as a SQL identifier; double quotation marks must surround the remainder of the string if special characters or lowercase characters are present in the trigger’s actual name, and if double quotation marks are not used, the contents will be uppercased.
Indicates whether the specified is itself to be executed, or must only be a guide used in selecting other triggers. This is typically set when the statement is a replacement for the actions the would itself perform. If , the specified trigger is not executed, but other triggers are still selected for firing as if the specified trigger was doing a DML to the table that is the target of the statement. The and parameters are ignored if the statement is not a DML.
Specifies the schema in which to resolve unqualified object names. If , the current schema is the effective user’s schema.
Name of the target container in which the cursor is to run. If or unspecified, the name of the target container is that of the calling container and no container switch is performed. If a valid container name is specified, the current user must be a common user with privilege to switch to the target container. If a container switch completes, the effective user will have its default roles.
Usage Notes
Using to dynamically run DDL statements can cause the program to stop responding. For example, a call to a procedure in a package results in the package being locked until the execution returns to the user side. Any operation that results in a conflicting lock, such as dynamically trying to drop the package before the first lock is released, stops the program from running.
Because client-side code cannot reference remote package variables or constants, you must explicitly use the values of the constants.
For example, the following code does not compile on the client:
(, , .); — uses constant
The following code works on the client, because the argument is explicitly provided:
.(, , ); — compiles on the client
The type is currently supported for backward compatibility of legacy code. However, you are advised to use both for its superior capability and because will be deprecated in a future release.
To parse SQL statements larger than 32 KB, the new overload version of the PARSE procedure can be used instead of the overload.
If the parameter value is the same as the calling container, a container switch will not occur. However, the default roles of the current user will be in effect.
Exceptions
If you create a type, procedure, function, or package using that has compilation warnings, an exception is raised, and the PL/SQL unit is still created.
152.9.27 RETURN_RESULT Procedures
This procedure returns the result of an executed statement to the client application.
The result can be retrieved later by the client. Alternatively, it can return the statement result to and be retrieved later by the immediate caller that executes a recursive statement in which this statement result will be returned.
The caller can be:
A PL/SQL stored procedure executing the recursive statement using
A Java stored procedure using JDBC
A stored procedure using
An external procedure using the Oracle Call Interface (OCI)
Syntax
DBMS_SQL.RETURN_RESULT(
rc IN OUT SYS_REFCURSOR,
to_client IN BOOLEAN DEFAULT TRUE);
DBMS_SQL.RETURN_RESULT(
rc IN OUT INTEGER,
to_client IN BOOLEAN DEFAULT TRUE);
Parameters
Table 152-30 RETURN_RESULT Procedure Parameters
Parameter
Description
Statement cursor or ref cursor
Returns (or does not return) the statement result to the client. If not, it is returned to the immediate caller.
Usage Notes
Currently only a SQL query can be returned, and the return of statement results over remote procedure calls is not supported.
Once the statement is returned, it is no longer accessible except by the client or the immediate caller to which it is returned.
Statement results cannot be returned when the statement being executed by the client or any intermediate recursive statement is a SQL query and an error is raised.
A ref cursor being returned can be strongly or weakly-typed.
A query being returned can be partially fetched.
Because statement provides no interface to retrieve the statement results returned from its recursive statement, the cursors of the statement results returned to the caller of the statement will be closed when the statement completes. To retrieve the returned statement results from a recursive statement in PL/SQL, use to execute the recursive statement.
Examples
CREATE PROCEDURE proc AS
rc1 sys_refcursor;
rc2 sys_refcursor;
BEGIN
OPEN rc1 FOR SELECT * FROM t1;
DBMS_SQL.RETURN_RESULT(rc1);
OPEN rc2 FOR SELECT * FROM t2;
DBMS_SQL.RETURN_RESULT(rc2);
END;
/
152.9.28 TO_CURSOR_NUMBER Function
This function takes an ed strongly or weakly-typed ref cursor and transforms it into a cursor number.
Syntax
DBMS_SQL.TO_CURSOR_NUMBER(
rc IN OUT SYS_REFCURSOR)
RETURN INTEGER;
Parameters
Table 152-31 TO_CURSOR_NUMBER Function Parameters
Parameter
Description
to be transformed into a cursor number
Return Values
Returns a DBMS_SQL manageable cursor number transformed from a
Usage Notes
The passed in has to be ed, otherwise an error is raised.
Once the is transformed into a cursor number, the is no longer accessible by any native dynamic SQL operations.
The cursor that is returned by this subprogram performs in the same way as a cursor that has already been executed.
Examples
CREATE OR REPLACE PROCEDURE DO_QUERY(sql_stmt VARCHAR2) IS
TYPE CurType IS REF CURSOR;
src_cur CurType;
curid NUMBER;
desctab DBMS_SQL.DESC_TAB;
colcnt NUMBER;
namevar VARCHAR2(50);
numvar NUMBER;
datevar DATE;
empno NUMBER := 100;
BEGIN
— sql_stmt := ‘select …… from employees where employee_id = :b1’;
OPEN src_cur FOR sql_stmt USING empno;
— Switch from native dynamic SQL to DBMS_SQL
curid := DBMS_SQL.TO_CURSOR_NUMBER (src_cur);
DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
— Define columns
FOR i IN 1 .. colcnt LOOP
IF desctab(i).col_type = 2 THEN
DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
ELSIF desctab(i).col_type = 12 THEN
DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
…….
ELSE
DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 25);
END IF;
END LOOP;
— Fetch Rows
WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
FOR i IN 1 .. colcnt LOOP
IF (desctab(i).col_type = 1) THEN
DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
ELSIF (desctab(i).col_type = 2) THEN
DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
ELSIF (desctab(i).col_type = 12) THEN
DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
….
END IF;
END LOOP;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(curid);
END;
/
152.9.29 TO_REFCURSOR Function
This function takes an ed, d, and d cursor and transforms/migrates it into a PL/SQL manageable (a weakly-typed cursor) that can be consumed by PL/SQL native dynamic SQL switched to use native dynamic SQL.
This subprogram is only used with cursors.
Syntax
DBMS_SQL.TO_REFCURSOR(
cursor_number IN OUT INTEGER)
RETURN SYS_REFCURSOR;
Parameters
Table 152-32 TO_REFCURSOR Function Parameters
Parameter
Description
Cursor number of the cursor to be transformed into
Return Values
Returns a PL/SQL transformed from a cursor number
Usage Notes
The cursor passed in by the has to be ed, d, and d; otherwise an error is raised.
Once the cursor_number is transformed into a , the is no longer accessible by any operations.
After a cursor_number is transformed into a , using . to check to see if the is still open results in an error.
If the cursor number was last parsed with a valid container parameter, it cannot be converted to a .
Examples
CREATE OR REPLACE PROCEDURE DO_QUERY(mgr_id NUMBER) IS
TYPE CurType IS REF CURSOR;
src_cur CurType;
curid NUMBER;
sql_stmt VARCHAR2(200);
ret INTEGER;
empnos DBMS_SQL.Number_Table;
depts DBMS_SQL.Number_Table;
BEGIN
— DBMS_SQL.OPEN_CURSOR
curid := DBMS_SQL.OPEN_CURSOR;
sql_stmt := ‘SELECT EMPLOYEE_ID, DEPARTMENT_ID from employees where MANAGER_ID = :b1’;
DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(curid, ‘b1’, mgr_id);
ret := DBMS_SQL.EXECUTE(curid);
— Switch from DBMS_SQL to native dynamic SQL
src_cur := DBMS_SQL.TO_REFCURSOR(curid);
— Fetch with native dynamic SQL
FETCH src_cur BULK COLLECT INTO empnos, depts;
IF empnos.COUNT > 0 THEN
DBMS_OUTPUT.PUT_LINE(‘EMPNO DEPTNO’);
DBMS_OUTPUT.PUT_LINE(‘—– ——‘);
— Loop through the empnos and depts collections
FOR i IN 1 .. empnos.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(empnos(i) || ‘ ‘ || depts(i));
END LOOP;
END IF;
— Close cursor
CLOSE src_cur;
END;
/
152.9.30 VARIABLE_VALUE Procedures
This procedure returns the value of the named variable for a given cursor. It is used to return the values of bind variables inside PL/SQL blocks or DML statements with clause.
Syntax
DBMS_SQL.VARIABLE_VALUE (
c IN INTEGER,
name IN VARCHAR2,
value OUT NOCOPY <datatype>);
Where <datatype> can be any one of the following types:
ADT (user-defined object types)
BINARY_DOUBLE
BINARY_FLOAT
BFILE
BLOB
BOOLEAN
CLOB CHARACTER SET ANY_CS
DATE
DSINTERVAL_UNCONSTRAINED
NESTED table
NUMBER
OPAQUE types
REF
TIME_UNCONSTRAINED
TIME_TZ_UNCONSTRAINED
TIMESTAMP_LTZ_UNCONSTRAINED
TIMESTAMP_TZ_UNCONSTRAINED
TIMESTAMP_UNCONSTRAINED
UROWID
VARCHAR2 CHARACTER SET ANY_CS
VARRAY
YMINTERVAL_UNCONSTRAINED
For variables containing , , and data, you can use the following variations on the syntax:
DBMS_SQL.VARIABLE_VALUE_CHAR (
c IN INTEGER,
name IN VARCHAR2,
value OUT CHAR CHARACTER SET ANY_CS);
DBMS_SQL.VARIABLE_VALUE_RAW (
c IN INTEGER,
name IN VARCHAR2,
value OUT RAW);
DBMS_SQL.VARIABLE_VALUE_ROWID (
c IN INTEGER,
name IN VARCHAR2,
value OUT ROWID);
The following syntax enables the procedure to accommodate bulk operations:
DBMS_SQL.VARIABLE_VALUE (
c IN INTEGER,
name IN VARCHAR2,
value OUT NOCOPY <table_type>);
For bulk operations, <> must be a supported DBMS_SQL predefined TABLE type.
See DBMS_SQL Data Structures
Pragmas
pragma restrict_references(variable_value,RNDS,WNDS);
Parameters
Table 152-33 VARIABLE_VALUE Procedure Parameters
Parameter
Description
ID number of the cursor from which to get the values.
Name of the variable for which you are retrieving the value.
Single row option: Returns the value of the variable for the specified position. Oracle raises the exception, , if the type of this output parameter differs from the actual type of the value, as defined by the call to .
Array option: Local variable that has been declared <>. For bulk operations, is an parameter.
152.9.31 VARIABLE_VALUE_PKG Procedure
This procedure returns the value of the named variable for a given cursor.
It is used to return the values of bind variables of collection or record types inside PL/SQL blocks or DML statements with clause for a declared package. The type of the variable must be declared in the package specification. Bulk operations are not supported for these types.
Syntax
DBMS_SQL.VARIABLE_VALUE_PKG (
c IN INTEGER,
name IN VARCHAR2,
value OUT NOCOPY <table_type>);
Where <datatype> can be any one of the following data types:
RECORD
VARRAY
NESTED TABLE
INDEX BY PLS_INTEGER TABLE
INDEX BY BINARY_INTEGER TABLE
Parameters
Table 152-34 VARIABLE_VALUE_PKG Parameters
Parameter
Description
ID number of the cursor from which to get the values.
Name of the variable for which you are retrieving the value.
Single row option: Returns the value of the variable for the specified position. Oracle raises the exception, , if the type of this output parameter differs from the actual type of the value, as defined by the call to .
Array option: Local variable that has been declared <>.
Example 152-5 Dynamic SQL using DBMS_SQL.VARIABLE_VALUE_PKG to Get the Value of a Bind Variable
The data types are declared in the package specification. The is used to get the value of the bind variable v2 in the cursor SQL statement.
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/95062.html
