Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-01 |
|
|
View PDF |
The DBMS_SQL
package provides an interface to use dynamic SQL to parse any data manipulation language (DML) or data definition language (DDL) statement using PL/SQL. For example, you can enter a DROP
TABLE
statement from within a stored procedure by using the PARSE
procedure supplied with the DBMS_SQL
package.
See Also:
For a comparison of DBMS_SQL
and native dynamic SQL, see Oracle Database Application Developer's Guide - Fundamentals.
This chapter contains the following topics:
Overview
Security Model
Constants
Types
Exceptions
Operational Notes
Examples
Oracle lets you to write stored procedures and anonymous PL/SQL blocks that use dynamic SQL. Dynamic SQL statements are not embedded in your source program; rather, they are stored in character strings that are input to, or built by, the program at runtime. This enables you to create more general-purpose procedures. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.
Native Dynamic SQL is an alternative to DBMS_SQL
that lets you place dynamic SQL statements directly into PL/SQL blocks. In most situations, Native Dynamic SQL is easier to use and performs better than DBMS_SQL
. However, Native Dynamic SQL itself has certain limitations:
There is no support for so-called Method 4 (for dynamic SQL statements with an unknown number of inputs or outputs)
There is no support for SQL statements larger than 32K bytes
Also, there are some tasks that can only be performed using DBMS_SQL.
The ability to use dynamic SQL from within stored procedures generally follows the model of the Oracle Call Interface (OCI).
See Also:
Oracle Call Interface Programmer's GuidePL/SQL differs somewhat from other common programming languages, such as C. For example, addresses (also called pointers) are not user-visible in PL/SQL. As a result, there are some differences between the Oracle Call Interface and the DBMS_SQL
package. These differences include the following:
The OCI uses bind by address, while the DBMS_SQL
package uses bind by value.
With DBMS_SQL
you must call VARIABLE_VALUE
to retrieve the value of an OUT
parameter for an anonymous block, and you must call COLUMN_VALUE
after fetching rows to actually retrieve the values of the columns in the rows into your program.
The current release of the DBMS_SQL
package does not provide CANCEL
cursor procedures.
Indicator variables are not required, because NULLs
are fully supported as values of a PL/SQL variable.
A sample usage of the DBMS_SQL
package follows. For users of the Oracle Call Interfaces, this code should seem fairly straightforward.
DBMS_SQL
is compiled with AUTHID
CURRENT_USER.
Any DBMS_SQL
subprograms called from an anonymous PL/SQL block are run using the privileges of the current user.
See Also:
For more information about invoking subprograms using either Invoker or Definer Rights, see Oracle Database PL/SQL User's Guide and Referencev6 constant INTEGER := 0; native constant INTEGER := 1; v7 constant INTEGER := 2;
General Types
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 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_tab IS TABLE OF desc_rec 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 varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
TYPE varchar2a IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER; 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_tab2 IS TABLE OF desc_rec2 INDEX BY BINARY_INTEGER;
TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
inconsistent_type EXCEPTION; pragma exception_init(inconsistent_type, -6562);
This exception is raised by the COLUMN_VALUE Procedure or the VARIABLE_VALUE Procedures when the type of the given OUT
parameter (for where to put the requested value) is different from the type of the value.
To process a SQL statement, you must have an open cursor. When you call the OPEN_CURSOR Function function, you receive a cursor ID
number for the data structure representing a valid cursor maintained by Oracle. These cursors are distinct from cursors defined at the precompiler, OCI, or PL/SQL level, and are used only by the DBMS_SQL
package.
Every SQL statement must be parsed by calling the PARSE Procedure. Parsing the statement checks the statement's syntax and associates it with the cursor in your program.
You can parse any DML or DDL statement. DDL statements are run on the parse, which performs the implied commit.
Note:
When parsing a DDL statement to drop a package or a procedure, a deadlock can occur if you're still using a procedure in the package. After a call to a procedure, that procedure is considered to be in use until execution has returned to the user side. Any such deadlock timeouts after five minutes.The execution flow of DBMS_SQL
is shown in Figure 100-1.
Many DML statements require that data in your program be input to Oracle. When you define a SQL statement that contains input data to be supplied at runtime, you must use placeholders in the SQL statement to mark where data must be supplied.
For each placeholder in the SQL statement, you must call one of the bind procedures, the BIND_ARRAY Procedures or the BIND_VARIABLE Procedures, to supply the value of a variable in your program (or the values of an array) to the placeholder. When the SQL statement is subsequently run, Oracle uses the data that your program has placed in the output and input, or bind, variables.
DBMS_SQL
can run a DML statement multiple times — each time with a different bind variable. The BIND_ARRAY
procedure lets you bind a collection of scalars, each value of which is used as an input variable once for each EXECUTE
. This is similar to the array interface supported by the OCI.
The columns of the row being selected in a SELECT
statement are identified by their relative positions as they appear in the select list, from left to right. For a query, you must call one of the define procedures (DEFINE_COLUMN
, DEFINE_COLUMN_LONG
, or DEFINE_ARRAY
) to specify the variables that are to receive the SELECT
values, much the way an INTO
clause does for a static query.
Use the DEFINE_COLUMN_LONG
procedure to define LONG
columns, in the same way that DEFINE_COLUMN
is used to define non-LONG
columns. You must call DEFINE_COLUMN_LONG
before using the COLUMN_VALUE_LONG
procedure to fetch from the LONG
column.
Use the DEFINE_ARRAY
procedure to define a PL/SQL collection into which you want to fetch rows in a single SELECT
statement. DEFINE_ARRAY
provides an interface to fetch multiple rows at one fetch. You must call DEFINE_ARRAY
before using the COLUMN_VALUE
procedure to fetch the rows.
Call the EXECUTE
function to run your SQL statement.
The FETCH_ROWS
function retrieves the rows that satisfy the query. Each successive fetch retrieves another set of rows, until the fetch is unable to retrieve anymore rows. Instead of calling EXECUTE
and then FETCH_ROWS
, you may find it more efficient to call EXECUTE_AND_FETCH
if you are calling EXECUTE
for a single execution.
For queries, call COLUMN_VALUE
to determine the value of a column retrieved by the FETCH_ROWS
call. For anonymous blocks containing calls to PL
/SQL
procedures or DML statements with returning
clause, call VARIABLE_VALUE
to retrieve the values assigned to the output variables when statements were run.
To fetch just part of a LONG
database column (which can be up to two gigabytes in size), use the COLUMN_VALUE_LONG
procedure. You can specify the offset (in bytes) into the column value, and the number of bytes to fetch.
When you no longer need a cursor for a session, close the cursor by calling CLOSE_CURSOR
. If you are using an Oracle Open Gateway, then you may need to close cursors at other times as well. Consult your Oracle Open Gateway documentation for additional information.
If you neglect to close a cursor, then the memory used by that cursor remains allocated even though it is no longer needed.
If you are using dynamic SQL to process a query, then you must perform the following steps:
Specify the variables that are to receive the values returned by the SELECT
statement by calling the DEFINE_COLUMN Procedure, the DEFINE_COLUMN_LONG Procedure, or the DEFINE_ARRAY Procedure.
Run your SELECT
statement by calling the EXECUTE Function.
Call the FETCH_ROWS Function (or EXECUTE_AND_FETCH
) to retrieve the rows that satisfied your query.
Call COLUMN_VALUE Procedure or COLUMN_VALUE_LONG Procedure to determine the value of a column retrieved by the FETCH_ROWS Function for your query. If you used anonymous blocks containing calls to PL/SQL procedures, then you must call the VARIABLE_VALUE Procedures to retrieve the values assigned to the output variables of these procedures.
If you are using dynamic SQL to process an INSERT
, UPDATE
, or DELETE
, then you must perform the following steps:
You must first run your INSERT
, UPDATE
, or DELETE
statement by calling the EXECUTE Function.
If statements have the returning
clause, then you must call the VARIABLE_VALUE Procedures to retrieve the values assigned to the output variables.
There are additional functions in the DBMS_SQL package for obtaining information about the last referenced cursor in the session. The values returned by these functions are only meaningful immediately after a SQL statement is run. In addition, some error-locating functions are only meaningful after certain DBMS_SQL calls. For example, you call the LAST_ERROR_POSITION Function immediately after a PARSE.
This section provides example procedures that make use of the DBMS_SQL
package.
Example 1
This example does not require the use of dynamic SQL because the text of the statement is known at compile time., but it illustrate the basic concept underlying the package.
The DEMO
procedure deletes all of the employees from the EMP
table whose salaries are greater than the salary that you specify when you run DEMO
.
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;
Example 2
The following sample procedure is passed a SQL statement, which it then parses and runs:
CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2) AS cursor_name INTEGER; ret INTEGER; BEGIN cursor_name := DBMS_SQL.OPEN_CURSOR;
DDL statements are run by the parse call, which performs the implied commit.
DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.NATIVE); ret := DBMS_SQL.EXECUTE(cursor_name); DBMS_SQL.CLOSE_CURSOR(cursor_name); END;
Creating such a procedure enables you to perform the following operations:
The SQL statement can be dynamically generated at runtime by the calling program.
The SQL statement can be a DDL statement or a DML without binds.
For example, after creating this procedure, you could make the following call:
exec('create table acct(c1 integer)');
You could even call this procedure remotely, as shown in the following example. This lets you perform remote DDL.
exec@hq.com('CREATE TABLE acct(c1 INTEGER)');
Example 3
The following sample procedure is passed the names of a source and a destination table, and copies the rows from the source table to the destination table. This sample procedure assumes that both the source and destination tables have the following columns:
id of type NUMBER name of type VARCHAR2(30) birthdate of type DATE
This procedure does not specifically require the use of dynamic SQL; however, it illustrates the concepts of this package.
CREATE OR REPLACE PROCEDURE copy ( source IN VARCHAR2, destination IN VARCHAR2) IS id_var NUMBER; name_var VARCHAR2(30); birthdate_var DATE; source_cursor INTEGER; destination_cursor INTEGER; ignore INTEGER; BEGIN -- Prepare a cursor to select from the source table: source_cursor := dbms_sql.open_cursor; DBMS_SQL.PARSE(source_cursor, 'SELECT id, name, birthdate FROM ' || source, DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var); DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name_var, 30); DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate_var); ignore := DBMS_SQL.EXECUTE(source_cursor); -- Prepare a cursor to insert into the destination table: destination_cursor := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(destination_cursor, 'INSERT INTO ' || destination || ' VALUES (:id_bind, :name_bind, :birthdate_bind)', DBMS_SQL.NATIVE); -- Fetch a row from the source table and insert it into the destination table: LOOP IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN -- get column values of the row DBMS_SQL.COLUMN_VALUE(source_cursor, 1, id_var); DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name_var); DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate_var); -- Bind the row into the cursor that inserts into the destination table. You -- could alter this example to require the use of dynamic SQL by inserting an -- if condition before the bind. DBMS_SQL.BIND_VARIABLE(destination_cursor, ':id_bind', id_var); DBMS_SQL.BIND_VARIABLE(destination_cursor, ':name_bind', name_var); DBMS_SQL.BIND_VARIABLE(destination_cursor, ':birthdate_bind', birthdate_var); ignore := DBMS_SQL.EXECUTE(destination_cursor); ELSE -- No more rows to copy: EXIT; END IF; END LOOP; -- Commit and close all cursors: COMMIT; DBMS_SQL.CLOSE_CURSOR(source_cursor); DBMS_SQL.CLOSE_CURSOR(destination_cursor); EXCEPTION WHEN OTHERS THEN IF DBMS_SQL.IS_OPEN(source_cursor) THEN DBMS_SQL.CLOSE_CURSOR(source_cursor); END IF; IF DBMS_SQL.IS_OPEN(destination_cursor) THEN DBMS_SQL.CLOSE_CURSOR(destination_cursor); END IF; RAISE; END; /
Examples 3, 4, and 5: Bulk DML
This series of examples shows how to use bulk array binds (table items) in the SQL DML statements DELETE
, INSERT
, and UPDATE
.
In a DELETE
statement, for example, you could bind in an array in the WHERE
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 BIND_ARRAY
call. Each element of the array potentially deletes a large number of employees from the database.
Here is an example of a bulk INSERT
statement:
DECLARE stmt VARCHAR2(200); empno_array DBMS_SQL.NUMBER_TABLE; empname_array DBMS_SQL.VARCHAR2_TABLE; c NUMBER; dummy NUMBER; BEGIN FOR i in 0..9 LOOP empno_array(i) := 1000 + i; empname_array(I) := get_name(i); END LOOP; stmt := 'INSERT INTO emp VALUES(:num_array, :name_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); 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; /
When the execute takes place, all 10 of the employees are inserted into the table.
Finally, here is an example of an bulk UPDATE
statement.
Declare stmt VARCHAR2(200); emp_no_array DBMS_SQL.NUMBER_TABLE; emp_addr_array DBMS_SQL.VARCHAR2_TABLE; c NUMBER; dummy NUMBER; BEGIN for i in 0..9 loop emp_no_array(i) := 1000 + i; emp_addr_array(I) := get_new_addr(i); END LOOP; stmt := 'update emp set ename = :name_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, ':name_array', empname_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; /
When the EXECUTE Function call happens, the addresses of all employees are updated at once. The two collections are always stepped in unison. If the WHERE
clause returns more than one row, then all those employees get the address the addr_array
happens to be pointing to at that time.
Examples 6 and 7: Defining an Array
The following examples show how to use the DEFINE_ARRAY
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 does a FETCH_ROWS Function call, it fetches 10 rows that are kept in DBMS_SQL
buffers. When the COLUMN_VALUE Procedure call is run, those rows move into the PL/SQL table specified (in this case n_tab
), at positions -10 to -1, as specified in the DEFINE
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 EXECUTE
and keeps getting updated every time a COLUMN_VALUE
call is made. If you reexecute at any point, then the current index for each DEFINE
is re-initialized to "indx".
In this way the entire result of the query is fetched into the table. When FETCH_ROWS
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 DEFINE_ARRAY
procedure:
Consider a table MULTI_TAB
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 BIND_ARRAY
to move the rows to another table by using a query such as 'INSERT
into SOME_T
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; /
Example 8: Describe Columns
This can be used as a substitute to the SQL*Plus DESCRIBE
call by using a SELECT
* query on the table that you want to describe.
DECLARE c NUMBER; d NUMBER; col_cnt 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; /
Example 9: RETURNING clause
The RETURNING
clause was added to DML statements in an earlier Oracle database release. With this clause, INSERT
, UPDATE
, and DELETE
statements can return values of expressions. These values are returned in bind variables.
DBMS_SQL
.BIND_VARIABLE
is used to bind these outbinds if a single row is inserted, updated, or deleted. If multiple rows are inserted, updated, or deleted, then DBMS_SQL
.BIND_ARRAY
is used. DBMS_SQL
.VARIABLE_VALUE
must be called to get the values in these bind variables.
Note:
This is similar toDBMS_SQL
.VARIABLE_VALUE
, which must be called after running a PL/SQL block with an out-bind inside DBMS_SQL
.i) Single row insert
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', DBMS_SQL.NATIVE); 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 variable DBMS_SQL.CLOSE_CURSOR(c); END; /
ii) Single row update
CREATE OR REPLACE PROCEDURE single_Row_update (c1 NUMBER, c2 NUMBER, r out NUMBER) IS c NUMBER; n NUMBER; BEGIN c := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(c, 'UPDATE tab SET c1 = :bnd1, c2 = :bnd2 ' || 'WHERE rownum < 2' || 'RETURNING c1*c2 INTO :bnd3', DBMS_SQL.NATIVE); 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 variable DBMS_SQL.CLOSE_CURSOR(c); END; /
iii) Single row delete
CREATE OR REPLACE PROCEDURE single_Row_Delete (c1 NUMBER, c2 NUMBER, r OUT NUMBER) is c NUMBER; n number; BEGIN c := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(c, 'delete from tab ' || 'where rownum < 2 ' || 'returning c1*c2 into :bnd3', DBMS_SQL.NATIVE); 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 variable DBMS_SQL.CLOSE_CURSOR(c); END; /
iv) Multiple row insert
CREATE OR REPLACE PROCEDURE multi_Row_insert (c1 DBMS_SQL.NUMBER_TABLE, c2 DBMS_SQL.NUMBER_TABLE, r OUT DBMS_SQL.NUMBER_TABLE) 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', DBMS_SQL.NATIVE); DBMS_SQL.BIND_ARRAY(c, 'bnd1', c1); DBMS_SQL.BIND_ARRAY(c, 'bnd2', c2); DBMS_SQL.BIND_ARRAY(c, 'bnd3', r); n := DBMS_SQL.EXECUTE(c); DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r);-- get value of outbind variable DBMS_SQL.CLOSE_CURSOR(c); END; /
v) Multiple row Update.
CREATE OR REPLACE PROCEDURE multi_Row_update (c1 NUMBER, c2 NUMBER, r OUT DBMS_SQL.NUMBER_TABLE) IS c NUMBER; n NUMBER; BEGIN c := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(c, 'UPDATE tab SET c1 = :bnd1 WHERE c2 = :bnd2 ' || 'RETURNING c1*c2 INTO :bnd3', DBMS_SQL.NATIVE); DBMS_SQL.BIND_VARIABLE(c, 'bnd1', c1); DBMS_SQL.BIND_VARIABLE(c, 'bnd2', c2); DBMS_SQL.BIND_ARRAY(c, 'bnd3', r); n := DBMS_SQL.EXECUTE(c); DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r);-- get value of outbind variable DBMS_SQL.CLOSE_CURSOR(c); END; /
Note:
bnd1 and bnd2 can be array as well. The value of the expression for all the rows updated will be in bnd3. There is no way of differentiating which rows got updated of each value of bnd1 and bnd2.vi) Multiple row delete
CREATE OR REPLACE PROCEDURE multi_row_delete (c1 DBMS_SQL.NUMBER_TABLE, r OUT DBMS_SQL.NUMBER_TABLE) is c NUMBER; n NUMBER; BEGIN c := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(c, 'DELETE FROM tab WHERE c1 = :bnd1' || 'RETURNING c1*c2 INTO :bnd2', DBMS_SQL.NATIVE); DBMS_SQL.BIND_ARRAY(c, 'bnd1', c1); DBMS_SQL.BIND_ARRAY(c, 'bnd2', r); n := DBMS_SQL.EXECUTE(c); DBMS_SQL.VARIABLE_VALUE(c, 'bnd2', r);-- get value of outbind variable DBMS_SQL.CLOSE_CURSOR(c); END; /
vii) Out-bind in bulk PL/SQL
CREATE OR REPLACE PROCEDURE foo (n NUMBER, square OUT NUMBER) IS BEGIN square := n * n; END;/ CREATE OR REPLACE PROCEDURE bulk_plsql (n DBMS_SQL.NUMBER_TABLE, square OUT DBMS_SQL.NUMBER_TABLE) IS c NUMBER; r NUMBER; BEGIN c := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(c, 'BEGIN foo(:bnd1, :bnd2); END;', DBMS_SQL.NATIVE); DBMS_SQL.BIND_ARRAY(c, 'bnd1', n); DBMS_SQL.BIND_ARRAY(c, 'bnd2', square); r := DBMS_SQL.EXECUTE(c); DBMS_SQL.VARIABLE_VALUE(c, 'bnd2', square); END; /
Note:
DBMS_SQL
.BIND_ARRAY
of number_Table
internally binds a number. The number of times statement is run depends on the number of elements in an inbind array.Table 100-1 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 |
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 LONG column, that has been defined using DEFINE_COLUMN_LONG |
DEFINE_ARRAY Procedure |
Defines a collection to be selected from the given cursor, used only with SELECT statements |
DEFINE_COLUMN Procedure |
Defines a column to be selected from the given cursor, used only with SELECT statements |
DEFINE_COLUMN_LONG Procedure |
Defines a LONG column to be selected from the given cursor, used only with SELECT statements |
DESCRIBE_COLUMNS Procedure |
Describes the columns for a cursor opened and parsed through DBMS_SQL |
DESCRIBE_COLUMNS2 Procedure |
Describes 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 |
IS_OPEN Function |
Returns TRUE 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 ROWID of last row processed |
LAST_SQL_FUNCTION_CODE Function |
Returns SQL function code for statement |
OPEN_CURSOR Function |
Returns cursor ID number of new cursor |
PARSE Procedure |
Parses given statement |
VARIABLE_VALUE Procedures |
Returns value of named variable for given cursor |
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 <table_variable
> and its corresponding <datatype> 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
Notice that the BIND_ARRAY
procedure is overloaded to accept different datatypes.
Parameters
Table 100-2 BIND_ARRAY Procedure Parameters
Parameter | Description |
---|---|
c |
ID number of the cursor to which you want to bind a value. |
name |
Name of the collection in the statement. |
table_variable |
Local variable that has been declared as <datatype >. |
index1 |
Index for the table element that marks the lower bound of the range. |
index2 |
Index for the table element that marks the upper bound of the range. |
Usage Notes
The length of the bind variable name should be <=30 bytes.
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).
See Also:
"Examples 3, 4, and 5: Bulk DML" for examples of how to bind collections.Bulk Array Binds
Bulk selects, inserts, updates, and deletes can enhance the performance of applications by bundling many calls into one. The DBMS_SQL
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 DBMS_SQL
buffers (the same as for all scalar types) and then the table is manipulated from the local DBMS_SQL
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 DBMS_SQL
.
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; <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
This procedures 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_VARIABLE ( c IN INTEGER, name IN VARCHAR2, value IN <datatype>)
Where <datatype> 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 VARCHAR2 CHARACTER SET ANY_CS YMINTERVAL_UNCONSTRAINED
Notice that BIND_VARIABLE
is overloaded to accept different datatypes.
The following syntax is also supported for BIND_VARIABLE
. The square brackets [] indicate an optional parameter for the BIND_VARIABLE
function.
DBMS_SQL.BIND_VARIABLE ( c IN INTEGER, name IN VARCHAR2, value IN VARCHAR2 CHARACTER SET ANY_CS [,out_value_size IN INTEGER]);
To bind CHAR
, RAW
, and ROWID
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 100-3 BIND_VARIABLE Procedure Parameters
Parameter | Description |
---|---|
c |
ID number of the cursor to which you want to bind a value. |
name |
Name of the variable in the statement. |
value |
Value that you want to bind to the variable in the cursor.
For |
out_value_size |
Maximum expected OUT value size, in bytes, for the VARCHAR2 , RAW , CHAR OUT or IN /OUT variable.
If no size is given, then the length of the current value is used. This parameter must be specified if the |
Usage Notes
If the variable is an IN
or IN
/OUT
variable or an IN
collection, then the given bind value must be valid for the variable or array type. Bind values for OUT
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);
The length of the bind variable name should be <=30 bytes.
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).
See Also:
"Examples 3, 4, and 5: Bulk DML" for examples of how to bind collections.Bulk Array Binds
Bulk selects, inserts, updates, and deletes can enhance the performance of applications by bundling many calls into one. The DBMS_SQL
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 DBMS_SQL
buffers (the same as for all scalar types) and then the table is manipulated from the local DBMS_SQL
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 DBMS_SQL
.
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; <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
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 100-4 CLOSE_CURSOR Procedure Parameters
Parameter | Mode | Description |
---|---|---|
c |
IN |
ID number of the cursor that you want to close. |
c |
OUT |
Cursor is set to null.
After you call |
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 FETCH_ROWS
.
Syntax
DBMS_SQL.COLUMN_VALUE ( c IN INTEGER, position IN INTEGER, value OUT <datatype> [,column_error OUT NUMBER] [,actual_length OUT INTEGER]);
Where <datatype> 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 <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
Note:
The square brackets [ ] indicate optional parameters.Pragmas
pragma restrict_references(column_value,RNDS,WNDS);
The following syntax is also supported for the COLUMN_VALUE
procedure:
DBMS_SQL.COLUMN_VALUE( c IN INTEGER, position IN INTEGER, <table_variable> IN <datatype>);
Where the <table_variable
> and its corresponding <datatype> can be any one of these matching pairs:
<bdbl_tab> Binary_Double_Table <bflt_tab> Binary_Float_Table <bfile_tab> Bfile_Table <blob_tab> Blob_Table <clob_tab> Clob_Table <date_tab> Date_Table <ids_tab> Interval_Day_To_Second_Table <iym_tab> Interval_Year_To_Month_Table <num_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 <urowid_tab> Urowid_Table <vchr2_tab> Varchar2_Table
For columns containing CHAR
, RAW
, and ROWID
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]);
Parameters
Table 100-5 COLUMN_VALUE Procedure Parameters
Parameter | Description |
---|---|
c |
ID number of the cursor from which you are fetching the values. |
position |
Relative position of the column in the cursor.
The first column in a statement has position 1. |
value |
Returns the value at the specified column and row.
If the row number specified is greater than the total number of rows fetched, then you receive an error message. Oracle raises exception |
table_variable |
Local variable that has been declared <datatype >. |
column_error |
Returns any error code for the specified column value. |
actual_length |
The actual length, before any truncation, of the value in the specified column. |
Exceptions
inconsistent_type
(ORA
-06562
) is raised if the type of the given OUT
parameter value
is different from the actual type of the value. This type was the given type when the column was defined by calling procedure DEFINE_COLUMN
.
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 100-6 COLUMN_VALUE_LONG Procedure Parameters
Parameter | Description |
---|---|
c |
Cursor ID number of the cursor from which to get the value. |
position |
Position of the column of which to get the value. |
length |
Number of bytes of the long value to fetch. |
offset |
Offset into the long field for start of fetch. |
value |
Value of the column as a VARCHAR2 . |
value_length |
Number of bytes actually returned in value. |
This procedure defines the collection for column into which you want to fetch rows (with a FETCH_ROWS
call). This procedure lets you do batch fetching of rows from a single SELECT
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 DBMS_SQL
buffers until you run a COLUMN_VALUE
call, at which time the rows are copied into the table that was passed as an argument to the COLUMN_VALUE
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 DBMS_SQL
. (These are the same types as you can specify for the BIND_ARRAY
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; <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
Syntax
DBMS_SQL.DEFINE_ARRAY ( c IN INTEGER, position IN INTEGER, <table_variable> IN <datatype> cnt IN INTEGER, lower_bnd IN INTEGER);
Where <table_variable
> and its corresponding <datatype> 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
Notice that DEFINE_ARRAY
is overloaded to accept different datatypes.
Pragmas
pragma restrict_references(define_array,RNDS,WNDS);
The subsequent FETCH_ROWS
call fetch "count" rows. When the COLUMN_VALUE
call is made, these rows are placed in positions indx, indx+1, indx+2, and so on. While there are still rows coming, the user keeps issuing FETCH_ROWS
/COLUMN_VALUE
calls. The rows keep accumulating in the table specified as an argument in the COLUMN_VALUE
call.
Parameters
Table 100-7 DEFINE_ARRAY Procedure Parameters
Parameter | Description |
---|---|
c |
ID number of the cursor to which you want to bind an array. |
position |
Relative position of the column in the array being defined.
The first column in a statement has position 1. |
table_variable |
Local variable that has been declared as <datatype >. |
cnt |
Number of rows that must be fetched. |
lower_bnd |
Results are copied into the collection, starting at this lower bound index. |
The count (cnt)
must be an integer greater than zero; otherwise an exception is raised. The indx
can be positive, negative, or zero. A query on which a DEFINE_ARRAY
call was issued cannot contain array binds.
See Also:
"Examples 6 and 7: Defining an Array" for examples of how to define collections.This procedure defines a column to be selected from the given cursor. This procedure is only used with SELECT
cursors.
The column being defined is identified by its relative position in the SELECT
list of the statement in the given cursor. The type of the COLUMN
value determines the type of the column being defined.
Syntax
DBMS_SQL.DEFINE_COLUMN ( c IN INTEGER, position IN INTEGER, column IN <datatype>)
Where <datatype
> 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 VARCHAR2 CHARACTER SET ANY_CS YMINTERVAL_UNCONSTRAINED
Notice that DEFINE_COLUMN
is overloaded to accept different datatypes.
Pragmas
pragma restrict_references(define_column,RNDS,WNDS);
The following syntax is also supported for the DEFINE_COLUMN
procedure:
DBMS_SQL.DEFINE_COLUMN ( c IN INTEGER, position IN INTEGER, column IN VARCHAR2 CHARACTER SET ANY_CS, column_size IN INTEGER), urowid IN INTEGER;
To define columns with CHAR
, RAW
, and ROWID
data, you can use the following variations on the procedure syntax:
DBMS_SQL.DEFINE_COLUMN_CHAR ( c IN INTEGER, position IN INTEGER, column IN CHAR CHARACTER SET ANY_CS, column_size IN INTEGER); DBMS_SQL.DEFINE_COLUMN_RAW ( c IN INTEGER, position IN INTEGER, column IN RAW, column_size IN INTEGER); DBMS_SQL.DEFINE_COLUMN_ROWID ( c IN INTEGER, position IN INTEGER, column IN ROWID);
Parameters
Table 100-8 DEFINE_COLUMN Procedure Parameters
Parameter | Description |
---|---|
c |
ID number of the cursor for the row being defined to be selected. |
position |
Relative position of the column in the row being defined.
The first column in a statement has position 1. |
column |
Value of the column being defined.
The type of this value determines the type for the column being defined. |
column_size |
Maximum expected size of the column value, in bytes, for columns of type VARCHAR2 , CHAR , and RAW . |
This procedure defines a LONG
column for a SELECT
cursor. The column being defined is identified by its relative position in the SELECT
list of the statement for the given cursor. The type of the COLUMN
value determines the type of the column being defined.
Syntax
DBMS_SQL.DEFINE_COLUMN_LONG ( c IN INTEGER, position IN INTEGER);
Parameters
Table 100-9 DEFINE_COLUMN_LONG Procedure Parameters
Parameter | Description |
---|---|
c |
ID number of the cursor for the row being defined to be selected. |
position |
Relative position of the column in the row being defined.
The first column in a statement has position 1. |
This procedure describes the columns for a cursor opened and parsed through DBMS_SQL
.
Syntax
DBMS_SQL.DESCRIBE_COLUMNS ( c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT DESC_TAB);
Parameters
Table 100-10 DESCRIBE_COLUMNS Procedure Parameters
Parameter | Description |
---|---|
c |
ID number of the cursor for the columns being described. |
col_cnt |
Number of columns in the select list of the query. |
desc_t |
Table of DESC_REC , each DESC_REC describing a column in the query. |
This function 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_tab2 OUT DESC_TAB);
Pragmas
PRAGMA RESTRICT_REFERENCES(describe_columns2,WNDS);
Parameters
Table 100-11 DESCRIBE_COLUMNS2 Procedure Parameters
Parameter | Description |
---|---|
c |
ID number of the cursor for the columns being described. |
col_cnt |
Number of columns in the select list of the query. |
desc_tab2 |
The 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. |
This function executes a given cursor. This function accepts the ID
number of the cursor and returns the number of rows processed. The return value is only valid for INSERT
, UPDATE
, and DELETE
statements; for other types of statements, including DDL, the return value is undefined and should be ignored.
Syntax
DBMS_SQL.EXECUTE ( c IN INTEGER) RETURN INTEGER;
Parameters
Table 100-12 EXECUTE Function Parameters
Parameter | Description |
---|---|
c |
Cursor ID number of the cursor to execute. |
This function executes the given cursor and fetches rows. This function provides the same functionality as calling EXECUTE
and then calling FETCH_ROWS
. Calling EXECUTE_AND_FETCH
instead, however, may reduce the number of network round-trips when used against a remote database.
The EXECUTE_AND_FETCH
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 100-13 EXECUTE_AND_FETCH Function Parameters
Parameter | Description |
---|---|
c |
ID number of the cursor to execute and fetch. |
exact |
Set to TRUE 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. |
This function fetches a row from a given cursor. You can call FETCH_ROWS
repeatedly as long as there are rows remaining to be fetched. These rows are retrieved into a buffer, and must be read by calling COLUMN_VALUE
, for each column, after each call to FETCH_ROWS
.
The FETCH_ROWS
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
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 100-15 IS_OPEN Function Parameters
Parameter | Description |
---|---|
c |
Cursor ID number of the cursor to check. |
Return Values
Table 100-16 IS_OPEN Function Return Values
Return Value | Description |
---|---|
TRUE |
Given cursor is currently open. |
FALSE |
Given cursor is currently not open. |
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);
Usage Notes
Call this function after a PARSE
call, before any other DBMS_SQL
procedures or functions are called.
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);
Usage Notes
Call this function after a FETCH_ROWS
or an EXECUTE_AND_FETCH
call. If called after an EXECUTE
call, then the value returned is zero.
This function returns the ROWID
of the last row processed.
Syntax
DBMS_SQL.LAST_ROW_ID RETURN ROWID;
Pragmas
pragma restrict_references(last_row_id,RNDS,WNDS);
Usage Notes
Call this function after a FETCH_ROWS
or an EXECUTE_AND_FETCH
call.
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);
Usage Notes
You should call this function immediately after the SQL statement is run; otherwise, the return value is undefined.
This procedure opens a new cursor. When you no longer need this cursor, you must close it explicitly by calling CLOSE_CURSOR
.
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.
Syntax
DBMS_SQL.OPEN_CURSOR RETURN INTEGER;
Pragmas
pragma restrict_references(open_cursor,RNDS,WNDS);
Return Values
This function returns the cursor ID number of the new cursor.
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 two versions of the PARSE
procedure: one uses a VARCHAR2
statement as an argument, and the other uses a VARCHAR2S
(table of VARCHAR2
) as an argument.
Syntax
DBMS_SQL.PARSE ( c IN INTEGER, statement IN VARCHAR2, language_flag IN INTEGER); DBMS_SQL.PARSE ( c IN INTEGER, statement IN VARCHAR2A, lb IN INTEGER, ub IN INTEGER, lfflg IN BOOLEAN, language_flag IN INTEGER);
The PARSE
procedure also supports the following syntax for large SQL statements:
DBMS_SQL.PARSE ( c IN INTEGER, statement IN VARCHAR2S, lb IN INTEGER, ub IN INTEGER, lfflg IN BOOLEAN, language_flag IN INTEGER);
Note:
The procedure concatenates elements of a PL/SQL table statement and parses the resulting string. You can use this procedure to parse a statement that is longer than the limit for a singleVARCHAR2
variable by splitting up the statement.Parameters
Table 100-17 PARSE Procedure Parameters
Parameter | Description |
---|---|
c |
ID number of the cursor in which to parse the statement. |
statement |
SQL statement to be parsed.
Unlike PL/SQL statements, your SQL statement should not include a final semicolon. For example:
|
lb |
Lower bound for elements in the statement. |
ub |
Upper bound for elements in the statement. |
lfflg |
If TRUE , then insert a linefeed after each element on concatenation. |
language_flag |
Determines how Oracle handles the SQL statement. The following options are recognized:
|
Usage Notes
Note:
Using DBMS_SQL
to dynamically run DDL statements can result in the program hanging. 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, results in a hang.
The size limit for parsing SQL statements with the preceding syntax is 32KB.
Note:
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:
DBMS_SQL.PARSE
(cur_hdl
, stmt_str
, DBMS_SQL
.V7
); -- uses constant DBMS_SQL.V7
The following code works on the client, because the argument is explicitly provided:
DBMS_SQL
.PARSE
(cur_hdl
, stmt_str
, 2
); -- compiles on the client
Examples
To parse SQL statements larger than 32 KB, DBMS_SQL
makes use of PL/SQL tables to pass a table of strings to the PARSE
procedure. These strings are concatenated and then passed on to the Oracle server.
You can declare a local variable as the VARCHAR2S
table-item type, and then use the PARSE
procedure to parse a large SQL statement as VARCHAR2S
.
The definition of the VARCHAR2S
datatype is:
TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
Exceptions
If you create a type/procedure/function/package using DBMS_SQL
that has compilation warnings, an ORA-24344
exception is raised, and the procedure is still created.
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 returning
clause.
Syntax
DBMS_SQL.VARIABLE_VALUE ( c IN INTEGER, name IN VARCHAR2, value OUT <datatype>);
Where <datatype> 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
The following syntax is also supported for the VARIABLE_VALUE
procedure:
DBMS_SQL.VARIABLE_VALUE ( c IN INTEGER, name IN VARCHAR2, <table_variable> IN <datatype>);
Where the <table_variable
> and its corresponding <datatype> can be any one of these matching pairs:
<bdbl_tab> Binary_Double_Table <bflt_tab> Binary_Float_Table <bfile_tab> Bfile_Table <blob_tab> Blob_Table <clob_tab> Clob_Table <date_tab> Date_Table <ids_tab> Interval_Day_To_Second_Table <iym_tab> Interval_Year_To_Month_Table <num_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 <urowid_tab> Urowid_Table <vchr2_tab> Varchar2_Table
For variables containing CHAR
, RAW
, and ROWID
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);
Pragmas
pragma restrict_references(variable_value,RNDS,WNDS);
Parameters
Table 100-18 VARIABLE_VALUE Procedure Parameters
Parameter | Description |
---|---|
c |
ID number of the cursor from which to get the values. |
name |
Name of the variable for which you are retrieving the value. |
value |
Returns the value of the variable for the specified position.
Oracle raises exception |
position |
Relative position of the column in the cursor.
The first column in a statement has position 1. |