Using Collections Methods

PL/SQL provides the built-in collection methods that make collections easier to use. The following table lists the methods and their purpose:

S.N.	Method Name & Purpose
1	EXISTS(n) Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
2	COUNT Returns the number of elements that a collection currently contains.
3	LIMIT Checks the Maximum Size of a Collection.
4	FIRST Returns the first (smallest) index numbers in a collection that uses integer subscripts.
5	LAST Returns the last (largest) index numbers in a collection that uses integer subscripts.
6	PRIOR(n) Returns the index number that precedes index n in a collection.
7	NEXT(n) Returns the index number that succeeds index n.
8	EXTEND Appends one null element to a collection.
9	EXTEND(n) Appends n null elements to a collection.
10	EXTEND(n,i) Appends n copies of the ith element to a collection.
11	TRIM Removes one element from the end of a collection.
12	TRIM(n) Removes n elements from the end of a collection.
13	DELETE Removes all elements from a collection, setting COUNT to 0.
14	DELETE(n) Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
15	DELETE(m,n) Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

A collection method is a built-in function or procedure that operates on collections and is called using dot notation.

Collection methods cannot be called from SQL statements.

EXTEND and TRIM cannot be used with associative arrays.

EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT are functions; EXTEND,

TRIM, and DELETE are procedures.

EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE take parameters corresponding to

collection subscripts, which are usually integers but can also be strings for associative arrays.

Only EXISTS can be applied to atomically null collections. If you apply another method to such collections, PL/SQL raises COLLECTION_IS_NULL.

Collection Exceptions

The following table provides the collection exceptions and when they are raised:

Collection Exception	Raised in Situations
COLLECTION_IS_NULL	You try to operate on an atomically null collection.
NO_DATA_FOUND	A subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT	A subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT	A subscript is outside the allowed range.
VALUE_ERROR	A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.

Checking if collection Element Exists ( EXISTS Method)

EXISTS(n) returns TRUE if the nth element in a collection exists. Otherwise, EXISTS(n) returns FALSE. By combining EXISTS with DELETE, you can work with sparse nested tables. You can also use EXISTS to avoid referencing a nonexistent element, which raises an exception. When passed an out-of-range subscript, EXISTS returns FALSE instead of raising SUBSCRIPT_OUTSIDE_LIMIT Exception.

DECLARE

TYPE NumList IS TABLE OF INTEGER;

n NumList := NumList(1,3,5,7);

BEGIN

n.DELETE(2); -- Delete the second element

IF n.EXISTS(1) THEN

dbms_output.put_line('OK, element #1 exists.');

END IF;

IF n.EXISTS(2) = FALSE THEN

dbms_output.put_line('OK, element #2 has been deleted.');

END IF;

IF n.EXISTS(99) = FALSE THEN

dbms_output.put_line('OK, element #99 does not exist at all.');

END IF;

END;

/

Counting the elements in a collection ( COUNT method)

COUNT returns the number of elements that a collection currently contains:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(2,4,6,8); -- Collection starts with 4 elements.
BEGIN
dbms_output.put_line('There are ' || n.COUNT || ' elements in N.');
n.EXTEND(3); -- Add 3 new elements at the end.
dbms_output.put_line('Now there are ' || n.COUNT || ' elements in N.');
n := NumList(86,99); -- Assign a completely new value with 2 elements.
dbms_output.put_line('Now there are ' || n.COUNT || ' elements in N.');
n.TRIM(2); -- Remove the last 2 elements, leaving none.
dbms_output.put_line('Now there are ' || n.COUNT || ' elements in N.');
END;
/

 COUNT is useful because the current size of a collection is not always known. For example, you can fetch a column of Oracle data into a nested table, where the number of elements depends on the size of the result set.

 For varrays, COUNT always equals LAST. You can increase or decrease the size of a varray using the EXTEND and TRIM methods, so the value of COUNT can change, up to the value of the LIMIT method.

 For nested tables, COUNT normally equals LAST. But, if you delete elements from the middle of a nested table, COUNT becomes smaller than LAST. When tallying elements, COUNT ignores deleted elements.

Checking the maximum size of  a Collection ( LIMIT method)

For nested tables and associative arrays, which have no maximum size, LIMIT returns NULL. For varrays, LIMIT returns the maximum number of elements that a varray can contain. You specify this limit in the type definition, and can change it later with the TRIM and EXTEND methods. For instance, if the maximum size of varray PROJECTS is 25 elements, the following IF condition is true:

DECLARE
TYPE Colors IS VARRAY(7) OF VARCHAR2(64);
c Colors := Colors('Gold','Silver');
BEGIN
dbms_output.put_line('C has ' || c.COUNT || ' elements now.');
dbms_output.put_line('C''s type can hold a maximum of ' || c.LIMIT || '
elements.');
dbms_output.put_line('The maximum number you can use with C.EXTEND() is ' ||
(c.LIMIT - c.COUNT));
END;
/

Finding the First or Last collection Element ( FIRST and LAST Methods)

FIRST and LAST return the first and last (smallest and largest) index numbers in a collection that uses integer subscripts.

For an associative array with VARCHAR2 key values, the lowest and highest key values are returned. By default, the order is based on the binary values of the characters in the string. If the NLS_COMP initialization parameter is set to ANSI, the order is based on the locale-specific sort order specified by the NLS_SORT initialization parameter. 

If the collection is empty, FIRST and LAST return NULL.
If the collection contains only one element, FIRST and LAST return the same index value.

The following example shows how to use FIRST and LAST to iterate through the elements in a collection that has consecutive subscripts:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,3,5,7);
counter INTEGER;
BEGIN
dbms_output.put_line('N''s first subscript is ' || n.FIRST);
dbms_output.put_line('N''s last subscript is ' || n.LAST);
-- When the subscripts are consecutive starting at 1, it's simple to loop through
them.
FOR i IN n.FIRST .. n.LAST
LOOP
dbms_output.put_line('Element #' || i || ' = ' || n(i));
END LOOP;
n.DELETE(2); -- Delete second element.
-- When the subscripts have gaps or the collection might be uninitialized,
-- the loop logic is more extensive. We start at the first element, and
-- keep looking for the next element until there are no more.
IF n IS NOT NULL THEN
counter := n.FIRST;
WHILE counter IS NOT NULL
LOOP
dbms_output.put_line('Element #' || counter || ' = ' || n(counter));
counter := n.NEXT(counter);
END LOOP;
ELSE
dbms_output.put_line('N is null, nothing to do.');
END IF;
END;
/

For varrays, FIRST always returns 1 and LAST always equals COUNT. 
For nested tables, normally FIRST returns 1 and LAST equals COUNT. But if you delete elements from the beginning of a nested table, FIRST returns a number larger than 1.
If you delete elements from the middle of a nested table, LAST becomes larger than COUNT.
When scanning elements, FIRST and LAST ignore deleted elements.

Looping Through Collection Elements (PRIOR and NEXT Methods)

PRIOR(n) returns the index number that precedes index n in a collection. NEXT(n) returns the index number that succeeds index n. If n has no predecessor, PRIOR(n) returns NULL. If n has no successor, NEXT(n) returns NULL.

For associative arrays with VARCHAR2 keys, these methods return the appropriate key value; ordering is based on the binary values of the characters in the string, unless the NLS_COMP initialization parameter is set to ANSI, in which case the ordering is based on the locale-specific sort order specified by the NLS_SORT initialization parameter.

These methods are more reliable than looping through a fixed set of subscript values, because elements might be inserted or deleted from the collection during the loop.
This is especially true for associative arrays, where the subscripts might not be in consecutive order and so the sequence of subscripts might be (1,2,4,8,16) or ('A','E','I','O','U').

DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1966,1971,1984,1989,1999);
BEGIN
dbms_output.put_line('The element after #2 is #' || n.NEXT(2));
dbms_output.put_line('The element before #2 is #' || n.PRIOR(2));
n.DELETE(3); -- Delete an element to show how NEXT can handle gaps.
dbms_output.put_line('Now the element after #2 is #' || n.NEXT(2));
IF n.PRIOR(n.FIRST) IS NULL THEN
dbms_output.put_line('Can''t get PRIOR of the first element or NEXT of the
last.');
END IF;
END;

/

o/p:

The element after #2 is #3
The element before #2 is #1
Now the element after #2 is #4
Can't get PRIOR of the first element or NEXT of the last.


PL/SQL procedure successfully completed.

You can use PRIOR or NEXT to traverse collections indexed by any series of subscripts. The following example uses NEXT to traverse a nested table from which some elements have been deleted:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
n NumList := NumList(1,3,5,7);
counter INTEGER;
BEGIN
n.DELETE(2); -- Delete second element.
-- When the subscripts have gaps, the loop logic is more extensive. We start at
the
-- first element, and keep looking for the next element until there are no more.

counter := n.FIRST;

WHILE counter IS NOT NULL

LOOP

dbms_output.put_line('Counting up: Element #' || counter || ' = ' ||

n(counter));

counter := n.NEXT(counter);

END LOOP;

-- Run the same loop in reverse order.

counter := n.LAST;

WHILE counter IS NOT NULL

LOOP

dbms_output.put_line('Counting down: Element #' || counter || ' = ' ||

n(counter));

counter := n.PRIOR(counter);

END LOOP;

END;

/

When traversing elements, PRIOR and NEXT skip over deleted elements.

Increasing the Size of a Collection (EXTEND Method)

To increase the size of a nested table or varray, use EXTEND.

You cannot use EXTEND with index-by tables.

This procedure has three forms:

■ EXTEND appends one null element to a collection.

■ EXTEND(n) appends n null elements to a collection.

■ EXTEND(n,i) appends n copies of the ith element to a collection.

You cannot use EXTEND to add elements to an uninitialized.

If you impose the NOT NULL constraint on a TABLE or VARRAY type, you cannot apply the first two forms of EXTEND to collections of that type.

EXTEND operates on the internal size of a collection, which includes any deleted elements. If EXTEND encounters deleted elements, it includes them in its tally. PL/SQL keeps placeholders for deleted elements so that you can re-create them by assigning new values.

DECLARE

TYPE NumList IS TABLE OF INTEGER;

n NumList := NumList(2,4,6,8);

x NumList := NumList(1,3);

PROCEDURE print_numlist(the_list NumList) IS

output VARCHAR2(128);

BEGIN

FOR i IN the_list.FIRST .. the_list.LAST

LOOP

output := output || NVL(TO_CHAR(the_list(i)),'NULL') || ' ';

END LOOP;

dbms_output.put_line(output);

END;

BEGIN

dbms_output.put_line('At first, N has ' || n.COUNT || ' elements.');

n.EXTEND(5); -- Add 5 elements at the end.

dbms_output.put_line('Now N has ' || n.COUNT || ' elements.');

-- Elements 5, 6, 7, 8, and 9 are all NULL.

print_numlist(n);

dbms_output.put_line('At first, X has ' || x.COUNT || ' elements.');

x.EXTEND(4,2); -- Add 4 elements at the end.

dbms_output.put_line('Now X has ' || x.COUNT || ' elements.');

-- Elements 3, 4, 5, and 6 are copies of element #2.

print_numlist(x);

END;

/

When it includes deleted elements, the internal size of a nested table differs from the values returned by COUNT and LAST. For instance, if you initialize a nested table with five elements, then delete elements 2 and 5, the internal size is 5, COUNT returns 3, and LAST returns 4. All deleted elements, regardless of position, are treated alike.

Decreasing the Size of a Collection (TRIM Method)

This procedure has two forms:

■ TRIM removes one element from the end of a collection.

■ TRIM(n) removes n elements from the end of a collection.

DECLARE

TYPE NumList IS TABLE OF NUMBER;

n NumList := NumList(1,2,3,5,7,11);

PROCEDURE print_numlist(the_list NumList) IS

output VARCHAR2(128);

BEGIN

IF n.COUNT = 0 THEN

dbms_output.put_line('No elements in collection.');

ELSE

FOR i IN the_list.FIRST .. the_list.LAST

LOOP

output := output || NVL(TO_CHAR(the_list(i)),'NULL') || ' ';

END LOOP;

dbms_output.put_line(output);

END IF;

END;

BEGIN

print_numlist(n);

n.TRIM(2); -- Remove last 2 elements.

print_numlist(n);

n.TRIM; -- Remove last element.

print_numlist(n);

n.TRIM(n.COUNT); -- Remove all remaining elements.

print_numlist(n);

-- If too many elements are specified, TRIM raises the exception SUBSCRIPT_BEYOND_

COUNT.

BEGIN

n := NumList(1,2,3);

n.TRIM(100);

EXCEPTION

WHEN SUBSCRIPT_BEYOND_COUNT THEN

dbms_output.put_line('I guess there weren''t 100 elements that could be trimmed.');

END;

-- When elements are removed by DELETE, placeholders are left behind. TRIM counts

these

-- placeholders as it removes elements from the end.

n := NumList(1,2,3,4);

n.DELETE(3); -- delete element 3

-- At this point, n contains elements (1,2,4).

-- TRIMming the last 2 elements removes the 4 and the placeholder, not 4 and 2.

n.TRIM(2);

print_numlist(n);

END;

/

END;

/

If n is too large, TRIM(n) raises SUBSCRIPT_BEYOND_COUNT.

TRIM operates on the internal size of a collection. If TRIM encounters deleted elements, it includes them in its tally. Consider the following example:

DECLARE

TYPE CourseList IS TABLE OF VARCHAR2(10);

courses CourseList;

BEGIN

courses := CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');

courses.DELETE(courses.LAST); -- delete element 3

/* At this point, COUNT equals 2, the number of valid

elements remaining. So, you might expect the next

statement to empty the nested table by trimming

elements 1 and 2. Instead, it trims valid element 2

and deleted element 3 because TRIM includes deleted

elements in its tally. */

courses.TRIM(courses.COUNT);

dbms_output.put_line(courses(1)); -- prints 'Biol 4412'

END;

/

In general, do not depend on the interaction between TRIM and DELETE. It is better to treat nested tables like fixed-size arrays and use only DELETE, or to treat them like stacks and use only TRIM and EXTEND.

Because PL/SQL does not keep placeholders for trimmed elements, you cannot replace a trimmed element simply by assigning it a new value.

Deleting Collection Elements (DELETE Method)

This procedure has various forms:

■ DELETE removes all elements from a collection.

■ DELETE(n) removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.

■ DELETE(m,n) removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

For example:

DECLARE

TYPE NumList IS TABLE OF NUMBER;

n NumList := NumList(10,20,30,40,50,60,70,80,90,100);

TYPE NickList IS TABLE OF VARCHAR2(64) INDEX BY VARCHAR2(32);

nicknames NickList;

BEGIN

n.DELETE(2); -- deletes element 2

n.DELETE(3,6); -- deletes elements 3 through 6

n.DELETE(7,7); -- deletes element 7

n.DELETE(6,3); -- does nothing since 6 > 3

n.DELETE; -- deletes all elements

nicknames('Bob') := 'Robert';

nicknames('Buffy') := 'Esmerelda';

nicknames('Chip') := 'Charles';

nicknames('Dan') := 'Daniel';

nicknames('Fluffy') := 'Ernestina';

nicknames('Rob') := 'Robert';

nicknames.DELETE('Chip'); -- deletes element denoted by this key

nicknames.DELETE('Buffy','Fluffy'); -- deletes elements with keys in this

alphabetic range

END;

/

Varrays always have consecutive subscripts, so you cannot delete individual elements except from the end (by using the TRIM method).

If an element to be deleted does not exist, DELETE simply skips it; no exception is raised. PL/SQL keeps placeholders for deleted elements, so you can replace a deleted element by assigning it a new value.

DELETE lets you maintain sparse nested tables. You can store sparse nested tables in the database, just like any other nested tables.

The amount of memory allocated to a nested table can increase or decrease dynamically. As you delete elements, memory is freed page by page. If you delete the entire table, all the memory is freed.

PL/SQL FOR Loop tips

The FOR loop  executes for a specified number of times, defined in the loop definition.  Because the number of loops is specified, the overhead of checking a condition to exit is eliminated.  The number of executions is defined in the loop definition as a range from a start value to an end value (inclusive).  The integer index in the FOR loop starts at the start value and increments by one (1) for each loop until it reaches the end value. 

SQL> begin
  2    for idx in 2..5 loop
  3      dbms_output.put_line (idx);
  4    end loop;
  5  end;
  6  /
2
3
4
5 

PL/SQL procedure successfully completed.

In the example below a variable idx is defined, assigning it the value 100.  When the FOR loop executes, the variable idx is also defined as the index for the FOR loop.  The original variable idx goes out of scope when the FOR loop defines its index variable.  Inside the FOR loop, the idx variable is the loop index.  Once the FOR loop terminates, the loop index goes out of scope and the original idx variable is again in scope.

SQL> declare

  2    idx number := 100;
  3  begin
  4    dbms_output.put_line (idx);
  5    for idx in 2..5 loop
  6      dbms_output.put_line (idx);
  7    end loop;
  8    dbms_output.put_line (idx);
  9  end;
 10  /
100
2
3
4
5
100 

PL/SQL procedure successfully completed.

You can use the loop index inside the loop, but you can not change it.  If you want to loop by an increment other than one, you will have to do so programmatically as the FOR loop will only increment the index by one. 

SQL> begin

  2    for i in 4 .. 200 loop
  3      i := i + 4;
  4    end loop;
  5  end;
  6  /
    i := i + 4;
    *
ERROR at line 3:ORA-06550: line 3, column 5:
PLS-00363: expression 'I' cannot be used as an assignment target
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored

The loop index start and stop values can be expressions or variables.  They are evaluated once at the start of the loop to determine the number of loop iterations.  If their values change during the loop processing, it does not impact the number of iterations.

SQL> declare

  2    n_start number := 3;
  3    n_stop  number := 6;
  4  begin
  5    for xyz in n_start .. n_stop loop
  6      n_stop := 100;
  7      dbms_output.put_line (xyz);
  8    end loop;
  9  end;
 10  /
3
4
5
6

PL/SQL procedure successfully completed.

Line 6 changes the stop value, setting it to 100.  But the loop still terminates at the value of 6.  The loop index start and stop values are always defined from lowest to highest.  If you want the index to count down use the REVERSE key word.

SQL> begin

  2    for num in 4 .. 7 loop
  3      dbms_output.put_line (num);
  4    end loop;
  5
  6    for num in reverse 4 .. 7 loop
  7      dbms_output.put_line (num);
  8    end loop;
  9
 10    for num in 7 .. 4 loop
 11      dbms_output.put_line (num);
 12    end loop;
 13  end;
 14  /
4
5
6
7
7
6
5
4 

PL/SQL procedure successfully completed.

Notice that the third FOR loop COMPILED BUT DID NOT EXECUTE!  The FOR loop calculated the number of loop iterations and got a negative number, therefore the loop count was zero.

In the next example a FOR loop is used to calculate the factorial of a number.  A factorial value is commonly used to determine all possible values for a number and is defined as 

x*(x-1)*(x-2)….(0) = !x.

!8 = 8*7*6*5*4*3*2*1 = 40320

SQL> declare

  2    v_seed number := &numb;
  3    v_hold number := 1;
  4  begin
  5    for i in reverse 1 .. v_seed loop
  6      v_hold := v_hold * i;
  7    end loop;
  8    dbms_output.put_line ('!'||v_seed||' = '||v_hold);
  9  end;
 10  / 

Enter value for numb: 8
!8 = 40320 

SQL> /
Enter value for numb: 4
!4 = 24

Source: http://www.dba-oracle.com/concepts/pl_sql_for_loop.htm

Dynamic SQL Concepts

Dynamic SQL is a programming methodology for generating and running SQL statements at run time. It is useful when writing general-purpose and flexible programs like ad hoc query systems, when writing programs that must run database definition language (DDL) statements, or when you do not know at compilation time the full text of a SQL statement or the number or data types of its input and output variables. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.

PL/SQL provides two ways to write dynamic SQL:

• Native dynamic SQL, a PL/SQL language (that is, native) feature for building and running dynamic SQL statements
• DBMS_SQL package, an API for building, running, and describing dynamic SQL statements

Native dynamic SQL code is easier to read and write than equivalent code that uses the DBMS_SQL package, and runs noticeably faster (especially when it can be optimized by the compiler). However, to write native dynamic SQL code, you must know at compile time the number and data types of the input and output variables of the dynamic SQL statement. If you do not know this information at compile time, you must use the DBMS_SQL package.

When You Need Dynamic SQL

In PL/SQL, you need dynamic SQL to run:

• SQL whose text is unknown at compile time. For example, a SELECT statement that includes an identifier that is unknown at compile time (such as a table name) or a WHERE clause in which the number of subclauses is unknown at compile time.
• SQL that is not supported as static SQL. That is, any SQL construct not included in "Description of Static SQL".

If you do not need dynamic SQL, use static SQL, which has these advantages:

• Successful compilation verifies that static SQL statements reference valid database objects and that the necessary privileges are in place to access those objects.
• Successful compilation creates schema object dependencies.

Native Dynamic SQL

Native dynamic SQL processes most dynamic SQL statements with the EXECUTE IMMEDIATE statement.

If the dynamic SQL statement is a SELECT statement that returns multiple rows, native dynamic SQL gives you these choices:

• Use the EXECUTE IMMEDIATE statement with the BULK COLLECT INTO clause.
• Use the OPEN FOR, FETCH, and CLOSE statements.

The SQL cursor attributes work the same way after native dynamic SQL INSERT, UPDATE, DELETE, MERGE, and single-row SELECT statements as they do for their static SQL counterparts. 

1. EXECUTE IMMEDIATE Statement

The EXECUTE IMMEDIATE statement is the means by which native dynamic SQL processes most dynamic SQL statements.

If the dynamic SQL statement is self-contained (that is, if it has no placeholders for bind variables and the only result that it can possibly return is an error), then the EXECUTE IMMEDIATE statement needs no clauses.

If the dynamic SQL statement includes placeholders for bind variables, each placeholder must have a corresponding bind variable in the appropriate clause of the EXECUTE IMMEDIATE statement, as follows:

• If the dynamic SQL statement is a SELECT statement that can return at most one row, put out-bind variables (defines) in the INTO clause and in-bind variables in the USING clause.
• If the dynamic SQL statement is a SELECT statement that can return multiple rows, put out-bind variables (defines) in the BULK COLLECT INTO clause and in-bind variables in the USING clause.
• If the dynamic SQL statement is a DML statement without a RETURNING INTO clause, other than SELECT, put all bind variables in the USING clause.
• If the dynamic SQL statement is a DML statement with a RETURNING INTO clause, put in-bind variables in the USING clause and out-bind variables in the RETURNING INTO clause.
• If the dynamic SQL statement is an anonymous PL/SQL block or a CALL statement, put all bind variables in the USING clause.

If the dynamic SQL statement invokes a subprogram, ensure that:

• Every bind variable that corresponds to a placeholder for a subprogram parameter has the same parameter mode as that subprogram parameter (as in Example 1) and a data type that is compatible with that of the subprogram parameter. 
• No bind variable has a data type that SQL does not support (such as BOOLEAN in Example 2).

The USING clause cannot contain the literal NULL. To work around this restriction, use an uninitialized variable where you want to use NULL, as in Example 3.

Example 1 Invoking Subprogram from Dynamic PL/SQL Block

-- Subprogram that dynamic PL/SQL block invokes:

CREATE OR REPLACE PROCEDURE create_dept (

  deptid IN OUT NUMBER,

  dname  IN     VARCHAR2,

  mgrid  IN     NUMBER,

  locid  IN     NUMBER

) AS

BEGIN

  deptid := departments_seq.NEXTVAL;

  INSERT INTO departments (

    department_id,

    department_name,

    manager_id,

    location_id

  )

  VALUES (deptid, dname, mgrid, locid);

END;

/

DECLARE

  plsql_block VARCHAR2(500);

  new_deptid  NUMBER(4);

  new_dname   VARCHAR2(30) := 'Advertising';

  new_mgrid   NUMBER(6)    := 200;

  new_locid   NUMBER(4)    := 1700;

BEGIN

 -- Dynamic PL/SQL block invokes subprogram:

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

 /* Specify bind variables in USING clause.

    Specify mode for first parameter.

    Modes of other parameters are correct by default. */

  EXECUTE IMMEDIATE plsql_block

    USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;

END;

/

• Example 2: Unsupported Data Type in Native Dynamic SQL. BOOLEAN variable are not supported in EXECUTE IMMEDIATE. Below operation will give error. 

SQL> CREATE OR REPLACE FUNCTION f (x INTEGER)
  2      RETURN BOOLEAN
  3    AS
  4    BEGIN
  5      RETURN TRUE;
  6    END f;
  7  /

Function created.

SQL> DECLARE
  2    dyn_stmt VARCHAR2(200);
  3    b1       BOOLEAN;
  4  BEGIN
  5      dyn_stmt := 'BEGIN :b := f(5); END;';
  6      EXECUTE IMMEDIATE dyn_stmt USING OUT b1;
  7  --    DBMS_OUTPUT.PUT_LINE(b1);
  8  END;
  9  /
    EXECUTE IMMEDIATE dyn_stmt USING OUT b1;
                                         *
ERROR at line 6:
ORA-06550: line 6, column 42:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 6, column 5:

PL/SQL: Statement ignored

• Example 3: Uninitialized Variable Represents NULL in USING Clause

SQL> select * from emp3 where empno=7521;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO HI
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
 
DECLARE
  a_null  CHAR(1);  -- Set to NULL automatically at run time
BEGIN
  EXECUTE IMMEDIATE 'UPDATE emp3 SET comm = :x where empno= 7521' USING a_null;
END;
/

SQL> select * from emp3 where empno=7521;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO HI
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7521 WARD       SALESMAN        7698 22-FEB-81       1250                    30

OPEN FOR, FETCH, and CLOSE Statements

If the dynamic SQL statement represents a SELECT statement that returns multiple rows, you can process it with native dynamic SQL as follows:

1. Use an OPEN FOR statement to associate a cursor variable with the dynamic SQL statement. In the USING clause of the OPEN FOR statement, specify a bind variable for each placeholder in the dynamic SQL statement.
   The USING clause cannot contain the literal NULL. To work around this restriction, use an uninitialized variable where you want to use NULL, as in Example 7-3.
2. Use the FETCH statement to retrieve result set rows one at a time, several at a time, or all at once.
3. Use the CLOSE statement to close the cursor variable.

Example 4 lists all employees who are managers, retrieving result set rows one at a time.

DECLARE
  TYPE EmpCurTyp  IS REF CURSOR;
  v_emp_cursor    EmpCurTyp;
  emp_record      employees%ROWTYPE;
  v_stmt_str      VARCHAR2(200);
  v_e_job         employees.job%TYPE;
BEGIN
  -- Dynamic SQL statement with placeholder:
  v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j';

  -- Open cursor & specify bind variable in USING clause:
  OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';

  -- Fetch rows from result set one at a time:
  LOOP
    FETCH v_emp_cursor INTO emp_record;
    EXIT WHEN v_emp_cursor%NOTFOUND;
  END LOOP;

  -- Close cursor:
  CLOSE v_emp_cursor;
END;
/

Repeated Placeholder Names in Dynamic SQL Statements

If you repeat placeholder names in dynamic SQL statements, be aware that the way placeholders are associated with bind variables depends on the kind of dynamic SQL statement.

Dynamic SQL Statement is Not Anonymous Block or CALL Statement

If the dynamic SQL statement does not represent an anonymous PL/SQL block or a CALL statement, repetition of placeholder names is insignificant. Placeholders are associated with bind variables in the USING clause by position, not by name.

For example, in this dynamic SQL statement, the repetition of the name :x is insignificant:
sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';

In the corresponding USING clause, you must supply four bind variables. They can be different; for example:
EXECUTE IMMEDIATE sql_stmt USING a, b, c, d;

The preceding EXECUTE IMMEDIATE statement runs this SQL statement:
INSERT INTO payroll VALUES (a, b, c, d)

To associate the same bind variable with each occurrence of :x, you must repeat that bind variable; for example:
EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;

The preceding EXECUTE IMMEDIATE statement runs this SQL statement:
INSERT INTO payroll VALUES (a, a, b, a)

Dynamic SQL Statement is Anonymous Block or CALL Statement

If the dynamic SQL statement represents an anonymous PL/SQL block or a CALL statement, repetition of placeholder names is significant. Each unique placeholder name must have a corresponding bind variable in the USING clause. If you repeat a placeholder name, you need not repeat its corresponding bind variable. All references to that placeholder name correspond to one bind variable in the USING clause.

In Example 5, all references to the first unique placeholder name, :x, are associated with the first bind variable in the USING clause, a, and the second unique placeholder name, :y, is associated with the second bind variable in the USING clause, b.

Example 5 Repeated Placeholder Names in Dynamic PL/SQL Block

CREATE PROCEDURE calc_stats (

  w NUMBER,

  x NUMBER,

  y NUMBER,

  z NUMBER )

IS

BEGIN

  DBMS_OUTPUT.PUT_LINE(w + x + y + z);

END;

/

DECLARE

  a NUMBER := 4;

  b NUMBER := 7;

  plsql_block VARCHAR2(100);

BEGIN

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

  EXECUTE IMMEDIATE plsql_block USING a, b;  -- calc_stats(a, a, b, a)

END;

/

DBMS_SQL Package

The DBMS_SQL package defines an entity called a SQL cursor number. Because the SQL cursor number is a PL/SQL integer, you can pass it across call boundaries and store it.

You must use the DBMS_SQL package to run a dynamic SQL statement when you do not know either of these until run time:

• SELECT list
• What placeholders in a SELECT or DML statement must be bound

In these situations, you must use native dynamic SQL instead of the DBMS_SQL package:

• The dynamic SQL statement retrieves rows into records.
• You want to use the SQL cursor attribute %FOUND, %ISOPEN, %NOTFOUND, or %ROWCOUNT after issuing a dynamic SQL statement that is an INSERT, UPDATE, DELETE, MERGE, or single-row SELECT statement.

When you need both the DBMS_SQL package and native dynamic SQL, you can switch between them, using:

• DBMS_SQL.TO_REFCURSOR Function
• DBMS_SQL.TO_CURSOR_NUMBER Function

DBMS_SQL.TO_REFCURSOR Function

The DBMS_SQL.TO_REFCURSOR function converts a SQL cursor number to a weak cursor variable, which you can use in native dynamic SQL statements.

Before passing a SQL cursor number to the DBMS_SQL.TO_REFCURSOR function, you must OPEN, PARSE, and EXECUTE it (otherwise an error occurs).

After you convert a SQL cursor number to a REF CURSOR variable, DBMS_SQL operations can access it only as the REF CURSOR variable, not as the SQL cursor number. For example, using the DBMS_SQL.IS_OPEN function to see if a converted SQL cursor number is still open causes an error.

Example 6 uses the DBMS_SQL.TO_REFCURSOR function to switch from the DBMS_SQL package to native dynamic SQL.

Example 6 Switching from DBMS_SQL Package to Native Dynamic SQL

CREATE OR REPLACE TYPE vc_array IS TABLE OF VARCHAR2(200);

/

CREATE OR REPLACE TYPE numlist IS TABLE OF NUMBER;

/

CREATE OR REPLACE PROCEDURE do_query_1 (

  placeholder vc_array,

  bindvars vc_array,

  sql_stmt VARCHAR2

)

IS

  TYPE curtype IS REF CURSOR;

  src_cur     curtype;

  curid       NUMBER;

  bindnames   vc_array;

  empnos      numlist;

  depts       numlist;

  ret         NUMBER;

  isopen      BOOLEAN;

BEGIN

  -- Open SQL cursor number:

  curid := DBMS_SQL.OPEN_CURSOR;

  -- Parse SQL cursor number:

  DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);

  bindnames := placeholder;

  -- Bind variables:

  FOR i IN 1 .. bindnames.COUNT LOOP

    DBMS_SQL.BIND_VARIABLE(curid, bindnames(i), bindvars(i));

  END LOOP;

  -- Run SQL cursor number:

  ret := DBMS_SQL.EXECUTE(curid);

  -- Switch from DBMS_SQL to native dynamic SQL:

  src_cur := DBMS_SQL.TO_REFCURSOR(curid);

  FETCH src_cur BULK COLLECT INTO empnos, depts;

  -- This would cause an error because curid was converted to a REF CURSOR:

  -- isopen := DBMS_SQL.IS_OPEN(curid);

  CLOSE src_cur;

END;

/

DBMS_SQL.TO_CURSOR_NUMBER Function

The DBMS_SQL.TO_CURSOR_NUMBER function converts a REF CURSOR variable (either strong or weak) to a SQL cursor number, which you can pass to DBMS_SQL subprograms.

Before passing a REF CURSOR variable to the DBMS_SQL.TO_CURSOR_NUMBER function, you must OPEN it.

After you convert a REF CURSOR variable to a SQL cursor number, native dynamic SQL operations cannot access it.

Example 7 uses the DBMS_SQL.TO_CURSOR_NUMBER function to switch from native dynamic SQL to the DBMS_SQL package.

Example 7 Switching from Native Dynamic SQL to DBMS_SQL Package

CREATE OR REPLACE PROCEDURE do_query_2 (

  sql_stmt VARCHAR2

)

IS

  TYPE curtype IS REF CURSOR;

  src_cur   curtype;

  curid     NUMBER;

  desctab   DBMS_SQL.DESC_TAB;

  colcnt    NUMBER;

  namevar   VARCHAR2(50);

  numvar    NUMBER;

  datevar   DATE;

  empno     NUMBER := 100;

BEGIN

  -- sql_stmt := SELECT ... FROM employees WHERE employee_id = :b1';

  -- Open REF CURSOR variable:

  OPEN src_cur FOR sql_stmt USING empno;

  -- Switch from native dynamic SQL to DBMS_SQL package:

  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);

      -- statements

    ELSE

      DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 50);

    END IF;

  END LOOP;

  -- Fetch rows with DBMS_SQL package:

  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);

        -- statements

      END IF;

    END LOOP;

  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(curid);

END;

/

Source: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm

Difference Between DBMS_SQL and EXECUTE IMMEDIATE