7 DBMS_SQL
The DBMS_SQL
package provides an interface for using dynamic SQL to execute data manipulation language (DML) and data definition language (DDL) statements, execute PL/SQL anonymous blocks, and call PL/SQL stored procedures and functions.
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.
This chapter contains the following topics:
-
-
Overview
-
Security model
-
Constants
-
Operational notes
-
Exceptions
-
Examples
-
-
-
Record types
-
Table types
-
Note:
For more information on native dynamic SQL, see Dynamic SQL in PL/SQL (EXECUTE IMMEDIATE Statement) in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide. You can also refer to EXECUTE IMMEDIATE Statement in Oracle Database PL/SQL Language Reference.
Using DBMS_SQL
Overview
TimesTen PL/SQL supports 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 functionality 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 (EXECUTE IMMEDIATE
) is an alternative to DBMS_SQL
that lets you place dynamic SQL statements, PL/SQL blocks, and PL/SQL procedure and function calls 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, such as there being no support for so-called Method 4 (for dynamic SQL statements with an unknown number of inputs or outputs). 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 Oracle Call Interface Programmer's Guide for information about OCI.
PL/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 OCI and the DBMS_SQL
package, including the following:
-
OCI binds by address, while the
DBMS_SQL
package binds by value. -
With
DBMS_SQL
you must callVARIABLE_VALUE
to retrieve the value of anOUT
parameter for an anonymous block, and you must callCOLUMN_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 provideCANCEL
cursor procedures. -
Indicator variables are not required, because
NULL
is fully supported as a value of a PL/SQL variable.
Security Model
DBMS_SQL
is owned by SYS
and compiled with AUTHID CURRENT_USER
. Any DBMS_SQL
subprogram called from an anonymous PL/SQL block is run using the privileges of the current user.
See Definer's Rights and Invoker's Rights (AUTHID Clause) in Oracle TimesTen In-Memory Database Security Guide.
Constants
The constants described in Table 7-1 are used with the language_flag
parameter of the PARSE Procedures. For TimesTen, use NATIVE
.
Table 7-1 DBMS_SQL Constants
Name | Type | Value | Description |
---|---|---|---|
|
|
|
Specifies Oracle Database version 6 behavior (not applicable for TimesTen). |
|
|
|
Specifies typical behavior for the database to which the program is connected. |
|
|
2 |
Specifies Oracle Database version 7 behavior (not applicable for TimesTen). |
Operational Notes
Execution Flow
OPEN_CURSOR
To process a SQL statement, you must have an open cursor. When you call the OPEN_CURSOR Function, you receive a cursor ID number for the data structure representing a valid cursor maintained by TimesTen. These cursors are distinct from cursors defined at the precompiler, OCI, or PL/SQL level, and are used only by the DBMS_SQL
package.
PARSE
Every SQL statement must be parsed by calling the PARSE Procedures. Parsing the statement checks the statement 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 procedure or a package, a timeout can occur if you are still using the procedure in question or a procedure in the package in question. After a call to a procedure, that procedure is considered to be in use until execution has returned to the user side. Any such timeout occurs after a short time.
The execution flow of DBMS_SQL
is shown in Figure 7-1 that follows.
BIND_VARIABLE or BIND_ARRAY
Many DML statements require that data in your program be input to TimesTen. 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 a bind procedure, either the BIND_ARRAY Procedure or the BIND_VARIABLE Procedure, 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, TimesTen 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 OCI.
DEFINE_COLUMN or DEFINE_ARRAY
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 a define procedure (DEFINE_COLUMN
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_ARRAY
procedure to define a PL/SQL collection into which rows are fetched 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.
EXECUTE
Call the EXECUTE
function to run your SQL statement.
FETCH_ROWS or EXECUTE_AND_FETCH
The FETCH_ROWS
function retrieves the rows that satisfy the query. Each successive fetch retrieves another set of rows, until the fetch cannot retrieve any more 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.
VARIABLE_VALUE or COLUMN_VALUE
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 a RETURNING
clause, call VARIABLE_VALUE
to retrieve the values assigned to the output variables when statements were run.
CLOSE_CURSOR
When you no longer need a cursor for a session, close the cursor by calling CLOSE_CURSOR
.
If you neglect to close a cursor, then the memory used by that cursor remains allocated even though it is no longer needed.
Processing Queries
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 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 to determine the value of a column retrieved by
FETCH_ROWS
for your query. If you used anonymous blocks containing calls to PL/SQL procedures, then you must call the VARIABLE_VALUE Procedure to retrieve the values assigned to the output variables of these procedures.
Processing Updates, Inserts, and Deletes
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
, orDELETE
statement by calling the EXECUTE Function. -
If statements have the
RETURNING
clause, then you must call the VARIABLE_VALUE Procedure to retrieve the values assigned to the output variables.
Locating Errors
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, call the LAST_ERROR_POSITION Function immediately after a PARSE
call.
Exceptions
The following table lists the exceptions raised by DBMS_SQL
.
Table 7-2 Exceptions Raised by DBMS_SQL
Exception | Error Code | Description |
---|---|---|
|
-6562 |
Raised by the COLUMN_VALUE Procedure or VARIABLE_VALUE Procedure when the type of the given |
Examples
This section provides these example procedures that use the DBMS_SQL
package.
Example 1: Basic
This example does not require the use of dynamic SQL because the text of the statement is known at compile time, but it illustrates the basic concept underlying the package.
The demo
procedure deletes all employees from a table myemployees
(created from the employees
table of the HR
schema) whose salaries exceed a specified value.
CREATE OR REPLACE PROCEDURE demo(p_salary IN NUMBER) AS
cursor_name INTEGER;
rows_processed INTEGER;
BEGIN
cursor_name := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name, 'DELETE FROM myemployees WHERE salary > :x',
DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', p_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;
Create the myemployees
table and see how many employees have salaries greater than or equal to $15,000:
Command> create table myemployees as select * from employees;
107 rows inserted.
Command> select * from myemployees where salary>=15000;
< 100, Steven, King, SKING, 515.123.4567, 1987-06-17 00:00:00, AD_PRES, 24000,
<NULL>, <NULL>, 90 >
< 101, Neena, Kochhar, NKOCHHAR, 515.123.4568, 1989-09-21 00:00:00, AD_VP, 17000,
<NULL>, 100, 90 >
< 102, Lex, De Haan, LDEHAAN, 515.123.4569, 1993-01-13 00:00:00, AD_VP, 17000,
<NULL>, 100, 90 >
3 rows found.
Run demo
to delete everyone with a salary greater than $14,999 and confirm the results, as follows:
Command> begin
demo(14999);
end;
/
PL/SQL procedure successfully completed.
Command> select * from myemployees where salary>=15000;
0 rows found.
Example 2: Copy Between Tables
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 (in TimesTen commit closes cursors automatically):
COMMIT;
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 INSERT
, UPDATE
, and DELETE
.
Here is an example of a bulk INSERT
statement that adds three new departments to the departments
table in the HR
schema:
DECLARE
stmt VARCHAR2(200);
departid_array DBMS_SQL.NUMBER_TABLE;
deptname_array DBMS_SQL.VARCHAR2_TABLE;
mgrid_array DBMS_SQL.NUMBER_TABLE;
locid_array DBMS_SQL.NUMBER_TABLE;
c NUMBER;
dummy NUMBER;
BEGIN
departid_array(1):= 280;
departid_array(2):= 290;
departid_array(3):= 300;
deptname_array(1) := 'Community Outreach';
deptname_array(2) := 'Product Management';
deptname_array(3) := 'Acquisitions';
mgrid_array(1) := 121;
mgrid_array(2) := 120;
mgrid_array(3) := 70;
locid_array(1):= 1500;
locid_array(2):= 1700;
locid_array(3):= 2700;
stmt := 'INSERT INTO departments VALUES(
:departid_array, :deptname_array, :mgrid_array, :locid_array)';
c := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(c, stmt, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_ARRAY(c, ':departid_array', departid_array);
DBMS_SQL.BIND_ARRAY(c, ':deptname_array', deptname_array);
DBMS_SQL.BIND_ARRAY(c, ':mgrid_array', mgrid_array);
DBMS_SQL.BIND_ARRAY(c, ':locid_array', locid_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;
Following is output from a SELECT
statement, showing the new rows.
Command> select * from departments;
< 10, Administration, 200, 1700 >
...
< 280, Community Outreach, 121, 1500 >
< 290, Product Management, 120, 1700 >
< 300, Acquisitions, 70, 2700 >
30 rows found.
Here is an example of a bulk UPDATE
statement that demonstrates updating salaries for four existing employees in the employees
table in the HR
schema:
DECLARE
stmt VARCHAR2(200);
empno_array DBMS_SQL.NUMBER_TABLE;
salary_array DBMS_SQL.NUMBER_TABLE;
c NUMBER;
dummy NUMBER;
BEGIN
empno_array(1):= 203;
empno_array(2):= 204;
empno_array(3):= 205;
empno_array(4):= 206;
salary_array(1) := 7000;
salary_array(2) := 11000;
salary_array(3) := 13000;
salary_array(4) := 9000;
stmt := 'update employees set salary = :salary_array
WHERE employee_id = :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;
Assume the following entries for the specified employees before running the example, showing salaries of $6500, $10000, $12000, and $8300:
Command> select * from employees where employee_id>=203 and employee_id<=206;
< 203, Susan, Mavris, SMAVRIS, 515.123.7777, 1994-06-07 00:00:00, HR_REP,
6500, <NULL>, 101, 40 >
< 204, Hermann, Baer, HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP,
10000, <NULL>, 101, 70 >
< 205, Shelley, Higgins, SHIGGINS, 515.123.8080, 1994-06-07 00:00:00, AC_MGR,
12000, <NULL>, 101, 110 >
< 206, William, Gietz, WGIETZ, 515.123.8181, 1994-06-07 00:00:00, AC_ACCOUNT,
8300, <NULL>, 205, 110 >
4 rows found.
The following shows the new salaries after running the example.
Command> select * from employees where employee_id>=203 and employee_id<=206;
< 203, Susan, Mavris, SMAVRIS, 515.123.7777, 1994-06-07 00:00:00, HR_REP,
7000, <NULL>, 101, 40 >
< 204, Hermann, Baer, HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP,
11000, <NULL>, 101, 70 >
< 205, Shelley, Higgins, SHIGGINS, 515.123.8080, 1994-06-07 00:00:00, AC_MGR,
13000, <NULL>, 101, 110 >
< 206, William, Gietz, WGIETZ, 515.123.8181, 1994-06-07 00:00:00, AC_ACCOUNT,
9000, <NULL>, 205, 110 >
4 rows found.
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, as follows:
DECLARE
stmt VARCHAR2(200);
dept_no_array DBMS_SQL.NUMBER_TABLE;
c NUMBER;
dummy NUMBER;
BEGIN
dept_no_array(1) := 60;
dept_no_array(2) := 70;
stmt := 'delete from employees where department_id = :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, 1);
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 this example, only the first element of the array is specified by the BIND_ARRAY
call (lower and upper bounds of the array elements are both set to 1), so only employees in department 60 are deleted.
Before running the example, there are five employees in department 60 and one in department 70, where the department number is the last entry in each row:
Command> select * from employees where department_id>=60 and department_id<=70;
< 103, Alexander, Hunold, AHUNOLD, 590.423.4567, 1990-01-03 00:00:00, IT_PROG,
9000, <NULL>, 102, 60 >
< 104, Bruce, Ernst, BERNST, 590.423.4568, 1991-05-21 00:00:00, IT_PROG, 6000,
<NULL>, 103, 60 >
< 105, David, Austin, DAUSTIN, 590.423.4569, 1997-06-25 00:00:00, IT_PROG, 4800,
<NULL>, 103, 60 >
< 106, Valli, Pataballa, VPATABAL, 590.423.4560, 1998-02-05 00:00:00, IT_PROG,
4800, <NULL>, 103, 60 >
< 107, Diana, Lorentz, DLORENTZ, 590.423.5567, 1999-02-07 00:00:00, IT_PROG,
4200, <NULL>, 103, 60 >
< 204, Hermann, Baer, HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP, 10000,
<NULL>, 101, 70 >
6 rows found.
After running the example, only the employee in department 70 remains.
Command> select * from employees where department_id>=60 and department_id<=70;
< 204, Hermann, Baer, HBAER, 515.123.8888, 1994-06-07 00:00:00, PR_REP, 10000,
<NULL>, 101, 70 >
1 row found.
Example 6: Define an Array
This example defines an array.
CREATE OR REPLACE PROCEDURE BULK_PLSQL(deptid NUMBER) IS
names DBMS_SQL.VARCHAR2_TABLE;
sals DBMS_SQL.NUMBER_TABLE;
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, 1);
DBMS_SQL.DEFINE_ARRAY(c, 2, sals, 5, 1);
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;
For example, for department 20 in the employees
table, this produces the following output:
Command> begin
bulk_plsql(20);
end;
/
Name = Hartstein, salary = 13000
Name = Fay, salary = 6000
PL/SQL procedure successfully completed.
Example 7: Describe Columns
This can be used as a substitute for the ttIsql
DESCRIBE
command by using a SELECT *
query on the table to describe. This example describes columns of the employees
table.
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 employees', 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 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;
Here is an abbreviated sample of the output, describing columns of the employees
table, assuming it was run from the HR
schema. Information from only the first two columns is shown here:
col_type = 2
col_maxlen = 7
col_name = EMPLOYEE_ID
col_name_len = 11
col_schema_name = HR
col_schema_name_len = 8
col_precision = 6
col_scale = 0
col_null_ok = false
col_type = 1
col_maxlen = 20
col_name = FIRST_NAME
col_name_len = 10
col_schema_name = HR
col_schema_name_len = 8
col_precision = 0
col_scale = 0
col_null_ok = true
...
Example 8: RETURNING Clause
With this clause, INSERT
, UPDATE
, and DELETE
statements can return values of expressions. These values are returned in bind variables.
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 BIND_ARRAY
is used. VARIABLE_VALUE
must be called to get the values in these bind variables.
Note:
This is similar to VARIABLE_VALUE
, which must be called after running a PL/SQL block with an out-bind inside DBMS_SQL
.
The examples that follow assume a table tab
has been created:
Command> create table tab (c1 number, c2 number);
Examples are shown for single row insert, single row update, multiple row insert, multiple row update, and multiple row delete.
Single row insert
This shows a 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;
The following runs this example and shows the results. The table was initially empty.
Command> declare r NUMBER;
begin
single_Row_insert(100,200,r);
dbms_output.put_line('Product = ' || r);
end;
/
Product = 20000
PL/SQL procedure successfully completed.
Command> select * from tab;
< 100, 200 >
1 row found.
Single Row Update
This shows a single row update. Note that rownum
is an internal variable for row number.
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 = 1 ' ||
'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;
The following runs this example and shows the results, updating the row that was inserted in the previous example.
Command> declare r NUMBER;
begin
single_Row_update(200,300,r);
dbms_output.put_line('Product = ' || r);
end;
/
Product = 60000
PL/SQL procedure successfully completed.
Command> select * from tab;
< 200, 300 >
1 row found.
Multiple Row Insert
This shows a 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;
The following script can be used to run this example in ttIsql
:
declare
c1_array dbms_sql.number_table;
c2_array dbms_sql.number_table;
r_array dbms_sql.number_table;
begin
c1_array(1) := 10;
c1_array(2) := 20;
c1_array(3) := 30;
c2_array(1) := 15;
c2_array(2) := 25;
c2_array(3) := 35;
multi_Row_insert(c1_array,c2_array,r_array);
dbms_output.put_line('Product for row1 = ' || r_array(1));
dbms_output.put_line('Product for row2 = ' || r_array(2));
dbms_output.put_line('Product for row3 = ' || r_array(3));
end;
/
Following are the results. The table was initially empty.
Product for row1 = 150
Product for row2 = 500
Product for row3 = 1050
PL/SQL procedure successfully completed.
Command> select * from tab;
< 10, 15 >
< 20, 25 >
< 30, 35 >
3 rows found.
Multiple Row Update
This shows a 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_OUTPUT.PUT_LINE(n || ' rows updated');
DBMS_SQL.VARIABLE_VALUE(c, 'bnd3', r);-- get value of outbind variable
DBMS_SQL.CLOSE_CURSOR(c);
END;
Note:
Note that bnd1
and bnd2
can be arrays as well. The value of the expression for all the rows updated is in bnd3
. There is no way of differentiating which rows were updated of each value of bnd1
and bnd2
.
The following script can be used to run the example in ttIsql
:
declare
c1 NUMBER;
c2 NUMBER;
r_array dbms_sql.number_table;
begin
c1 := 100;
c2 := 0;
multi_Row_update(c1, c2, r_array);
dbms_output.put_line('Product for row1 = ' || r_array(1));
dbms_output.put_line('Product for row2 = ' || r_array(2));
dbms_output.put_line('Product for row3 = ' || r_array(3));
end;
/
Here are the results, updating the rows that were inserted in the previous example. (The report of the number of rows updated is from the example itself. The products are reported by the test script.)
3 rows updated
Product for row1 = 1500
Product for row2 = 2500
Product for row3 = 3500
PL/SQL procedure successfully completed.
Command> select * from tab;
< 100, 15 >
< 100, 25 >
< 100, 35 >
3 rows found.
Command>
Multiple Row Delete
v) This shows a multiple row delete.
CREATE OR REPLACE PROCEDURE multi_Row_delete
(c1_test NUMBER,
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_VARIABLE(c, 'bnd1', c1_test);
DBMS_SQL.BIND_ARRAY(c, 'bnd2', r);
n := DBMS_SQL.EXECUTE(c);
DBMS_OUTPUT.PUT_LINE(n || ' rows deleted');
DBMS_SQL.VARIABLE_VALUE(c, 'bnd2', r);-- get value of outbind variable
DBMS_SQL.CLOSE_CURSOR(c);
END;
The following script can be used to run the example in ttIsql
.
declare
c1_test NUMBER;
r_array dbms_sql.number_table;
begin
c1_test := 100;
multi_Row_delete(c1_test, r_array);
dbms_output.put_line('Product for row1 = ' || r_array(1));
dbms_output.put_line('Product for row2 = ' || r_array(2));
dbms_output.put_line('Product for row3 = ' || r_array(3));
end;
/
Here are the results, deleting the rows that were updated in the previous example. (The report of the number of rows deleted is from the example itself. The products are reported by the test script.)
3 rows deleted
Product for row1 = 1500
Product for row2 = 2500
Product for row3 = 3500
PL/SQL procedure successfully completed.
Command> select * from tab;
0 rows found.
Note:
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.
Example 9: PL/SQL Block in Dynamic SQL
You can execute a PL/SQL block in dynamic SQL, using either DBMS_SQL
or EXECUTE IMMEDIATE
. This example executes a block using DBMS_SQL
.
Assume the following procedure:
Command> create or replace procedure foo is
begin
dbms_output.put_line('test');
end;
/
Procedure created.
Now execute the procedure in a PL/SQL block using DBMS_SQL
:
Command> declare
c number;
r number;
begin
c := dbms_sql.open_cursor;
dbms_sql.parse(c, 'begin foo; end;', dbms_sql.native);
r := dbms_sql.execute(c);
end;
/
test
PL/SQL procedure successfully completed.
Data Structures
The DBMS_SQL
package defines the following record types and table types.
Note:
-
The
PLS_INTEGER
andBINARY_INTEGER
data types are identical. This document usesBINARY_INTEGER
to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples. -
The
INTEGER
andNUMBER(38)
data types are also identical. This document usesINTEGER
throughout.
Table types
DESC_REC Record Type
Note:
This type has been deprecated in favor of the DESC_REC2 Record Type.
This record type holds the describe information for a single column in a dynamic query. It is the element type of the DESC_TAB
table type and the DESCRIBE_COLUMNS Procedure.
Syntax
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;
Fields
Table 7-3 DESC_REC Fields
Field | Description |
---|---|
|
Type of column |
|
Maximum column length |
|
Name of column |
|
Length of column name |
|
Column schema name |
|
Length of column schema name |
|
Precision of column |
|
Scale of column |
|
Column character set ID |
|
Column character set form |
|
Null column flag, |
DESC_REC2 Record Type
DESC_REC2
is the element type of the DESC_TAB2
table type and the DESCRIBE_COLUMNS2 Procedure.
This record type is identical to DESC_REC
except for the col_name
field, which has been expanded to the maximum possible size for VARCHAR2
. It is therefore preferred to DESC_REC
, which is deprecated, because column name values can be greater than 32 characters.
Syntax
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);
Fields
Table 7-4 DESC_REC2 Fields
Field | Description |
---|---|
|
Type of column |
|
Maximum column length |
|
Name of column |
|
Length of column name |
|
Column schema name |
|
Length of column schema name |
|
Precision of column |
|
Scale of column |
|
Column character set ID |
|
Column character set form |
|
Null column flag, |
DESC_REC3 Record Type
DESC_REC3
is the element type of the DESC_TAB3
table type and the DESCRIBE_COLUMNS3 Procedure.
DESC_REC3
is identical to DESC_REC2
except for two additional fields to hold the type name (type_name
) and type name len (type_name_len
) of a column in a dynamic query. The col_type_name
and col_type_name_len
fields are only populated when the col_type
field value is 109 (the Oracle Database type number for user-defined types), which is not currently used.
Syntax
TYPE desc_rec3 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,
col_type_name varchar2(32767) := '',
col_type_name_len binary_integer := 0);
Fields
Table 7-5 DESC_REC3 Fields
Field | Description |
---|---|
|
Type of column |
|
Maximum column length |
|
Name of column |
|
Length of column name |
|
Column schema name |
|
Length of column schema name |
|
Precision of column |
|
Scale of column |
|
Column character set ID |
|
Column character set form |
|
Null column flag, |
|
Reserved for future use |
|
Reserved for future use |
BINARY_DOUBLE_TABLE Table Type
This is a table of BINARY_DOUBLE
.
Syntax
TYPE binary_double_table IS TABLE OF BINARY_DOUBLE INDEX BY BINARY_INTEGER;
BINARY_FLOAT_TABLE Table Type
This is a table of BINARY_FLOAT
.
Syntax
TYPE binary_float_table IS TABLE OF BINARY_FLOAT INDEX BY BINARY_INTEGER;
BLOB_TABLE Table Type
This is a table of BLOB
.
Syntax
TYPE blob_table IS TABLE OF BLOB INDEX BY BINARY_INTEGER;
CLOB_TABLE Table Type
This is a table of CLOB
.
Syntax
TYPE clob_table IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
DATE_TABLE Table Type
This is a table of DATE
.
Syntax
type date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER;
DESC_TAB Table Type
This is a table of DESC_REC Record Type.
Syntax
TYPE desc_tab IS TABLE OF desc_rec INDEX BY BINARY_INTEGER;
DESC_TAB2 Table Type
This is a table of DESC_REC2 Record Type.
Syntax
TYPE desc_tab2 IS TABLE OF desc_rec2 INDEX BY BINARY_INTEGER;
DESC_TAB3 Table Type
This is a table of DESC_REC3 Record Type.
Syntax
TYPE desc_tab3 IS TABLE OF desc_rec3 INDEX BY BINARY_INTEGER;
INTERVAL_DAY_TO_SECOND_TABLE Table Type
This is a table of DSINTERVAL_UNCONSTRAINED
.
Syntax
TYPE interval_day_to_second_Table IS TABLE OF
DSINTERVAL_UNCONSTRAINED INDEX BY binary_integer;
INTERVAL_YEAR_TO_MONTH_TABLE Table Type
This is a table of YMINTERVAL_UNCONSTRAINED
.
Syntax
TYPE interval_year_to_month_table IS TABLE OF YMINTERVAL_UNCONSTRAINED
INDEX BY BINARY_INTEGER;
NUMBER_TABLE Table Type
This is a table of NUMBER
.
Syntax
TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TIME_TABLE Table Type
This is a table of TIME_UNCONSTRAINED
.
Syntax
TYPE time_table IS TABLE OF TIME_UNCONSTRAINED INDEX BY BINARY_INTEGER;
TIMESTAMP_TABLE Table Type
This is a table of TIMESTAMP_UNCONSTRAINED
.
Syntax
TYPE timestamp_table IS TABLE OF TIMESTAMP_UNCONSTRAINED INDEX BY BINARY_INTEGER;
VARCHAR2_TABLE Table Type
This is table of VARCHAR2(2000)
.
Syntax
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
VARCHAR2A Table Type
This is table of VARCHAR2(32767)
.
Syntax
TYPE varchar2a IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
VARCHAR2S Table Type
This is table of VARCHAR2(256)
.
Note:
This type has been superseded by the VARCHAR2A Table Type. It is supported only for backward compatibility.
Syntax
TYPE varchar2s IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
DBMS_SQL Subprograms
Table 7-6 summarizes the DBMS_SQL
subprograms, followed by a full description of each subprogram.
Table 7-6 DBMS_SQL Package Subprograms
Subprogram | Description |
---|---|
Binds a given value to a given collection. |
|
Binds a given value to a given variable. |
|
Closes given cursor and frees memory. |
|
Returns value of the cursor element for a given position in a cursor. |
|
|
Returns a selected part of a Important: Because TimesTen does not support the The |
Defines a collection to be selected from the given cursor. Used only with |
|
Defines a column to be selected from the given cursor. Used only with |
|
|
Defines a Important: Because TimesTen does not support the The |
Describes the columns for a cursor opened and parsed through |
|
Describes the specified column, as an alternative to DESCRIBE_COLUMNS Procedure. |
|
Describes the specified column, as an alternative to DESCRIBE_COLUMNS Procedure. |
|
Executes a given cursor. |
|
Executes a given cursor and fetches rows. |
|
Fetches a row from a given cursor. |
|
Returns |
|
Returns byte offset in the SQL statement text where the error occurred. |
|
Returns cumulative count of the number of rows fetched. |
|
Returns the rowid of last row processed, TimesTen does not support this feature. |
|
Returns SQL function code for statement. |
|
Returns cursor ID number of new cursor. |
|
Parses given statement. |
|
Takes an opened strongly or weakly typed |
|
Takes an opened, parsed, and executed cursor and transforms or migrates it into a PL/SQL-manageable |
|
Returns value of named variable for given cursor. |
BIND_ARRAY Procedure
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 of the following matching pairs:
<bflt_tab> dbms_sql.Binary_Float_Table
<bdbl_tab> dbms_sql.Binary_Double_Table
<bl_tab> dbms_sql.Blob_Table
<cl_tab> dbms_sql.Clob_Table
<c_tab> dbms_sql.Varchar2_Table
<d_tab> dbms_sql.Date_Table
<ids_tab> dbms_sql.Interval_Day_to_Second_Table
<iym_tab> dbms_sql.Interval_Year_to_Month_Table
<n_tab> dbms_sql.Number_Table
<tm_tab> dbms_sql.Time_Table
<tms_tab> dbms_sql.Timestamp_Table
Notice that the BIND_ARRAY
procedure is overloaded to accept different data types.
Parameters
Table 7-7 BIND_ARRAY Procedure Parameters
Parameter | Description |
---|---|
|
ID number of the cursor where the value is to be bound |
|
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
This section discusses usage notes for the BIND_ARRAY procedure, covering these topics:
General Notes
The length of the bind variable name should be less than or equal to 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. The index1
value 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 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 work space 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), 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 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 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 timestamp_table
IS TABLE OF timestamp_unconstrained
INDEX BY BINARY_INTEGER;
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
<tm_tab> Time_Table
<tms_tab> Timestamp_Table
<ids_tab> Interval_Day_To_Second_Table
<iym_tab> Interval_Year_To_Month_Table
Examples
See Examples.
BIND_VARIABLE Procedure
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 of the following types:
BINARY_DOUBLE
BINARY_FLOAT
BLOB
CLOB CHARACTER SET ANY_CS
DATE
INTERVAL DAY TO SECOND(9,9) (DSINTERVAL_UNCONSTRAINED)
NUMBER
TIME(9) (TIME_UNCONSTRAINED)
TIMESTAMP(9) (TIMESTAMP_UNCONSTRAINED)
VARCHAR2 CHARACTER SET ANY_CS
INTERVAL YEAR TO MONTH(9) (YMINTERVAL_UNCONSTRAINED)
VARRAY
Nested table
Notice that BIND_VARIABLE
is overloaded to accept different data types.
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 following 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);
Parameters
Table 7-8 BIND_VARIABLE Procedure Parameters
Parameter | Description |
---|---|
|
ID number of the cursor where the value is to be bound |
|
Name of the variable in the statement |
|
Value to bind to the variable in the cursor For |
|
Maximum expected 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 last_name FROM employees WHERE salary > :X;
For this example, the corresponding bind call would look similar to the following:
BIND_VARIABLE(cursor_name, ':X', 3500);
Or:
BIND_VARIABLE (cursor_name, 'X', 3500);
The length of the bind variable name should be less than or equal to 30 bytes.
Examples
See Examples.
CLOSE_CURSOR Procedure
This procedure closes a given cursor. The memory allocated to the cursor is released and you can no longer fetch from that cursor.
Syntax
DBMS_SQL.CLOSE_CURSOR (
c IN OUT INTEGER);
Parameters
Table 7-9 CLOSE_CURSOR Procedure Parameters
Parameter | Description |
---|---|
|
|
COLUMN_VALUE Procedure
This procedure is used to access the data fetched by calling the FETCH_ROWS Function. It returns the value of the cursor element for a given position in a given cursor.
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 datatype
can be any of the following types:
BINARY_DOUBLE
BINARY_FLOAT
BLOB
CLOB CHARACTER SET ANY_CS
DATE
INTERVAL DAY TO SECOND(9,9) (DSINTERVAL_UNCONSTRAINED)
NUMBER
TIME(9) (TIME_UNCONSTRAINED)
TIMESTAMP(9) (TIMESTAMP_UNCONSTRAINED)
VARCHAR2 CHARACTER SET ANY_CS
INTERVAL YEAR TO MONTH(9) (YMINTERVAL_UNCONSTRAINED)
VARRAY
Nested table
For variables 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]);
The following syntax enables the COLUMN_VALUE
procedure to accommodate bulk operations:
DBMS_SQL.COLUMN_VALUE(
c IN INTEGER,
position IN INTEGER,
<param_name> IN OUT NOCOPY <table_type>);
Where the param_name
and its corresponding table_type
can be any of these matching pairs:
<bdbl_tab> dbms_sql.Binary_Double_Table
<bflt_tab> dbms_sql.Binary_Float_Table
<bl_tab> dbms_sql.Blob_Table
<cl_tab> dbms_sql.Clob_Table
<c_tab> dbms_sql.Varchar2_Table
<d_tab> dbms_sql.Date_Table
<ids_tab> dbms_sql.Interval_Day_To_Second_Table
<iym_tab> dbms_sql.Interval_Year_To_Month_Table
<n_tab> dbms_sql.Number_Table
<tm_tab> dbms_sql.Time_Table
<tms_tab> dbms_sql.Timestamp_Table
Parameters
Table 7-10 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, where the first column in a statement has position 1 |
|
Value returned from the specified column |
|
Error code for the column value, if applicable |
|
Actual length, before any truncation, of the value in the specified column |
Table 7-11 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, where the first column in a statement has position 1 |
|
Local variable that has been declared The For bulk operations, the subprogram appends the new elements at the appropriate (implicitly maintained) index. Consider an example where the DEFINE_ARRAY Procedure is used, a batch size (the |
Exceptions
ORA-06562: Type of out argument must match type of column or bind variable
This exception 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 DEFINE_COLUMN
.
Examples
See Examples.
DEFINE_ARRAY Procedure
This procedure defines the collection into which the row values are fetched, with a FETCH_ROWS Function call, for a given column. This procedure lets you do batch fetching of rows from a single SELECT
statement. A single fetch brings several rows into the PL/SQL aggregate object.
Scalar 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 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 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 timestamp_table
IS TABLE OF timestamp_unconstrained
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 table_variable
and its corresponding datatype
can be any of the following matching pairs:
<bflt_tab> dbms_sql.Binary_Float_Table
<bdbl_tab> dbms_sql.Binary_Double_Table
<bl_tab> dbms_sql.Blob_Table
<cl_tab> dbms_sql.Clob_Table
<c_tab> dbms_sql.Varchar2_Table
<d_tab> dbms_sql.Date_Table
<n_tab> dbms_sql.Number_Table
<tm_tab> dbms_sql.Time_Table
<tms_tab> dbms_sql.Timestamp_Table
<ids_tab> dbms_sql.Interval_Day_To_Second_Table
<iym_tab> dbms_sql.Interval_Year_To_Month_Table
Note that DEFINE_ARRAY
is overloaded to accept different data types.
Parameters
Table 7-12 DEFINE_ARRAY Procedure Parameters
Parameter | Description |
---|---|
|
ID number of the cursor where the array is to be bound |
|
Relative position of the column in the array being defined, where the first column in a statement has position 1 |
|
Local variable that has been declared as |
|
Number of rows that must be fetched |
|
Lower bound index, the starting point at which results are copied into the collection |
Usage Notes
The count (cnt
) must be an integer greater than zero. The lower_bnd
can be positive, negative, or zero. A query on which a DEFINE_ARRAY
call was issued cannot contain array binds.
Exceptions
ORA-29253: Invalid count argument passed to procedure dbms_sql.define_array
This exception is raised if the count (cnt
) is less than or equal to zero.
Examples
See Examples.
DEFINE_COLUMN Procedure
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 of the following types:
BINARY_DOUBLE
BINARY_FLOAT
BLOB
CLOB CHARACTER SET ANY_CS
DATE
INTERVAL DAY TO SECOND(9,9) (DSINTERVAL_UNCONSTRAINED)
NUMBER
TIME(9) (TIME_UNCONSTRAINED)
TIMESTAMP(9) (TIMESTAMP_UNCONSTRAINED)
INTERVAL YEAR TO MONTH(9) (YMINTERVAL_UNCONSTRAINED)
VARRAY
Nested table
Note that DEFINE_COLUMN
is overloaded to accept different data types.
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);
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 7-13 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, where 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 |
Examples
See Examples.
DESCRIBE_COLUMNS Procedure
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 DBMS_SQL.DESC_TAB);
DBMS_SQL.DESCRIBE_COLUMNS (
c IN INTEGER,
col_cnt OUT INTEGER,
desc_t OUT DBMS_SQL.DESC_REC);
Parameters
Table 7-14 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 |
|
Table to fill in with the description of each of the columns of the query |
Examples
See Examples.
DESCRIBE_COLUMNS2 Procedure
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_t OUT DBMS_SQL.DESC_TAB2);
DBMS_SQL.DESCRIBE_COLUMNS2 (
c IN INTEGER,
col_cnt OUT INTEGER,
desc_t OUT DBMS_SQL.DESC_REC2);
Parameters
Table 7-15 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 |
|
Table to fill in with the description of each of the columns of the query, indexed from 1 to the number of elements in the select list of the query |
DESCRIBE_COLUMNS3 Procedure
This function 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 DBMS_SQL.DESC_TAB3);
DBMS_SQL.DESCRIBE_COLUMNS3 (
c IN INTEGER,
col_cnt OUT INTEGER,
desc_t OUT DBMS_SQL.DESC_REC3);
Parameters
Table 7-16 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 |
|
Table to fill in with the description of each of the columns of the query, indexed from 1 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 opened and parsed, otherwise an error is raised for an invalid cursor ID.
EXECUTE Function
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 7-17 EXECUTE Function Parameters
Parameter | Description |
---|---|
|
Cursor ID number of the cursor to execute |
Return Value
An INTEGER
value that indicates the number of rows processed
EXECUTE_AND_FETCH Function
This function executes the given cursor and fetches rows. It provides the same functionality as calling EXECUTE
and then calling FETCH_ROWS
; however, calling EXECUTE_AND_FETCH
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;
Parameters
Table 7-18 EXECUTE_AND_FETCH Function Parameters
Parameter | Description |
---|---|
|
ID number of the cursor to execute and fetch |
|
Even if an exception is raised, the rows are still fetched and available. |
Return Value
An INTEGER
value indicating the number of rows that were fetched
Exceptions
ORA-01422: Exact fetch returns more than requested number of rows
This exception is raised if the number of rows matching the query is not 1.
FETCH_ROWS Function
This function fetches a row from a given cursor.
A DEFINE_ARRAY Procedure call defines the collection into which the row values are fetched.
A FETCH_ROWS
call fetches the specified number of rows, according to the cnt
parameter of the DEFINE_ARRAY
call. When you fetch the rows, they are copied into DBMS_SQL
buffers until you execute a COLUMN_VALUE Procedure call, for each column, at which time the rows are copied into the table that was passed as an argument to COLUMN_VALUE
. The rows are placed in positions lower_bnd
, lower_bnd
+1, lower_bnd
+2, and so on, according to the lower_bnd
setting in the DEFINE_ARRAY
call. While there are still rows coming in, the user keeps issuing FETCH_ROWS
and COLUMN_VALUE
calls. You can call FETCH_ROWS
repeatedly as long as there are rows remaining to be fetched.
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;
Parameters
Table 7-19 FETCH_ROWS Function Parameters
Parameter | Description |
---|---|
|
ID number of the cursor to fetch |
Return Value
An INTEGER
value indicating the number of rows that were fetched
Examples
See Examples.
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;
Parameters
Table 7-20 IS_OPEN Function Parameters
Parameter | Description |
---|---|
|
Cursor ID number of the cursor to check |
Return Value
TRUE
for any cursor number that has been opened but not closed, or FALSE
for a NULL
cursor number
Note that the CLOSE_CURSOR Procedure nulls out the cursor variable passed to it.
Exceptions
ORA-29471 DBMS_SQL access denied
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 raises this error, meaning that DBMS_SQL
is non-operational for the session.
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;
Return Value
An INTEGER
value indicating the byte offset in the SQL statement text where the error occurred
Usage Notes
Call this function after a PARSE
call, before any other DBMS_SQL
procedures or functions are called.
LAST_ROW_COUNT Function
This function returns the cumulative count of the number of rows fetched.
Syntax
DBMS_SQL.LAST_ROW_COUNT
RETURN INTEGER;
Return Value
An INTEGER
value indicating the cumulative count of the number of rows that were fetched
Usage Notes
Call this function after a FETCH_ROWS
or an EXECUTE_AND_FETCH
call. If called after an EXECUTE
call, the value returned is zero.
LAST_ROW_ID Function
This function returns the rowid of the last row processed, but NULL
for TimesTen.
TimesTen does not support this feature.
Syntax
DBMS_SQL.LAST_ROW_ID
RETURN ROWID;
Return Value
NULL
for TimesTen
LAST_SQL_FUNCTION_CODE Function
This function returns the SQL function code for the statement.
These codes are listed in the OCI Function Codes table in Oracle Call Interface Programmer's Guide.
Syntax
DBMS_SQL.LAST_SQL_FUNCTION_CODE
RETURN INTEGER;
Return Value
An INTEGER
value indicating the SQL function code for the statement
Usage Notes
Call this function immediately after the SQL statement is run. Otherwise, the return value is undefined.
OPEN_CURSOR Function
This procedure opens a new cursor.
The second overload takes a security_level
parameter to apply fine-grained control to the security of the opened cursor. In TimesTen, however, there is no security enforcement: security_level=0
.
When you no longer need this cursor, you must close it explicitly by calling the CLOSE_CURSOR Procedure.
Syntax
DBMS_SQL.OPEN_CURSOR
RETURN INTEGER;
DBMS_SQL.OPEN_CURSOR (
security_level IN INTEGER)
RETURN INTEGER;
Parameters
Table 7-21 OPEN_CURSOR Function Parameters
Parameter | Description |
---|---|
|
Specifies the level of security protection to enforce on the opened cursor. Only the security level 0 is valid in TimesTen (levels 1 and 2 are not supported).
|
Return Value
The cursor ID of the new cursor
Usage Notes
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.
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 PARSE
procedure:
-
Taking a
VARCHAR2
statement as an argument -
Taking
VARCHAR2A
, table ofVARCHAR2(32767)
, as an argument -
Taking
VARCHAR2S
, table ofVARCHAR2(32767)
, as an argument -
Taking a
CLOB
statement as an argumentYou can use the
CLOB
overload version of the parse procedure to parse a SQL statement larger than 32 KB.
The VARCHAR2A
overload version of 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 single VARCHAR2
variable by splitting up the statement.
Syntax
DBMS_SQL.PARSE (
c IN INTEGER,
statement IN VARCHAR2,
language_flag IN INTEGER);
DBMS_SQL.PARSE (
c IN INTEGER,
statement IN DBMS_SQL.VARCHAR2A,
lb IN INTEGER,
ub IN INTEGER,
lfflg IN BOOLEAN,
language_flag IN INTEGER);
DBMS_SQL.PARSE (
c IN INTEGER,
statement IN DBMS_SQL.VARCHAR2S,
lb IN INTEGER,
ub IN INTEGER,
lfflg IN BOOLEAN,
language_flag IN INTEGER);
DBMS_SQL.PARSE (
c IN INTEGER,
statement IN CLOB,
language_flag IN INTEGER);
Parameters
Table 7-22 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 32 KB can be stored in CLOBs. Unlike PL/SQL statements, your SQL statement should not include a final semicolon. For example:
|
|
Lower bound for elements in the statement |
|
Upper bound for elements in the statement |
|
|
|
Flag to determine how the SQL statement is handled For TimesTen, use the |
Usage Notes
-
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.NATIVE); -- uses constant DBMS_SQL.NATIVE
The following code works on the client, because the argument is explicitly provided:
DBMS_SQL.PARSE(cur_hdl, stmt_str, 1); -- compiles on the client
-
The
VARCHAR2S
type is supported only for backward compatibility. You are advised to useVARCHAR2A
instead.
Exceptions
ORA-24344: Success with compilation error
If you create a type, procedure, function, or package that has compilation warnings, this exception is raised but the object is still created.
Examples
See Examples.
TO_CURSOR_NUMBER Function
This function takes an opened strongly or weakly-typed REF CURSOR and transforms it into a DBMS_SQL
cursor number.
Syntax
DBMS_SQL.TO_CURSOR_NUMBER(
rc IN OUT SYS_REFCURSOR)
RETURN INTEGER;
Parameters
Table 7-23 TO_CURSOR_NUMBER Function Parameters
Parameter | Description |
---|---|
|
REF CURSOR to be transformed into a cursor number |
Return Value
A DBMS_SQL
manageable cursor number transformed from a REF CURSOR
Usage Notes
-
The REF CURSOR passed in has to be opened (
OPEN_CURSOR
). -
Once the REF CURSOR is transformed into a
DBMS_SQL
cursor number, the REF CURSOR is no longer accessible by any native dynamic SQL operations. -
Toggling between a REF CURSOR and
DBMS_SQL
cursor number after a fetch has started is not allowed.
Examples
CREATE OR REPLACE PROCEDURE DO_QUERY1(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;
BEGIN
-- sql_stmt := 'select * from employees';
OPEN src_cur FOR sql_stmt;
-- 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;
You could execute this procedure as follows:
Command> begin
do_query1('select * from employees');
end;
/
PL/SQL procedure successfully completed.
TO_REFCURSOR Function
This function takes an opened (by OPEN_CURSOR
), parsed (by PARSE
), and executed (by EXECUTE
) cursor and transforms or migrates it into a PL/SQL-manageable REF CURSOR (a weakly-typed cursor) that can be consumed by PL/SQL native dynamic SQL.
This subprogram is only used with SELECT
cursors.
Syntax
DBMS_SQL.TO_REFCURSOR(
cursor_number IN OUT INTEGER)
RETURN SYS_REFCURSOR;
Parameters
Table 7-24 TO_REFCURSOR Function Parameters
Parameter | Description |
---|---|
|
Cursor number of the cursor to be transformed into a REF CURSOR |
Return Value
A PL/SQL REF CURSOR transformed from a DBMS_SQL
cursor number
Usage notes
-
The cursor passed in by the
cursor_number
has to be opened, parsed, and executed. Otherwise an error is raised. -
Once the
cursor_number
is transformed into a REF CURSOR, it is no longer accessible by anyDBMS_SQL
operations. -
After a
cursor_number
is transformed into a REF CURSOR, usingIS_OPEN
results in an error. -
Toggling between REF CURSOR and
DBMS_SQL
cursor number after starting to fetch is not allowed. An error is raised.
Examples
CREATE OR REPLACE PROCEDURE DO_QUERY2(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;
The following example executes this procedure for a manager ID of 103.
Command> begin
do_query2(103);
end;
/
EMPNO DEPTNO
----- ------
104 60
105 60
106 60
107 60
PL/SQL procedure successfully completed.
VARIABLE_VALUE Procedure
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 of DML statements with a RETURNING
clause.
Syntax
DBMS_SQL.VARIABLE_VALUE (
c IN INTEGER,
name IN VARCHAR2,
value OUT NOCOPY <datatype>);
Where datatype
can be any of the following types:
BINARY_DOUBLE
BINARY_FLOAT
BLOB
CLOB CHARACTER SET ANY_CS
DATE
INTERVAL DAY TO SECOND(9,9) (DSINTERVAL_UNCONSTRAINED)
NUMBER
TIME(9) (TIME_UNCONSTRAINED)
TIMESTAMP(9) (TIMESTAMP_UNCONSTRAINED)
VARCHAR2 CHARACTER SET ANY_CS
INTERVAL YEAR TO MONTH(9) (YMINTERVAL_UNCONSTRAINED)
VARRAY
Nested 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);
The following syntax enables the VARIABLE_VALUE
procedure to accommodate bulk operations:
DBMS_SQL.VARIABLE_VALUE (
c IN INTEGER,
name IN VARCHAR2,
value OUT NOCOPY <table_type>);
For bulk operations, table_type
can be any of the following:
dbms_sql.Binary_Double_Table
dbms_sql.Binary_Float_Table
dbms_sql.Blob_Table
dbms_sql.Clob_Table
dbms_sql.Date_Table
dbms_sql.Interval_Day_To_Second_Table
dbms_sql.Interval_Year_To_Month_Table
dbms_sql.Number_Table
dbms_sql.Time_Table
dbms_sql.Timestamp_Table
dbms_sql.Varchar2_Table
Parameters
Table 7-25 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 |
|
For the single row option, value of the variable for the specified position For the array option, local variable that has been declared Note: For bulk operations, |
Exceptions
ORA-06562: Type of out argument must match type of column or bind variable
This is raised if the type of the output parameter differs from the type of the value as defined by the BIND_VARIABLE
call.
Examples
See Examples.