Collections in Oracle PL/SQL

-

Oracle uses collections in PL/SQL the same way other languages use arrays. Oracle provides three basic collections, each with an assortment of methods.

  • Index-By Tables (Associative Arrays)
  • Nested Table
  • Varrays
  • Collection Methods
  • Multiset Operations
  • Multidimensional Collections
Related articles.
  • Associative Arrays in Oracle 9i
  • Bulk Binds (BULK COLLECT & FORALL) and Record Processing in Oracle

Index-By Tables (Associative Arrays)

The first type of collection is known as index-by tables. These behave in the same way as arrays except that have no upper bounds, allowing them to constantly extend. As the name implies, the collection is indexed using BINARY_INTEGER values, which do not need to be consecutive. The collection is extended by assigning values to an element using an index value that does not currently exist.
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE table_type IS TABLE OF NUMBER(10)
    INDEX BY BINARY_INTEGER;
  
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN
  -- Initialise the collection.
  << load_loop >>
  FOR i IN 1 .. 5 LOOP
    v_tab(i) := i;
  END LOOP load_loop;
  
  -- Delete the third item of the collection.
  v_tab.DELETE(3);
  
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

The number 1
The number 2
The number 4
The number 5

PL/SQL procedure successfully completed.

SQL>
In Oracle 9i Release 2 these have been renamed to Associative Arrays and can be indexed by BINARY INTEGER or VARCHAR2.

Nested Table Collections

Nested table collections are an extension of the index-by tables. The main difference between the two is that nested tables can be stored in a database column but index-by tables cannot. In addition some DML operations are possible on nested tables when they are stored in the database. During creation the collection must be dense, having consecutive subscripts for the elements. Once created elements can be deleted using the DELETE method to make the collection sparse. The NEXTmethod overcomes the problems of traversing sparse collections.
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE table_type IS TABLE OF NUMBER(10);
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN

  -- Initialise the collection with two values.
  v_tab := table_type(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := i;
  END LOOP load_loop;
  
  -- Delete the third item of the collection.
  v_tab.DELETE(3);
  
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

The number 1
The number 2
The number 4
The number 5

PL/SQL procedure successfully completed.

SQL>

Varray Collections

A VARRAY is similar to a nested table except you must specifiy an upper bound in the declaration. Like nested tables they can be stored in the database, but unlike nested tables individual elements cannot be deleted so they remain dense.
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE table_type IS VARRAY(5) OF NUMBER(10);
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN
  -- Initialise the collection with two values.
  v_tab := table_type(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := i;
  END LOOP load_loop;
  
  -- Can't delete from a VARRAY.
  -- v_tab.DELETE(3);

  -- Traverse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

The number 1
The number 2
The number 3
The number 4
The number 5

PL/SQL procedure successfully completed.

SQL>
Extending the load_loop to 3..6 attempts to extend the VARRAY beyond it's limit of 5 elements resulting in the following error.
DECLARE
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at line 12

Collection Methods

A variety of methods exist for collections, but not all are relevant for every collection type.
  • EXISTS(n) - Returns TRUE if the specified element exists.
  • COUNT - Returns the number of elements in the collection.
  • LIMIT - Returns the maximum number of elements for a VARRAY, or NULL for nested tables.
  • FIRST - Returns the index of the first element in the collection.
  • LAST - Returns the index of the last element in the collection.
  • PRIOR(n) - Returns the index of the element prior to the specified element.
  • NEXT(n) - Returns the index of the next element after the specified element.
  • EXTEND - Appends a single null element to the collection.
  • EXTEND(n) - Appends n null elements to the collection.
  • EXTEND(n1,n2) - Appends n1 copies of the n2th element to the collection.
  • TRIM - Removes a single element from the end of the collection.
  • TRIM(n) - Removes n elements from the end of the collection.
  • DELETE - Removess all elements from the collection.
  • DELETE(n) - Removes element n from the collection.
  • DELETE(n1,n2) - Removes all elements from n1 to n2 from the collection.

Multiset Operations

Oracle provides MULTISET operations against collectsion, including the following.
MULTISET UNION joins the two collections together, doing the equivalent of a UNION ALL between the two sets.
SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6);
  l_tab2 t_tab := t_tab(5,6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET UNION l_tab2;
  
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;
/
1
2
3
4
5
6
5
6
7
8
9
10

PL/SQL procedure successfully completed.

SQL>
The DISTINCT keyword can be added to any of the multiset operations to removes the duplicates. Adding it to the MULTISET UNION makes it the equivalent of a UNION between the two sets.
SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6);
  l_tab2 t_tab := t_tab(5,6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET UNION DISTINCT l_tab2;
  
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;
/
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

SQL>
MULTISET EXCEPT returns the elements of the first set that are not present in the second set.
SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6,7,8,9,10);
  l_tab2 t_tab := t_tab(6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET EXCEPT l_tab2;
  
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;
/
1
2
3
4
5

PL/SQL procedure successfully completed.

SQL>
MULTISET INTERSECT returns the elements that are present in both sets.
SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6,7,8,9,10);
  l_tab2 t_tab := t_tab(6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET INTERSECT l_tab2;
  
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;
/
6
7
8
9
10

PL/SQL procedure successfully completed.

SQL>

Multidimensional Collections

In addition to regular data types, collections can be based on record types, allowing the creation of two-dimensional collections.
SET SERVEROUTPUT ON

-- Collection of records.
DECLARE
  TYPE t_row IS RECORD (
    id  NUMBER,
    description VARCHAR2(50)
  );

  TYPE t_tab IS TABLE OF t_row;
  l_tab t_tab := t_tab();
BEGIN
  FOR i IN 1 .. 10 LOOP
    l_tab.extend();
    l_tab(l_tab.last).id := i;
    l_tab(l_tab.last).description := 'Description for ' || i;
  END LOOP;
END;
/

-- Collection of records based on ROWTYPE.
CREATE TABLE t1 (
  id  NUMBER,
  description VARCHAR2(50)
);

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF t1%ROWTYPE;
  l_tab t_tab := t_tab();
BEGIN
  FOR i IN 1 .. 10 LOOP
    l_tab.extend();
    l_tab(l_tab.last).id := i;
    l_tab(l_tab.last).description := 'Description for ' || i;
  END LOOP;
END;
/
For multidimentional arrays you can build collections of collections.
DECLARE
  TYPE t_tab1 IS TABLE OF NUMBER;
  TYPE t_tab2 IS TABLE OF t_tab1;
  l_tab1 t_tab1 := t_tab1(1,2,3,4,5);
  l_tab2 t_tab2 := t_tab2();
BEGIN
  FOR i IN 1 .. 10 LOOP
    l_tab2.extend();
    l_tab2(l_tab2.last) := l_tab1;
  END LOOP;
END;
/

Comments

  1. Unknown1 May 2014 at 02:38

    This comment has been removed by the author.

    ReplyDelete
  2. Unknown15 July 2016 at 23:31

    This comment has been removed by the author.

    ReplyDelete

Post a Comment

Popular posts from this blog

Oracle Purchasing Module Step by Step in R12

-
Image
PURCHASING  All the Purchasing related activity information will be declared in Purchasing              Procure 2 Pay Cycle : Requisition à RFQ à Quotation à Quotation Analysis à Purchasing à Receiving à Invoice  à  Payment à Reconciliation PO-Integration :  PO  is integrated by GL, AP, I Supplier, Procurement, and Project Accounting  PO  Responsibility             Step1: Define  PO  Responsibility                         Nav: System administrator à Security à Responsibility à Define             Step2: Assign  PO  options to the PO Responsibility                 ...
Read more

Sub Ledger Accounting SLA (Complete Functional Information)

-
Image
SubLegder Accounting in R12 SLA Part -1 In these article, we will explore the very basics of SLA and why it exists. Firstly, what exactly does SLA do? SLA is a module which now sits between the SubLedgers like AP/AR etc and the General Ledger. Have a look at this diagram below.  As you will notice, SLA can act as a mediator between the subledgers and Oracle General Ledger. Before we progress, some terminologies of R12 must be revisited. In 11i we had set of books, and in R12 we call them Ledgers. Likewise in R12 we also have secondary ledgers and reporting ledgers. Hence from 11i perspective think of Ledger as Set of Books. As for Subledger, a Subledger is nothing but a module like AP/AR/PO/Inventory etc. In the diagram below, the second scenario is explained whereby let's say that payables module generates a charge account segment combination for an invoice distribution line as A.B.C.D. In such case, if SLA module is not customized, then the very same A.B.C.D ...
Read more

Difference between ATO and PTO in oracle apps

-
Difference between ATO and PTO in oracle apps 1) MAKE TO ORDER: This is based on Customer specification, you make the product and supply it to them. This is applicable for Engineering items or any Product which you can make based on Customer order Example: Engineering Items 2) ASSEMBLE TO ORDER: This is you have items in parts and it needs to be assembled according to customer requirements For example you have Laptop coming with standard configuration of 160GB Hard disk and 1 GB RAM. But there are customer orders coming in asking for 200GB Hard Disk and 2 GB RAM and if you can assemble them to suit Customer Requirement 3) CONFIGURE TO ORDER: This is Configuring the Product itself to suit Customer requirements Example. The Thread Manufacturing unit Machineries are configured. I have seen Laxmi Machine Works in INDIA which manufactures textile Machineries configure the machineries based on No.of spindles 4) PICK TO ORDER: This is same as Computer Example in ATO. But the  differenc...
Read more