Dynamic cursors in PL/SQL

create or replace package dynamic_cursor is

type t_crs is ref cursor;

procedure dyn_sel (

tab_name in varchar2,

field_name in varchar2,

val in varchar2,

crs in out t_crs);

procedure openCursor;

end dynamic_cursor;

create or replace package body dynamic_cursor as

procedure dyn_sel (

tab_name in varchar2,

field_name in varchar2,

val in varchar2,

crs in out t_crs)

is

stmt varchar2(100);
begin

stmt := 'select * from ' || tab_name || ' where ' || field_name || ' =
:1 ';

open crs for stmt using val;

end dyn_sel;

procedure openCursor is

tc t_crs;

f1 varchar2(50);

f2 varchar2(50);

begin

dyn_sel('test_for_cursor','a','two',tc);

loop

fetch tc into f1,f2;

exit when tc%notfound;

dbms_output.put_line(f2);

end loop;

end openCursor;

end dynamic_cursor;

begin

dynamic_cursor.openCursor;

end;

/
Open cursor with a dynamic select statement
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE showemps (where_in IN VARCHAR2 := NULL)
  2  IS
  3     TYPE cv_typ IS REF CURSOR;
  4     cv cv_typ;
  5     v_nm emp.ename%TYPE;
  6  BEGIN
  7     OPEN cv FOR
  8        'SELECT ename FROM emp WHERE ' || NVL (where_in, '1=1');
  9     LOOP
 10        FETCH cv INTO v_nm;
 11        EXIT WHEN cv%NOTFOUND;
 12        DBMS_OUTPUT.PUT_LINE (v_nm);
 13     END LOOP;
 14     CLOSE cv;
 15  END;
 16  /

Procedure created.

SQL>
SQL> show error
No errors.
SQL>
SQL> EXEC showemps(null);
SMITH

25. 3. 3. Opening multiple queries using the same cursor

variable
SQL>
SQL> DECLARE
  2    TYPE rc is REF CURSOR;
  3    refCursorValue rc;
  4    productRecord product%ROWTYPE;
  5    v_product_description VARCHAR2(20);
  6    v_company_short_name VARCHAR2(30);
  7  BEGIN
  8    OPEN refCursorValue FOR SELECT *from product;
  9
 10    LOOP
 11      FETCH refCursorValue INTO productRecord;
 12      EXIT WHEN refCursorValue%NOTFOUND;
 13      dbms_output.put_line(to_char(productRecord.product_id)||' '||
 14      rpad(productRecord.product_description,20,' '));
 15    END LOOP;
 16
 17    OPEN refCursorValue FOR SELECT h.product_description,o.company_short_na
me
 18                  FROM company o,product h
 19                  WHERE o.product_id =h.product_id;
 20
 21    LOOP
 22      FETCH refCursorValue INTO v_product_description,v_company_short_name;
 23      EXIT WHEN refCursorValue%NOTFOUND;
 24      dbms_output.put_line(rpad(v_product_description,20,' ')||' '||
rpad(v_company_short_name,30,' '));
 25    END LOOP;
 26    CLOSE refCursorValue;
 27  END;
 28  /
1 Java
2 Oracle
3 C#
4 Javascript
5 Python
Java                 A Inc.
Java                 B Inc.
Java                 C Inc.
Oracle               D Inc.
Oracle               E Inc.
Oracle               F Inc.

PL/SQL procedure successfully completed

Oracle/PLSQL: Procedure that outputs a dynamic PLSQL

cursor
Question:  In Oracle, I have a table called "wine" and a stored procedure that outputs a cursor based on the "wine"
table.

I've created an HTML Form where the user can enter any combination of three values to retrieve results from the
"wine" table. My problem is that I need a general "select" statement that will work no matter what value(s), the user
enters.

Example:

parameter_1= "Chianti"
parameter_2= "10"
parameter_3= wasn't entered by the user but I have to use in the select statement. And this is my problem. How to
initialize this parameter to get all rows for column3?

SELECT * FROM wine

WHERE column1 = parameter_1
AND column2 = parameter_2
AND column3 = parameter_3;.

The output of my stored procedure must be a cursor.

Answer:  To solve your problem, you will need to output a dynamic PLSQL cursor in Oracle.

Let's take a look at how we can do this. We've divided this process into 3 steps.

Step 1 - Table Definition

First, we need a table created in Oracle called "wine". Below is the create statement for the wine table.

create table wine

( col1 varchar2(40),
  col2 varchar2(40),
  col3 varchar2(40)
);

We've made this table definition very simple, for demonstration purposes.

Step 2 - Create package

Next, we've created a package called "winepkg" that contains our cursor definition. This needs to be done so that we
can use a cursor as an output parameter in our stored procedure.
create or replace PACKAGE winepkg
IS
   /* Define the REF CURSOR type. */
   TYPE wine_type IS REF CURSOR RETURN wine%ROWTYPE;
END winepkg;

This cursor will accept all fields from the "wine" table.

Step 3 - Create stored procedure

Our final step is to create a stored procedure to return the cursor. It accepts three parameters (entered by the user on
the HTML Form) and returns a cursor (c1) of type "wine_type" which was declared in Step 2.

The procedure will determine the appropriate cursor to return, based on the value(s) that have been entered by the
user (input parameters).

create or replace procedure find_wine2

  (col1_in in varchar2,
   col2_in in varchar2,
   col3_in in varchar2,
   c1 out winepkg.wine_type)
as

BEGIN

   /* all columns were entered */

   IF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
      select *
      from wine
      where wine.col1 = col1_in
      and  wine.col2 = col2_in
      and  wine.col3 = col3_in;

   /* col1 and col2 were entered */

   ELSIF (length(col1_in) > 0) and (length(col2_in) > 0) and (length(col3_in) = 0)
   THEN
      OPEN c1 FOR
      select *
      from wine
      where wine.col1 = col1_in
      and  wine.col2 = col2_in;

   /* col1 and col3 were entered */

   ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
      select *
      from wine
      where wine.col1 = col1_in
      and  wine.col3 = col3_in;

   /* col2 and col3 where entered */

   ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
      select *
      from wine
      where wine.col2 = col2_in
      and  wine.col3 = col3_in;

   /* col1 was entered */

   ELSIF (length(col1_in) > 0) and (length(col2_in) = 0) and (length(col3_in) = 0)
   THEN
      OPEN c1 FOR
      select *
      from wine
      where wine.col1 = col1_in;

   /* col2 was entered */

   ELSIF (length(col1_in) = 0) and (length(col2_in) > 0) and (length(col3_in) = 0)
   THEN
      OPEN c1 FOR
      select *
      from wine
      where wine.col2 = col2_in;

   /* col3 was entered */

   ELSIF (length(col1_in) = 0) and (length(col2_in) = 0) and (length(col3_in) > 0)
   THEN
      OPEN c1 FOR
      select *
      from wine
      where wine.col3 = col3_in;

   END IF;

END find_wine2;

1.9 Cursors in PL/SQL

Every SQL statement executed by the RDBMS has a private SQL area that contains information
about the SQL statement and the set of data returned. In PL/SQL, a cursor is a name assigned to
a specific private SQL area for a specific SQL statement. There can be either static cursors,
whose SQL statement is determined at compile time, or dynamic cursors, whose SQL statement
is determined at runtime. Static cursors are covered in greater detail in this section. Dynamic
cursors in PL/SQL are implemented via the built-in package DBMS_SQL. See the book Oracle
Built-in Packages and the corresponding Oracle PL/SQL Built-ins Pocket Reference , both from
O'Reilly & Associates, for full coverage on DBMS_SQL and the other built-in packages.
1.9.1 Explicit Cursors

Explicit cursors are SELECT statements that are DECLAREd explicitly in the declaration
section of the current block or in a package specification. Use OPEN, FETCH, and CLOSE in
the execution or exception sections of your programs.

1.9.1.1 Declaring explicit cursors

To use an explicit cursor, you must first declare it in the declaration section of a block or
package. There are three types of explicit cursor declarations:

 A cursor without parameters, such as:

CURSOR company_cur IS SELECT company_id FROM

company;

 A cursor that accepts arguments through a parameter list:

CURSOR company_cur (id_in IN NUMBER) IS SELECT name FROM

company WHERE company_id = id_in;

 A cursor header that contains a RETURN clause in place of the SELECT statement:

CURSOR company_cur (id_in IN NUMBER) RETURN company%ROWTYPE IS

SELECT * FROM company;

This technique can be used in packages to hide the implementation of the cursor in the package
body. See the Section 1.14 " section for more information.

1.9.1.2 Opening explicit cursors

To open a cursor, use the following syntax:

OPEN cursor_name [(argument [,argument ...])];

where cursor_name is the name of the cursor as declared in the declaration section. The
arguments are required if the definition of the cursor contains a parameter list.

You must open an explicit cursor before you can fetch rows from that cursor. When the cursor is
opened, the processing includes the PARSE, BIND, OPEN, and EXECUTE statements. This
OPEN processing includes: determining an execution plan, associating host variables and cursor
parameters with the placeholders in the SQL statement, determining the result set, and, finally,
setting the current row pointer to the first row in the result set.
When using a cursor FOR loop, the OPEN is implicit in the FOR statement. If you try to open a
cursor that is already open, PL/SQL will raise an "ORA-06511: PL/SQL: cursor already open"
exception.

1.9.1.3 Fetching from explicit cursors

The FETCH statement places the contents of the current row into local variables. To retrieve all
rows in a result set, each row needs to be fetched. The syntax for a FETCH statement is:

FETCH cursor_name INTO record_or_variable_list;

where cursor_name is the name of the cursor as declared and opened.

1.9.1.4 Closing explicit cursors

The syntax of the CLOSE statement is:

CLOSE cursor_name;

where cursor_name is the name of the cursor declared and opened.

After all rows have been fetched, a cursor needs to be closed. Closing a cursor releases the
private SQL area used by the cursor, freeing the memory used by that cursor.

If you declare a cursor in a local anonymous, procedure, or function block, that cursor will
automatically close when the block terminates. Package-based cursors must be closed explicitly,
or they stay open for the duration of your session. Closing a cursor that is not open raises an
INVALID CURSOR exception.

1.9.1.5 Explicit cursor attributes

There are four attributes associated with cursors: ISOPEN, FOUND, NOTFOUND, and
ROWCOUNT. These attributes can be accessed with the % delimiter to obtain information about
the state of the cursor. The syntax for a cursor attribute is:

cursor_name%attribute

where cursor_name is the name of the explicit cursor.

The behaviors of the explicit cursor attributes are described in the following table.
Attribute Description
%ISOPEN TRUE if cursor is open.

FALSE if cursor is not open.

%FOUND INVALID_CURSOR is raised if cursor has not been OPENed.

NULL before the first fetch.

TRUE if record was fetched successfully.

FALSE if no row was returned.

INVALID_CURSOR if cursor has been CLOSEd.

%NOTFOUND INVALID_CURSOR is raised if cursor has not been OPENed.

NULL before the first fetch.

FALSE if record was fetched successfully.

TRUE if no row was returned.

INVALID_CURSOR if cursor has been CLOSEd.

%ROWCOUNT INVALID_CURSOR is raised if cursor has not been OPENed.

The number of rows fetched from the cursor.

INVALID_CURSOR if cursor has been CLOSEd.

Frequently a cursor attribute is checked as part of a WHILE loop that fetches rows from a cursor:

DECLARE caller_rec caller_pkg.caller_cur%ROWTYPE; BEGIN OPEN

caller_pkg.caller_cur; LOOP FETCH caller_pkg.caller_cur into
caller_rec; EXIT WHEN caller_pkg.caller_cur%NOTFOUND
OR caller_pkg.caller_cur%ROWCOUNT > 10; UPDATE call
SET caller_id = caller_rec.caller_id WHERE call_timestamp <
SYSDATE; END LOOP; CLOSE caller_pkg.caller_cur; END;

1.9.2 Implicit Cursors

Whenever a SQL statement is directly in the execution or exception section of a PL/SQL block,
you are working with implicit cursors. These statements include INSERT, UPDATE, DELETE,
and SELECT INTO statements. Unlike explicit cursors, implicit cursors do not need to be
declared, OPENed, FETCHed, or CLOSEd.

SELECT statements handle the %FOUND and %NOTFOUND attributes differently from
explicit cursors. When an implicit SELECT statement does not return any rows, PL/SQL
immediately raises the NO_DATA_FOUND exception and control passes to the exception
section. When an implicit SELECT returns more than one row, PL/SQL immediately raises the
TOO_MANY_ROWS exception and control passes to the exception section.

Implicit cursor attributes are referenced via the SQL cursor. For example:

BEGIN UPDATE activity SET last_accessed := SYSDATE WHERE UID =

user_id; IF SQL%NOTFOUND THEN INSERT INTO activity_log
(uid,last_accessed) VALUES (user_id,SYSDATE); END IF END;

SQL Attributes Description

%ISOPEN Always FALSE since the cursor is opened implicitly and closed
immediately after the statement is executed.
%FOUND NULL before the statement.

TRUE if one or more rows were inserted, updated, or deleted or if

only one row was selected.

FALSE if no row was selected, updated, inserted, or deleted.

%NOTFOUND NULL before the statement.

TRUE if no row was selected, updated, inserted, or deleted.

FALSE if one or more rows were inserted, updated, or deleted.

%ROWCOUNT The number of rows affected by the cursor.
%BULK_ROWCOUNT A pseudo index-by table containing the numbers of rows affected by
(Oracle8 i) the statements executed in bulk bind operations. See the "Bulk Binds
(Oracle8 i )" section for more information on
%BULK_ROWCOUNT.

Use the RETURNING clause in INSERT, UPDATE, and DELETE statements to obtain data
modified by the associated DML statement. This clause allows you to avoid an additional
SELECT statement to query the results of the DML statement. For example:
BEGIN UPDATE activity SET last_accessed := SYSDATE WHERE UID =
user_id RETURNING last_accessed, cost_center INTO timestamp,
chargeback_acct;

1.9.2.1 The SELECT FOR UPDATE clause

By default, the Oracle RDBMS locks rows as they are changed. To lock all rows in a result set,
use the FOR UPDATE clause in your SELECT statement when you OPEN the cursor, instead of
when you change the data. Using the FOR UPDATE clause does not require you to actually
make changes to the data; it only locks the rows when opening the cursor. These locks are
released on the next COMMIT or ROLLBACK. As always, these row locks do not affect other
SELECT statements unless they, too, are FOR UPDATE. The FOR UPDATE clause is appended
to the end of the SELECT statement and has the following syntax:

SELECT ... FROM ... FOR UPDATE [OF column_reference] [NOWAIT];

where column_reference is a comma-delimited list of columns that appear in the SELECT

clause. The NOWAIT keyword tells the RDBMS to not wait for other blocking locks to be
released. The default is to wait forever.

In the following example, only columns from the inventory (pet) table are referenced FOR
UPDATE, so no rows in the dog_breeds (dog) table are locked when hounds_in_stock_cur is
opened:

DECLARE CURSOR hounds_in_stock_cur IS SELECT pet.stock_no,

pet.breeder, dog.size FROM dog_breeds dog ,inventory pet
WHERE dog.breed = pet.breed AND dog.class = 'HOUND' FOR
UPDATE OF pet.stock_no, pet.breeder; BEGIN

1.9.2.2 The WHERE CURRENT OF clause

UPDATE and DELETE statements can use a WHERE CURRENT OF clause if they reference a
cursor declared FOR UPDATE. This syntax indicates that the UPDATE or DELETE should
modify the current row identified by the FOR UPDATE cursor. The syntax is:

[UPDATE | DELETE ] ... WHERE CURRENT OF cursor_name;

By using WHERE CURRENT OF, you do not have to repeat the WHERE clause in the SELECT
statement. For example:

DECLARE CURSOR wip_cur IS SELECT acct_no, enter_date FROM wip

WHERE enter_date < SYSDATE -7 FOR UPDATE; BEGIN FOR wip_rec IN
wip_cur LOOP INSERT INTO acct_log (acct_no, order_date)
VALUES (wip_rec.acct_no, wip_rec.enter_ date); DELETE
FROM wip WHERE CURRENT OF wip_cur; END LOOP; END;
1.9.3 Cursor Variables

A cursor variable is a data structure that points to a cursor object, which in turn points to
the cursor's result set. You can use cursor variables to more easily retrieve rows in a result
set from client and server programs. You can also use cursor variables to hide minor
variations in queries.

The syntax for a REF_CURSOR type is:

TYPE ref_cursor_name IS REF CURSOR [RETURN record_type];

If you do not include a RETURN clause, then you are declaring a weak REF CURSOR.
Cursor variables declared from weak REF CURSORs can be associated with any query at
runtime. A REF CURSOR declaration with a RETURN clause defines a "strong" REF
CURSOR. A cursor variable based on a strong REF CURSOR can be associated with
queries whose result sets match the number and datatype of the record structure after the
RETURN at runtime.

To use cursor variables, you must first create a REF_CURSOR type, then declare a cursor
variable based on that type.

The following example shows the use of both weak and strong REF CURSORs:

DECLARE -- Create a cursor type based on the companies table.

TYPE company_curtype IS REF CURSOR RETURN companies%ROWTYPE; --
Create the variable based on the REF CURSOR. company_cur
company_curtype; -- And now the weak, general approach. TYPE
any_curtype IS REF CURSOR; generic_curvar any_curtype;

The syntax to OPEN a cursor variable is:

OPEN cursor_name FOR select_statement;

FETCH and CLOSE a cursor variable using the same syntax as for explicit cursors. There
are a number of restrictions on cursor variables:

 Cursor variables cannot be declared in a package since they do not have a persistent
state.

 You cannot use the FOR UPDATE clause with cursor variables.

 You cannot assign NULLs to a cursor variable nor use comparison operators to test
for equality, inequality, or nullity.
 Neither database columns nor collections can store cursor variables.

 You cannot use RPCs to pass cursor variables from one server to another.

 Cursor variables cannot be used with the dynamic SQL built-in package
DBMS_SQL.

11
Native Dynamic SQL
A happy and gracious flexibility ... --Matthew Arnold

This chapter shows you how to use native dynamic SQL (dynamic SQL for short), a PL/SQL
interface that makes your applications more flexible and versatile. You learn simple ways to
write programs that can build and process SQL statements "on the fly" at run time.

Within PL/SQL, you can execute any kind of SQL statement (even data definition and data
control statements) without resorting to cumbersome programmatic approaches. Dynamic SQL
blends seamlessly into your programs, making them more efficient, readable, and concise.

This chapter discusses the following topics:

What Is Dynamic SQL?

The Need for Dynamic SQL

Using the EXECUTE IMMEDIATE Statement

Using the OPEN-FOR, FETCH, and CLOSE Statements

Tips and Traps for Dynamic SQL

What Is Dynamic SQL?

Most PL/SQL programs do a specific, predictable job. For example, a stored procedure might
accept an employee number and salary increase, then update the sal column in the emp table. In
this case, the full text of the UPDATE statement is known at compile time. Such statements do not
change from execution to execution. So, they are called static SQL statements.

However, some programs must build and process a variety of SQL statements at run time. For
example, a general-purpose report writer must build different SELECT statements for the various
reports it generates. In this case, the full text of the statement is unknown until run time. Such
statements can, and probably will, change from execution to execution. So, they are called
dynamic SQL statements.

Dynamic SQL statements are stored in character strings built by your program at run time. Such
strings must contain the text of a valid SQL statement or PL/SQL block. They can also contain
placeholders for bind arguments. A placeholder is an undeclared identifier, so its name, to which
you must prefix a colon, does not matter. For example, PL/SQL makes no distinction between
the following strings:

'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm'

'DELETE FROM emp WHERE sal > :s AND comm < :c'

To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. However,
to process a multi-row query (SELECT statement), you must use the OPEN-FOR, FETCH, and CLOSE
statements.

The Need for Dynamic SQL

You need dynamic SQL in the following situations:

 You want to execute a SQL data definition statement (such as CREATE), a data control
statement (such as GRANT), or a session control statement (such as ALTER SESSION). In PL/SQL,
such statements cannot be executed statically.
 You want more flexibility. For example, you might want to defer your choice of schema objects
until run time. Or, you might want your program to build different search conditions for the
WHERE clause of a SELECT statement. A more complex program might choose from various SQL
operations, clauses, etc.
 You use package DBMS_SQL to execute SQL statements dynamically, but you want better
performance, something easier to use, or functionality that DBMS_SQL lacks such as support for
objects and collections. (For a comparison with DBMS_SQL, see Oracle9i Application Developer's
Guide - Fundamentals.)

Using the EXECUTE IMMEDIATE Statement

The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL
statement or an anonymous PL/SQL block. The syntax is

EXECUTE IMMEDIATE dynamic_string

[INTO {define_variable[, define_variable]... | record}]

[USING [IN | OUT | IN OUT] bind_argument

[, [IN | OUT | IN OUT] bind_argument]...]

[{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];

where dynamic_string is a string expression that represents a SQL statement or PL/SQL block,
define_variable is a variable that stores a selected column value, and record is a user-defined
or %ROWTYPE record that stores a selected row. An input bind_argument is an expression whose
value is passed to the dynamic SQL statement or PL/SQL block. An output bind_argument is a
variable that stores a value returned by the dynamic SQL statement or PL/SQL block.

Except for multi-row queries, the dynamic string can contain any SQL statement (without the
terminator) or any PL/SQL block (with the terminator). The string can also contain placeholders
for bind arguments. However, you cannot use bind arguments to pass the names of schema
objects to a dynamic SQL statement. For the right way, see "Making Procedures Work on
Arbitrarily Named Schema Objects".

Used only for single-row queries, the INTO clause specifies the variables or record into which
column values are retrieved. For each value retrieved by the query, there must be a
corresponding, type-compatible variable or field in the INTO clause.

Used only for DML statements that have a RETURNING clause (without a BULK COLLECT clause),
the RETURNING INTO clause specifies the variables into which column values are returned. For
each value returned by the DML statement, there must be a corresponding, type-compatible
variable in the RETURNING INTO clause.

You can place all bind arguments in the USING clause. The default parameter mode is IN. For
DML statements that have a RETURNING clause, you can place OUT arguments in the RETURNING
INTO clause without specifying the parameter mode, which, by definition, is OUT. If you use both
the USING clause and the RETURNING INTO clause, the USING clause can contain only IN
arguments.

At run time, bind arguments replace corresponding placeholders in the dynamic string. So, every
placeholder must be associated with a bind argument in the USING clause and/or RETURNING INTO
clause. You can use numeric, character, and string literals as bind arguments, but you cannot use
Boolean literals (TRUE, FALSE, and NULL). To pass nulls to the dynamic string, you must use a
workaround. See "Passing Nulls".
Dynamic SQL supports all the SQL datatypes. So, for example, define variables and bind
arguments can be collections, LOBs, instances of an object type, and refs. As a rule, dynamic SQL
does not support PL/SQL-specific types. So, for example, define variables and bind arguments
cannot be Booleans or index-by tables. The only exception is that a PL/SQL record can appear in
the INTO clause.

You can execute a dynamic SQL statement repeatedly using new values for the bind arguments.
However, you incur some overhead because EXECUTE IMMEDIATE re-prepares the dynamic string
before every execution.

Some Examples of Dynamic SQL

The following PL/SQL block contains several examples of dynamic SQL:

DECLARE

sql_stmt VARCHAR2(200);

plsql_block VARCHAR2(500);

emp_id NUMBER(4) := 7566;

salary NUMBER(7,2);

dept_id NUMBER(2) := 50;

dept_name VARCHAR2(14) := 'PERSONNEL';

location VARCHAR2(13) := 'DALLAS';

emp_rec emp%ROWTYPE;

BEGIN

EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';

sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';

EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

sql_stmt := 'SELECT * FROM emp WHERE empno = :id';

EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';

EXECUTE IMMEDIATE plsql_block USING 7788, 500;

sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1

RETURNING sal INTO :2';

EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;

EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'

USING dept_id;

EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';

END;

In the example below, a standalone procedure accepts the name of a database table (such as
'emp') and an optional WHERE-clause condition (such as 'sal > 2000'). If you omit the
condition, the procedure deletes all rows from the table. Otherwise, the procedure deletes only
those rows that meet the condition.

CREATE PROCEDURE delete_rows (

table_name IN VARCHAR2,

condition IN VARCHAR2 DEFAULT NULL) AS

where_clause VARCHAR2(100) := ' WHERE ' || condition;

BEGIN

IF condition IS NULL THEN where_clause := NULL; END IF;

EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || where_clause;

EXCEPTION

...

END;

Backward Compatibility of the USING Clause

When a dynamic INSERT, UPDATE, or DELETE statement has a RETURNING clause, output bind
arguments can go in the RETURNING INTO clause or the USING clause. In new applications, use the
RETURNING INTO clause. In old applications, you can continue to use the USING clause. For
example, both of the following EXECUTE IMMEDIATE statements are allowed:

DECLARE

sql_stmt VARCHAR2(200);
my_empno NUMBER(4) := 7902;

my_ename VARCHAR2(10);

my_job VARCHAR2(9);

my_sal NUMBER(7,2) := 3250.00;

BEGIN

sql_stmt := 'UPDATE emp SET sal = :1 WHERE empno = :2

RETURNING ename, job INTO :3, :4';

/* Bind returned values through USING clause. */

EXECUTE IMMEDIATE sql_stmt

USING my_sal, my_empno, OUT my_ename, OUT my_job;

/* Bind returned values through RETURNING INTO clause. */

EXECUTE IMMEDIATE sql_stmt

USING my_sal, my_empno RETURNING INTO my_ename, my_job;

...

END;

Specifying Parameter Modes

With the USING clause, you need not specify a parameter mode for input bind arguments because
the mode defaults to IN. With the RETURNING INTO clause, you cannot specify a parameter mode
for output bind arguments because, by definition, the mode is OUT. An example follows:

DECLARE

sql_stmt VARCHAR2(200);

dept_id NUMBER(2) := 30;

old_loc VARCHAR2(13);

BEGIN

sql_stmt :=
'DELETE FROM dept WHERE deptno = :1 RETURNING loc INTO :2';

EXECUTE IMMEDIATE sql_stmt USING dept_id RETURNING INTO old_loc;

...

END;

When appropriate, you must specify the OUT or IN OUT mode for bind arguments passed as
parameters. For example, suppose you want to call the following standalone procedure:

CREATE PROCEDURE create_dept (

deptno IN OUT NUMBER,

dname IN VARCHAR2,

loc IN VARCHAR2) AS

BEGIN

SELECT deptno_seq.NEXTVAL INTO deptno FROM dual;

INSERT INTO dept VALUES (deptno, dname, loc);

END;

To call the procedure from a dynamic PL/SQL block, you must specify the IN OUT mode for the
bind argument associated with formal parameter deptno, as follows:

DECLARE

plsql_block VARCHAR2(500);

new_deptno NUMBER(2);

new_dname VARCHAR2(14) := 'ADVERTISING';

new_loc VARCHAR2(13) := 'NEW YORK';

BEGIN

plsql_block := 'BEGIN create_dept(:a, :b, :c); END;';

EXECUTE IMMEDIATE plsql_block

USING IN OUT new_deptno, new_dname, new_loc;

IF new_deptno > 90 THEN ...

END;

Using the OPEN-FOR, FETCH, and CLOSE Statements

You use three statements to process a dynamic multi-row query: OPEN-FOR, FETCH, and CLOSE.
First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from the result
set one at a time. When all the rows are processed, you CLOSE the cursor variable. (For more
information about cursor variables, see "Using Cursor Variables".)

Opening the Cursor Variable

The OPEN-FOR statement associates a cursor variable with a multi-row query, executes the query,
identifies the result set, positions the cursor on the first row in the result set, then zeroes the
rows-processed count kept by %ROWCOUNT.

Unlike the static form of OPEN-FOR, the dynamic form has an optional USING clause. At run time,
bind arguments in the USING clause replace corresponding placeholders in the dynamic SELECT
statement. The syntax is

OPEN {cursor_variable | :host_cursor_variable} FOR dynamic_string

[USING bind_argument[, bind_argument]...];

where cursor_variable is a weakly typed cursor variable (one without a return type),
host_cursor_variable is a cursor variable declared in a PL/SQL host environment such as an
OCI program, and dynamic_string is a string expression that represents a multi-row query.

In the following example, you declare a cursor variable, then associate it with a dynamic SELECT
statement that returns rows from the emp table:

DECLARE

TYPE EmpCurTyp IS REF CURSOR; -- define weak REF CURSOR type

emp_cv EmpCurTyp; -- declare cursor variable

my_ename VARCHAR2(15);

my_sal NUMBER := 1000;

BEGIN
OPEN emp_cv FOR -- open cursor variable

'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal;

...

END;

Any bind arguments in the query are evaluated only when the cursor variable is opened. So, to
fetch from the cursor using different bind values, you must reopen the cursor variable with the
bind arguments set to their new values.

Fetching from the Cursor Variable

The FETCH statement returns a row from the result set of a multi-row query, assigns the values of
select-list items to corresponding variables or fields in the INTO clause, increments the count kept
by %ROWCOUNT, and advances the cursor to the next row. The syntax follows:

FETCH {cursor_variable | :host_cursor_variable}

INTO {define_variable[, define_variable]... | record};

Continuing the example, you fetch rows from cursor variable emp_cv into define variables
my_ename and my_sal:

LOOP

FETCH emp_cv INTO my_ename, my_sal; -- fetch next row

EXIT WHEN emp_cv%NOTFOUND; -- exit loop when last row is fetched

-- process row

END LOOP;

For each column value returned by the query associated with the cursor variable, there must be a
corresponding, type-compatible variable or field in the INTO clause. You can use a different INTO
clause on separate fetches with the same cursor variable. Each fetch retrieves another row from
the same result set.
If you try to fetch from a closed or never-opened cursor variable, PL/SQL raises the predefined
exception INVALID_CURSOR.

Closing the Cursor Variable

The CLOSE statement disables a cursor variable. After that, the associated result set is undefined.
The syntax follows:

CLOSE {cursor_variable | :host_cursor_variable};

In this example, when the last row is processed, you close cursor variable emp_cv:

LOOP

FETCH emp_cv INTO my_ename, my_sal;

EXIT WHEN emp_cv%NOTFOUND;

-- process row

END LOOP;

CLOSE emp_cv; -- close cursor variable

If you try to close an already-closed or never-opened cursor variable, PL/SQL raises

INVALID_CURSOR.

Examples of Dynamic SQL for Records, Objects, and Collections

As the following example shows, you can fetch rows from the result set of a dynamic multi-row
query into a record:

DECLARE

TYPE EmpCurTyp IS REF CURSOR;

emp_cv EmpCurTyp;

emp_rec emp%ROWTYPE;

sql_stmt VARCHAR2(200);

my_job VARCHAR2(15) := 'CLERK';

BEGIN

sql_stmt := 'SELECT * FROM emp WHERE job = :j';

OPEN emp_cv FOR sql_stmt USING my_job;

LOOP

FETCH emp_cv INTO emp_rec;

EXIT WHEN emp_cv%NOTFOUND;

-- process record

END LOOP;

CLOSE emp_cv;

END;

The next example illustrates the use of objects and collections. Suppose you define object type
Person and VARRAY type Hobbies, as follows:

CREATE TYPE Person AS OBJECT (name VARCHAR2(25), age NUMBER);

CREATE TYPE Hobbies IS VARRAY(10) OF VARCHAR2(25);

Now, using dynamic SQL, you can write a package of procedures that uses these types, as
follows:

CREATE PACKAGE teams AS

PROCEDURE create_table (tab_name VARCHAR2);

PROCEDURE insert_row (tab_name VARCHAR2, p Person, h Hobbies);

PROCEDURE print_table (tab_name VARCHAR2);

END;

CREATE PACKAGE BODY teams AS

PROCEDURE create_table (tab_name VARCHAR2) IS

BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ' || tab_name ||

' (pers Person, hobbs Hobbies)';

END;

PROCEDURE insert_row (

tab_name VARCHAR2,

p Person,

h Hobbies) IS

BEGIN

EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name ||

' VALUES (:1, :2)' USING p, h;

END;

PROCEDURE print_table (tab_name VARCHAR2) IS

TYPE RefCurTyp IS REF CURSOR;

cv RefCurTyp;

p Person;

h Hobbies;

BEGIN

OPEN cv FOR 'SELECT pers, hobbs FROM ' || tab_name;

LOOP

FETCH cv INTO p, h;

EXIT WHEN cv%NOTFOUND;

-- print attributes of 'p' and elements of 'h'

END LOOP;

CLOSE cv;

END;
END;

From an anonymous PL/SQL block, you might call the procedures in package teams, as follows:

DECLARE

team_name VARCHAR2(15);

...

BEGIN

...

team_name := 'Notables';

teams.create_table(team_name);

teams.insert_row(team_name, Person('John', 31),

Hobbies('skiing', 'coin collecting', 'tennis'));

teams.insert_row(team_name, Person('Mary', 28),

Hobbies('golf', 'quilting', 'rock climbing'));

teams.print_table(team_name);

END;

Using Bulk Dynamic SQL

In this section, you learn how to add the power of bulk binding to dynamic SQL. Bulk binding
improves performance by minimizing the number of context switches between the PL/SQL and
SQL engines. With bulk binding, entire collections, not just individual elements, are passed back
and forth.

Using the following commands, clauses, and cursor attribute, your applications can construct
bulk SQL statements, then execute them dynamically at run time:

BULK FETCH statement

BULK EXECUTE IMMEDIATE statement

FORALL statement
COLLECT INTO clause

RETURNING INTO clause

%BULK_ROWCOUNT cursor attribute

The static versions of these statements, clauses, and cursor attribute are discussed in "Reducing
Loop Overhead for Collections with Bulk Binds". Refer to that section for background
information.

Syntax for Dynamic Bulk Binds

Bulk binding lets Oracle bind a variable in a SQL statement to a collection of values. The
collection type can be any PL/SQL collection type (index-by table, nested table, or varray).
However, the collection elements must have a SQL datatype such as CHAR, DATE, or NUMBER.
Three statements support dynamic bulk binds: EXECUTE IMMEDIATE, FETCH, and FORALL.

Bulk EXECUTE IMMEDIATE

This statement lets you bulk-bind define variables or OUT bind arguments passed as parameters to
a dynamic SQL statement. The syntax follows:

EXECUTE IMMEDIATE dynamic_string

[[BULK COLLECT] INTO define_variable[, define_variable ...]]

[USING bind_argument[, bind_argument ...]]

[{RETURNING | RETURN}

BULK COLLECT INTO bind_argument[, bind_argument ...]];

With a dynamic multi-row query, you can use the BULK COLLECT INTO clause to bind define
variables. The values in each column are stored in a collection.

With a dynamic INSERT, UPDATE, or DELETE statement that returns multiple rows, you can use
the RETURNING BULK COLLECT INTO clause to bulk-bind output variables. The returned rows of
values are stored in a set of collections.
Bulk FETCH

This statement lets you fetch from a dynamic cursor the same way you fetch from a static cursor.
The syntax follows:

FETCH dynamic_cursor

BULK COLLECT INTO define_variable[, define_variable ...];

If the number of define variables in the BULK COLLECT INTO list exceeds the number of columns
in the query select-list, Oracle generates an error.

Bulk FORALL

This statement lets you bulk-bind input variables in a dynamic SQL statement. In addition, you
can use the EXECUTE IMMEDIATE statement inside a FORALL loop. The syntax follows:

FORALL index IN lower bound..upper bound

EXECUTE IMMEDIATE dynamic_string

USING bind_argument | bind_argument(index)

[, bind_argument | bind_argument(index)] ...

[{RETURNING | RETURN} BULK COLLECT

INTO bind_argument[, bind_argument ... ]];

The dynamic string must represent an INSERT, UPDATE, or DELETE statement (not a SELECT
statement).

Examples of Dynamic Bulk Binds

You can bind define variables in a dynamic query using the BULK COLLECT INTO clause. As the
following example shows, you can use that clause in a bulk FETCH or bulk EXECUTE IMMEDIATE
statement:

DECLARE

TYPE EmpCurTyp IS REF CURSOR;

TYPE NumList IS TABLE OF NUMBER;

TYPE NameList IS TABLE OF VARCHAR2(15);

emp_cv EmpCurTyp;

empnos NumList;

enames NameList;

sals NumList;

BEGIN

OPEN emp_cv FOR 'SELECT empno, ename FROM emp';

FETCH emp_cv BULK COLLECT INTO empnos, enames;

CLOSE emp_cv;

EXECUTE IMMEDIATE 'SELECT sal FROM emp'

BULK COLLECT INTO sals;

END;

Only the INSERT, UPDATE, and DELETE statements can have output bind variables. To bulk-bind
them, you use the BULK RETURNING INTO clause, which can appear only in an EXECUTE
IMMEDIATE. An example follows:

DECLARE

TYPE NameList IS TABLE OF VARCHAR2(15);

enames NameList;

bonus_amt NUMBER := 500;

sql_stmt VARCHAR(200);

BEGIN

sql_stmt := 'UPDATE emp SET bonus = :1 RETURNING ename INTO :2';

EXECUTE IMMEDIATE sql_stmt

USING bonus_amt RETURNING BULK COLLECT INTO enames;

END;

To bind the input variables in a SQL statement, you can use the FORALL statement and USING
clause, as shown below. However, the SQL statement cannot be a query.

DECLARE

TYPE NumList IS TABLE OF NUMBER;

TYPE NameList IS TABLE OF VARCHAR2(15);

empnos NumList;

enames NameList;

BEGIN

empnos := NumList(1,2,3,4,5);

FORALL i IN 1..5

EXECUTE IMMEDIATE

'UPDATE emp SET sal = sal * 1.1 WHERE empno = :1

RETURNING ename INTO :2'

USING empnos(i) RETURNING BULK COLLECT INTO enames;

...

END;

Tips and Traps for Dynamic SQL

This section shows you how to take full advantage of dynamic SQL and how to avoid some
common pitfalls.

Improving Performance

In the example below, Oracle opens a different cursor for each distinct value of emp_id. This can
lead to resource contention and poor performance.

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS

BEGIN
EXECUTE IMMEDIATE

'DELETE FROM emp WHERE empno = ' || TO_CHAR(emp_id);

END;

You can improve performance by using a bind variable, as shown below. This allows Oracle to
reuse the same cursor for different values of emp_id.

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS

BEGIN

EXECUTE IMMEDIATE

'DELETE FROM emp WHERE empno = :num' USING emp_id;

END;

Making Procedures Work on Arbitrarily Named Schema Objects

Suppose you need a procedure that accepts the name of any database table, then drops that table
from your schema. Using dynamic SQL, you might write the following standalone procedure:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS

BEGIN

EXECUTE IMMEDIATE 'DROP TABLE :tab' USING table_name;

END;

However, at run time, this procedure fails with an invalid table name error. That is because you
cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.
Instead, you must embed parameters in the dynamic string, then pass the names of schema
objects to those parameters.

To debug the last example, you must revise the EXECUTE IMMEDIATE statement. Instead of using
a placeholder and bind argument, you embed parameter table_name in the dynamic string, as
follows:

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;

END;

Now, you can pass the name of any database table to the dynamic SQL statement.

Using Duplicate Placeholders

Placeholders in a dynamic SQL statement are associated with bind arguments in the USING
clause by position, not by name. So, if the same placeholder appears two or more times in the
SQL statement, each appearance must correspond to a bind argument in the USING clause. For
example, given the dynamic string

sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';

you might code the corresponding USING clause as follows:

EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;

However, only the unique placeholders in a dynamic PL/SQL block are associated with bind
arguments in the USING clause by position. So, if the same placeholder appears two or more
times in a PL/SQL block, all appearances correspond to one bind argument in the USING clause.
In the example below, the first unique placeholder (x) is associated with the first bind argument
(a). Likewise, the second unique placeholder (y) is associated with the second bind argument (b).

DECLARE

a NUMBER := 4;

b NUMBER := 7;

BEGIN

plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;'

EXECUTE IMMEDIATE plsql_block USING a, b;

...

END;
Using Cursor Attributes

Every explicit cursor has four attributes: %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. When
appended to the cursor name, they return useful information about the execution of static and
dynamic SQL statements.

To process SQL data manipulation statements, Oracle opens an implicit cursor named SQL. Its
attributes return information about the most recently executed INSERT, UPDATE, DELETE, or
single-row SELECT statement. For example, the following standalone function uses %ROWCOUNT to
return the number of rows deleted from a database table:

CREATE FUNCTION rows_deleted (

table_name IN VARCHAR2,

condition IN VARCHAR2) RETURN INTEGER AS

BEGIN

EXECUTE IMMEDIATE

'DELETE FROM ' || table_name || ' WHERE ' || condition;

RETURN SQL%ROWCOUNT; -- return number of rows deleted

END;

Likewise, when appended to a cursor variable name, the cursor attributes return information
about the execution of a multi-row query. For more information about cursor attributes, see
"Using Cursor Attributes".

Passing Nulls

Suppose you want to pass nulls to a dynamic SQL statement. For example, you might write the
following EXECUTE IMMEDIATE statement:

EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING NULL;

However, this statement fails with a bad expression error because the literal NULL is not allowed
in the USING clause. To work around this restriction, simply replace the keyword NULL with an
uninitialized variable, as follows:
DECLARE

a_null CHAR(1); -- set to NULL automatically at run time

BEGIN

EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING a_null;

END;

Doing Remote Operations

As the following example shows, PL/SQL subprograms can execute dynamic SQL statements
that refer to objects on a remote database:

PROCEDURE delete_dept (db_link VARCHAR2, dept_id INTEGER) IS

BEGIN

EXECUTE IMMEDIATE 'DELETE FROM dept@' || db_link ||

' WHERE deptno = :num' USING dept_id;

END;

Also, the targets of remote procedure calls (RPCs) can contain dynamic SQL statements. For
example, suppose the following standalone function, which returns the number of rows in a
table, resides on the Chicago database:

CREATE FUNCTION row_count (tab_name VARCHAR2) RETURN INTEGER AS

rows INTEGER;

BEGIN

EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows;

RETURN rows;

END;

From an anonymous block, you might call the function remotely, as follows:

DECLARE

emp_count INTEGER;
BEGIN

emp_count := row_count@chicago('emp');

Using Invoker Rights

By default, a stored procedure executes with the privileges of its definer, not its invoker. Such
procedures are bound to the schema in which they reside. For example, assume that the
following standalone procedure, which can drop any kind of database object, resides in schema
scott:

CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2) AS

BEGIN

EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;

END;

Also assume that user jones has been granted the EXECUTE privilege on this procedure. When
user jones calls drop_it, as follows, the dynamic DROP statement executes with the privileges
of user scott:

SQL> CALL drop_it('TABLE', 'dept');

Also, the unqualified reference to table dept is resolved in schema scott. So, the procedure
drops the table from schema scott, not from schema jones.

However, the AUTHID clause enables a stored procedure to execute with the privileges of its
invoker (current user). Such procedures are not bound to a particular schema. For example, the
following version of drop_it executes with the privileges of its invoker:

CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2)

AUTHID CURRENT_USER AS

BEGIN

EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;

END;
Also, the unqualified reference to the database object is resolved in the schema of the invoker.
For details, see "Invoker Rights Versus Definer Rights".

Using Pragma RESTRICT_REFERENCES

A function called from SQL statements must obey certain rules meant to control side effects.
(See "Controlling Side Effects of PL/SQL Subprograms".) To check for violations of the rules,
you can use the pragma RESTRICT_REFERENCES. The pragma asserts that a function does not
read and/or write database tables and/or package variables. (For more information, See Oracle9i
Application Developer's Guide - Fundamentals.)

However, if the function body contains a dynamic INSERT, UPDATE, or DELETE statement, the
function always violates the rules "write no database state" (WNDS) and "read no database state"
(RNDS). That is because dynamic SQL statements are checked at run time, not at compile time. In
an EXECUTE IMMEDIATE statement, only the INTO clause can be checked at compile time for
violations of RNDS.

Avoiding Deadlocks

In a few situations, executing a SQL data definition statement results in a deadlock. For example,
the procedure below causes a deadlock because it attempts to drop itself. To avoid deadlocks,
never try to ALTER or DROP a subprogram or package while you are still using it.

CREATE PROCEDURE calc_bonus (emp_id NUMBER) AS

BEGIN

...

EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus';