BULK COLLECT in Oracle

The BULK COLLECT clause tell to SQL engine to bilk bind output collect before returning them to PL/SQL engine, we can use BULK COLLECT in SELECT INTO ,FETCH INTO ,RETURNING INTO clause.


BULK COLLECT in SELECT INTO Clause:

BULK COLLECT in FETCH INTO Clause:

BULK COLLECT in RETURNING Clause:

BULK COLLECT With LIMIT:
Collection is stored in the PGA memory, not the SGA memory. SGA memory is sared by all sessions connected by Oracle Database but PGA memory is allocated for each session. Thus if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, i assition to the memory allocated to the SGA. 

To avoid this memory problem Oracle use the LIMIT clause. 
Example:

DECLARE

CURSOR emp_cur  IS 
 SELECT * FROM EMP;

TYPE emp_aat IS TABLE OF emp_cur%ROWTYPE
INDEX BY PLS_INTEGER;
l_emp emp_aat;
V_LIMIT_VAL NUMBER :=5;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur BULK COLLECT INTO l_emp LIMIT V_LIMIT_VAL;
FOR IDX IN 1..l_emp.COUNT
LOOP
 --write your logic
 null;
END LOOP;
END LOOP;
CLOSE emp_cur;

END;
/

Bulk Bind in Oracle

Bulk Bind or we can say FORALL in Oracle.
The assigning of values to PL/SQL variables in SQL statements is called binding.The binding of an entire collection at once is called bulk binding.

Oracle use two engine to process the PL/SQL code.The PL/SQL engine accepts any valid PL/SQL block or subprogram and SQL engine executes the SQL statements.

Check the below figure how context switch between the PL/SQL and SQL engines.

• PL/SQL engine executes procedural statements and sends SQL statements to the SQL engine.
• SQL engine executes the SQL statements and returns data to the PL/SQL engine(switch between the PL/SQL and SQL engines ).
• If context switch between the PL/SQL and SQL engines is many then it is performance issue.
• That can happen when SQL statements execute inside a loop using collection (index-by table, nested table, varray, or host array) elements as bind variables.

 Check the below example:

DECLARE
TYPE empno_type is table of emp.empno%type
index by pls_integer;
v_emp_no  empno_type;
BEGIN
select empno bulk collect into v_emp_no 
from emp where deptno in (10,20);
for i in v_emp_no.first..v_emp_no.last
loop
update emp set sal=sal+1000 where empno=v_emp_no(i).empno;
commit;
end loop;
END;

in this program UPDATE statement is sent to the SQL engine with each iteration of the FOR loop.

[NB:In such cases, if the SQL statement affects five or more database rows, the use of bulk binds can improve performance considerably.]

Using BULK BIND to improve performance:

• Bulk binds improve performance by minimizing the number of context switches between the PL/SQL and SQL engines. 
• With bulk binds, entire collections(not just individual elements) are passed to the SQL engines for UPDATE the record at once.

Example:

DECLARE
 TYPE empno_type is table of emp.empno%type
 index by pls_integer;
 v_emp_no  empno_type;
BEGIN
 select empno bulk collect into v_emp_no 
 from emp where deptno in (10,20);
 forall i in v_emp_no.first..v_emp_no.last
    update emp set sal=sal+1000 where empno=v_emp_no(i);
commit;

END;

FORALL Statement:

• The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine.

• It allows only DML (INSERT,UPDATE AND DELETE)

• The collection subscript cannot be an expression.

• All collection elements in the specified range must exist. If an element is missing or was deleted, you get an error.

Example:

DECLARE
   TYPE NumList IS TABLE OF NUMBER;
   depts NumList := NumList(10, 20, 30, 40);
BEGIN
   depts.DELETE(3);  -- delete third element
   FORALL i IN depts.FIRST..depts.LAST
      DELETE FROM emp WHERE deptno = depts(i);
      -- raises an "element does not exist" exception
END;

FORALL with SAVE EXCEPTION:

Add SAVE EXCEPTIONS to a FORALL statement, and Oracle Database will save any exceptions that are raised during the execution of individual insert, update, delete, or merge statements generated by the FORALL.

• Using FORALL  i am inserting 100 record into a table, after 50 record failed with error.Then remaining record will not insert into the table. 
• Using FORALL with SAVE EXCEPTION , the error record will ignore( or error record will insert into your error log table) and remaining record will insert .

Create one table call exception_test.
Example:

  DECLARE

  TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

  l_tab          t_tab := t_tab();
  l_error_count  NUMBER;
  
  ex_dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
  -- Fill the collection.
  FOR i IN 1 .. 100 LOOP
    l_tab.extend;
    l_tab(l_tab.last).id := i;
  END LOOP;

  -- Cause a failure.
  l_tab(50).id := NULL;
  l_tab(51).id := NULL;
  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.
  BEGIN
    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
      INSERT INTO exception_test
      VALUES l_tab(i);
  EXCEPTION
    WHEN ex_dml_errors THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count LOOP
        DBMS_OUTPUT.put_line('Error: ' || i || 
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;
  END;
END;
/

Question For Collections

PL/SQL Interview Question  for Collections.

What is Collection? How many type of collection?
Why or when we will use collection?
What is the difference between PL/SQL table, Nested table and Varrays ?
What are common exception related with collection?
What are common methods of handling exceptions?
Give sample defination of varray of object and table of object?
What is the difference between Collection and Record?
What is inline storage and out of line stogare of the nested table?
Write a program using collections.

Difference Between Collection and Record

What is the difference between Collection and Records?

Collection	Record
The internal components always have the same data type, and are called elements	The internal components can have different data types, and are called fields
You can access each element of a collection variable by its unique index	You can access each field of a record variable by its name
Syntax: variable_name(index).	Syntax: variable_name.field_name
To create a collection variable : define a collection type and then create a variable of that type or use %TYPE.	To create a record variable: define a RECORD type and then create a variable of that type or use %ROWTYPE or %TYPE.
You can create a collection of records.	You can create a record that contains collections.

Collection Methods

The following collection methods help generalize code, make collections easier to use, and make your applications easier to maintain:

Method Name	Purpose
EXISTS(n)	Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
COUNT	Returns the number of elements that a collection currently contains.
LIMIT	Checks the Maximum Size of a Collection.
FIRST	Checks the Maximum Size of a Collection.
LAST	Returns the last (largest) index numbers in a collection that uses integer subscripts.
PRIOR(n)	Returns the index number that precedes index n in a collection.
NEXT(n)	Returns the index number that succeeds index n.
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.
TRIM	Removes one element from the end of a collection.
TRIM(n)	Removes n elements from the end of a collection.
DELETE	Removes all elements from a collection, setting COUNT to 0.
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.

Collection Exceptions

The following table provides the collection exceptions(or we can say predefined exceptions) and when they are raised:

Exception	Raised when	Oracle Error
COLLECTION_IS_NULL	You try to operate on an atomically null collection.	ORA-06531
NO_DATA_FOUND	A subscript designates an element that was deleted	ORA-01403
SUBSCRIPT_BEYOND_COUNT	A subscript exceeds the number of elements in a collection	ORA-06533
SUBSCRIPT_OUTSIDE_LIMIT	A subscript is outside the legal range	ORA-06532
VALUE_ERROR	A subscript is null or not convertible to an integer	ORA-06502

 Raised in program:

DECLARE
TYPE NumList IS TABLE OF NUMBER;
nums NumList;  -- atomically null
BEGIN
/* Assume execution continues despite the raised exceptions. */
   nums(1) := 1;            -- raises COLLECTION_IS_NULL       (1)
   nums := NumList(1,2);  -- initialize table
   nums(NULL) := 3          -- raises VALUE_ERROR              (2)
   nums(0) := 3;            -- raises SUBSCRIPT_OUTSIDE_LIMIT  (3)
   nums(3) := 3;            -- raises SUBSCRIPT_BEYOND_COUNT   (4)
   nums.DELETE(1);  -- delete element 1

   IF nums(1) = 1 THEN ...  -- raises NO_DATA_FOUND            (5)

Example:

declare
  TYPE TYPE_NAME_NUM IS TABLE OF INTEGER;
  nums TYPE_NAME_NUM; /*:=TYPE_NAME_NUM(100,200,300,400)*/;
begin
  null;
  nums(1) := 1;
exception
  when COLLECTION_IS_NULL then
    dbms_output.put_line(sqlerrm);
/*ORA-06531: Reference to uninitialized collection*/
  
end;

Associative Array(Index by Table) Vs Nested Table Vs VARRAY

What is difference between Index By Tables,Nested Tables and Varrays?

The chart below lists the properties of the Index By Tables,Nested Tables and Varrays

Index By Table	Nested Table	Varrays
Subscript Characteristics : Can be arbitrary numbers or strings. Need not be sequential.	Subscript Characteristics : Sequential numbers, starting from one.	Subscript Characteristics : Sequential numbers, starting from one.
Size : Unbounded i.e. the number of elements it can hold is not pre-defined	Size: Unbounded i.e. the number of elements it can hold is not pre-defined	Size: Bounded i.e. holds a declared number of elements, though this number can be changed at run time
Database Storage: Index by tables can be used in PL/SQL programs only, cannot be stored in the database.	Database Storage: Can be stored in the database using equivalent SQL types, and manipulated through SQL.	Database Storage: Can be stored in the database using equivalent SQL types, and manipulated through SQL (but with less ease than nested tables)
Referencing and lookups: Works as key-value pairs.e.g. Salaries of employees can be stored with unique employee numbers used as subscripts sal(102) := 2000;	Referencing and lookups: Similar to one-column database tables. Oracle stores the nested table data in no particular order. But when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1.	Referencing and lookups: Standard subscripting syntax e.g. color(3) is the 3rd color in varray color
Flexibility to changes: Most flexible. Size can increase/ decrease dynamically. Elements can be added to any position in the list and deleted from any position.	Flexibility to changes: Almost like index-by tables, except that subscript values are not as flexible. Deletions are possible from non-contiguous positions.	Flexibility to changes: Not very flexible. You must retrieve and update all the elements of the varray at the same time.

Collection In PL/SQL

An Oracle PL/SQL collection is a single-dimensional array; it consists of one or more elements accessible through an index value. Collections are used in some of the most important performance optimization features of PL/SQL, such as BULK COLLECT.(Collection is a group of element of same type.Each element in the list has a unique position number or label we called as "subscript".).

PL/SQL provides three collection types :

1. PL/SQL Table or Index-by tables or Associative array
2. Nested table
3. Variable-size array or Varray.

Syntax :

1. Collection Type Definition:

TYPE type_name IS 
{ assoc_array_type_def|nested_table_type_def| varray_type_def} ;

2. assoc_array_type_def:

TABLE OF element_type [ NOT NULL ]
[ INDEX BY { PLS_INTEGER | BINARY_INTEGER | VARCHAR2 ( v_size ) } ]

3. nested_table_type_def:

TABLE OF element_type [ NOT NULL ]

4. varray_type_def:

{VARRAY | VARYING ARRAY } ( size_limit )
OF element_type [ NOT NULL ]

5. element_type:

{ cursor_name%ROWTYPE
| db_table_name{%ROWTYPE | .column_name%TYPE}
| object_name%TYPE
| [REF] object_type_name
| scalar_datatype_name
| record_name[.field_name]%TYPE
| record_type_name
| variable_name%TYPE
}

[element_type: The data type of the collection element (any PL/SQL data type except REF CURSOR)].

6. Collection Variable Declarations:

collection_name  type_name;

The type definition of a nested table or varray can appear either in the declarative part of a block, subprogram, package specification, or package body (in which case it is local to the block, subprogram, or package) or in the CREATE TYPE Statement (in which case it is a standalone stored type).


Here are some guidelines when we need to use which collection:

Use index by tables when:

• Your program needs small lookups.
• The collection can be made at run time in the memory when the package or procedure is initialized.
• The data volume is unknown beforehand.
• The subscript values are flexible (e.g. strings, negative numbers, non-sequential).
• You do not need to store the collection in the database.

Use nested tables when:

• The data needs to be stored in the database
• The number of elements in the collection is not known in advance
• The elements of the collection may need to be retrieved out of sequence
• Updates and deletions affect only some elements, at arbitrary locations
• Your program does not expect to rely on the subscript remaining stable, as their order may change 
• when nested tables are stored in the database.

Use varrays when:

• The data needs to be stored in the database.
• The number of elements of the varray is known in advance.
• The data from the varray is accessed in sequence.
• Updates and deletions happen on the varray as a whole and not on arbitrarily located elements in the varray.

Check Associative Array(Index by Table) Vs Nested Table Vs VARRAY

Examples:
Check three type of collection use in program

DECLARE
   TYPE nested_type IS TABLE OF VARCHAR2(30);
   TYPE varray_type IS VARRAY(5) OF INTEGER;
   TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
   v1 nested_type;
   v2 varray_type;
   v3 assoc_array_num_type;
   v4 assoc_array_str_type;
   v5 assoc_array_str_type2;
BEGIN
-- an arbitrary number of strings can be inserted v1
   v1 := nested_type('Shipping','Sales','Finance','Payroll'); 
   v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
   v3(99) := 10; -- Just start assigning to elements
   v3(7) := 100; -- Subscripts can be any integer values
   v4(42) := 'Smith'; -- Just start assigning to elements
   v4(54) := 'Jones'; -- Subscripts can be any integer values
   v5('Canada') := 'North America'; -- Just start assigning to elements
   v5('Greece') := 'Europe';        -- Subscripts can be string values
END;
/

Check All The Collection Methods

Check Collection Exceptions

Difference Between Collection and Record

Interview Questions