Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Thursday, January 22, 2015

Oracle Collections Examples

--1)    Simple Nested Table
DECLARE
   TYPE TYP_NT_NUM IS TABLE OF NUMBER;

   Nt_tab   TYP_NT_NUM;
BEGIN
   Nt_tab :=
      TYP_NT_NUM (5,
                  10,
                  15,
                  20);

   FOR i IN 1 .. nt_tab.COUNT
   LOOP
      DBMS_OUTPUT.put_line ('The Value Nested Table ' || nt_tab (i));
   END LOOP;
END;

--2)    Simple VARRAY
DECLARE
   TYPE TYP_V_DAY IS VARRAY (7) OF VARCHAR2 (15);

   v_tab   TYP_V_DAY;
BEGIN
   v_tab :=
      TYP_V_DAY ('Sunday',
                 'Monday',
                 'Tuesday',
                 'Wedneday',
                 'Thursday',
                 'Friday',
                 'Saturday');

   FOR i IN 1 .. v_tab.COUNT
   LOOP
      DBMS_OUTPUT.put_line ('The Value from VARRAY ' || v_tab (i));
   END LOOP;
END;

--3)    Simple PL/SQL Table
DECLARE
   TYPE TYP_TAB IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER;

   my_tab   TYP_TAB;
BEGIN
   my_tab (1) := 5;
   my_tab (2) := 10;
   my_tab (3) := 15;

   FOR i IN 1 .. my_tab.COUNT
   LOOP
      DBMS_OUTPUT.put_line ('The Value from Index By Table ' || my_tab (i));
   END LOOP;
END;

DECLARE
   TYPE TYP_TAB IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER;

   my_tab   TYP_TAB;
   i        NUMBER := 1;
BEGIN
   my_tab (1) := 5;
   my_tab (2) := 10;
   my_tab (3) := 15;

   WHILE my_tab.EXISTS (i)
   LOOP
      DBMS_OUTPUT.put_line ('The Value from Index By Table ' || my_tab (i));
      i := i + 1;
   END LOOP;
END;

--Character Type PL/SQL Table
DECLARE
   TYPE TYPE_TAB_DAYS IS TABLE OF PLS_INTEGER
      INDEX BY VARCHAR2 (20);

   day_tab   TYPE_TAB_DAYS;
   x_index   VARCHAR2 (20);
   p         VARCHAR2 (20) := 'sdf';
BEGIN
   day_tab ('Monday') := 10;
   day_tab ('Tuesday') := 20;
   day_tab ('Wednesday') := 30;
   day_tab (p) := 40;
   x_index := day_tab.FIRST;

   LOOP
      IF x_index IS NULL
      THEN
        EXIT;
      END IF;

      DBMS_OUTPUT.put_line (
         'The Value from Index By Table ' || day_tab (x_index));
      x_index := day_tab.NEXT (x_index);
   END LOOP;
END;

--4)    Check if the Collection is NULL
DECLARE
   TYPE TYP_VAR_TAB IS VARRAY (30) OF VARCHAR2 (100);

   tab1   TYP_VAR_TAB;                         -- declared but not initialized
BEGIN
   IF Tab1 IS NULL
   THEN
      -- NULL collection, have to initialize it --
      tab1 :=
         TYP_VAR_TAB ('Sunday',
                      'Monday',
                      'Tuesday',
                      'Wedneday',
                      'Thursday',
                      'Friday',
                      'Saturday');
   END IF;                              -- Now, we can handle the collection -

   FOR i IN 1 .. tab1.COUNT
   LOOP
      DBMS_OUTPUT.put_line ('The Value from VARRAY ' || tab1 (i));
   END LOOP;
END;

--5)    We can assign values of one collection to the another  collections if they are of the same type
DECLARE
   TYPE TYPE_TAB_EMP IS TABLE OF NUMBER
      INDEX BY BINARY_INTEGER;

   TYPE TYPE_TAB_EMP2 IS TABLE OF NUMBER
      INDEX BY BINARY_INTEGER;

   tab1   TYPE_TAB_EMP;
   tab2   TYPE_TAB_EMP;
   tab3   TYPE_TAB_EMP2;
BEGIN
   tab1 (1) := 5;
   tab1 (2) := 10;
   tab1 (3) := 15;
   tab2 := tab1;

   -- OK tab3 := tab1 ; -- Error : types not similar
   FOR i IN 1 .. tab2.COUNT
   LOOP
      DBMS_OUTPUT.put_line ('The Value from Index By Table ' || tab2 (i));
   END LOOP;
END;

--Collection Methods
--EXITS(n):  Returns TRUE if the index element exists in the collection, else it returns FALSE.
DECLARE
   TYPE TYP_V_DAY IS VARRAY (7) OF VARCHAR2 (15);

   v_tab   TYP_V_DAY;
BEGIN
   v_tab :=
      TYP_V_DAY ('Sunday',
                 'Monday',
                 'Tuesday',
                 'Wedneday',
                 'Thursday',
                 'Friday',
                 'Saturday');

   IF v_tab.EXISTS (2)
   THEN
      DBMS_OUTPUT.put_line ('The Value from VARRAY ' || v_tab (2));
   END IF;
END;

DECLARE
   TYPE TYP_V_DAY IS VARRAY (7) OF VARCHAR2 (15);

   v_tab   TYP_V_DAY;
BEGIN
   v_tab :=
      TYP_V_DAY ('Sunday',
                 'Monday',
                 'Tuesday',
                 'Wedneday',
                 'Thursday',
                 'Friday',
                 'Saturday');

   IF v_tab.EXISTS (8)
   THEN
      DBMS_OUTPUT.put_line ('The Value from VARRAY ' || v_tab (2));
   ELSE
      DBMS_OUTPUT.put_line ('No Element Found in the collections');
   END IF;
END;

--COUNT: Returns the number of elements in a collection.
DECLARE
   TYPE TYP_TAB IS TABLE OF NUMBER;

   my_tab   TYP_TAB
               := TYP_TAB (1,
                           2,
                           3,
                           4,
                           5);
BEGIN
   DBMS_OUTPUT.Put_line ('COUNT = ' || TO_CHAR (my_tab.COUNT));
   my_tab.DELETE (2);
   DBMS_OUTPUT.Put_line ('COUNT = ' || TO_CHAR (my_tab.COUNT));
END;

--LIMIT : Returns the maximum number of elements that a varray can contain. Return NULL for Nested tables and Index-by table.
DECLARE
   TYPE TYP_ARRAY IS ARRAY (30) OF NUMBER;

   my_array   TYP_ARRAY := TYP_ARRAY (1, 2, 3);
BEGIN
   DBMS_OUTPUT.put_line ('Max array size is ' || my_array.LIMIT);
END;

--FIRST & LAST :
--Returns the first or last subscript of a collection. If the collection is empty, FIRST and LAST return NULL.
DECLARE
   TYPE TYP_TAB IS TABLE OF NUMBER;

   my_tab   TYP_TAB
               := TYP_TAB (1,
                           2,
                           3,
                           4,
                           5);
BEGIN
   FOR i IN my_tab.FIRST .. my_tab.LAST
   LOOP
      DBMS_OUTPUT.Put_line (
         'my_tab(' || LTRIM (TO_CHAR (i)) || ') = ' || TO_CHAR (my_tab (i)));
   END LOOP;
END;

DECLARE
   TYPE TYP_TAB IS TABLE OF NUMBER;

   my_tab   TYP_TAB
               := TYP_TAB (1,
                           2,
                           3,
                           4,
                           5);
BEGIN
  DBMS_OUTPUT.Put_line (my_tab.FIRST);
   DBMS_OUTPUT.Put_line (my_tab.LAST);

   FOR i IN my_tab.FIRST .. my_tab.LAST
   LOOP
      DBMS_OUTPUT.Put_line (
         'my_tab(' || LTRIM (TO_CHAR (i)) || ') = ' || TO_CHAR (my_tab (i)));
   END LOOP;
END;

DECLARE
   TYPE TYP_TAB IS TABLE OF PLS_INTEGER
      INDEX BY VARCHAR2 (1);

   my_tab   TYP_TAB;
BEGIN
   FOR i IN 65 .. 69
   LOOP
      my_tab (CHR (i)) := i;
   END LOOP;

   DBMS_OUTPUT.Put_Line (
      'First= ' || my_tab.FIRST || ' Last= ' || my_tab.LAST);
END;

--PRIOR(index) and NEXT(index) :
--Returns the previous or next subscript of the index element.

--If the index element has no predecessor, PRIOR (index) returns NULL. Likewise, if index has no successor, NEXT (index) returns NULL.
DECLARE
   TYPE TYP_TAB IS TABLE OF PLS_INTEGER
      INDEX BY VARCHAR2 (1);

   my_tab   TYP_TAB;
   c        VARCHAR2 (1);
BEGIN
   FOR i IN 65 .. 69
   LOOP
      my_tab (CHR (i)) := i;
   END LOOP;

   c := my_tab.FIRST;                                         -- first element

   LOOP
      DBMS_OUTPUT.Put_Line ('my_tab(' || c || ') = ' || my_tab (c));
      c := my_tab.NEXT (c);                       -- get the successor element
      EXIT WHEN c IS NULL;                                -- end of collection
   END LOOP;
END;

--Use the PRIOR() or NEXT() method to be sure that you do not access an invalid element:
DECLARE
   TYPE TYP_TAB IS TABLE OF PLS_INTEGER;

   my_tab   TYP_TAB
               := TYP_TAB (1,
                           2,
                           3,
                           4,
                           5);
BEGIN
   my_tab.DELETE (2);                    --delete an element of the collection

   FOR i IN my_tab.FIRST .. my_tab.LAST
   LOOP
      DBMS_OUTPUT.Put_Line (
         'my_tab(' || LTRIM (TO_CHAR (i)) || ') = ' || my_tab (i));
   END LOOP;
END;

--“NO DATA FOUND” Error
--In this example, we get an error because one element of the collection was deleted.
--One solution is to use the PRIOR()/NEXT() method:
DECLARE
   TYPE TYP_TAB IS TABLE OF PLS_INTEGER;

   my_tab   TYP_TAB
               := TYP_TAB (1,
                           2,
                           3,
                           4,
                           5);
   v        PLS_INTEGER;
BEGIN
   my_tab.DELETE (2);
   v := my_tab.FIRST;

   LOOP
      DBMS_OUTPUT.Put_Line (
         'my_tab(' || LTRIM (TO_CHAR (v)) || ') = ' || my_tab (v));
      v := my_tab.NEXT (v);                    -- get the next valid subscript
      EXIT WHEN v IS NULL;
   END LOOP;
END;

--Another solution is to test if the index exists before use it:
DECLARE
   TYPE TYP_TAB IS TABLE OF PLS_INTEGER;

   my_tab   TYP_TAB
               := TYP_TAB (1,
                           2,
                           3,
                           4,
                           5);
BEGIN
   my_tab.DELETE (2);

   FOR i IN my_tab.FIRST .. my_tab.LAST
   LOOP
      IF my_tab.EXISTS (i)
      THEN
         DBMS_OUTPUT.Put_Line (
            'my_tab(' || LTRIM (TO_CHAR (i)) || ') = ' || my_tab (i));
      END IF;
   END LOOP;
END;

--EXTEND[(n[,i])]  :
--Used to extend a collection (add new elements)
•    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.


DECLARE
   TYPE typ_nes_tab IS TABLE OF VARCHAR2 (20);

   tab1   typ_nes_tab;
   i      PLS_INTEGER;

   PROCEDURE PRINT (i IN PLS_INTEGER)
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('tab1(' || LTRIM (TO_CHAR (i)) || ') = ' || tab1 (
                                                                           i
                                                                        ));
   END;

   PROCEDURE printall
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('* Print all collection *');

      FOR i IN tab1.FIRST .. tab1.LAST
      LOOP
         IF tab1.EXISTS (i)
         THEN
            DBMS_OUTPUT.put_line (
               'tab1(' || LTRIM (TO_CHAR (i)) || ') = ' || tab1 (i)
            );
         END IF;
      END LOOP;
   END;
BEGIN
   tab1 := typ_nes_tab ('One');
   i := tab1.COUNT;
   DBMS_OUTPUT.put_line ('tab1.COUNT = ' || i);
   PRINT (i);
   tab1.EXTEND;
   i := tab1.COUNT;
   tab1 (i) := 'Two';
   printall;
   --Add two Empty elements --
   tab1.EXTEND (2);
   i := i + 1;
   tab1 (i) := 'Three';
   i := i + 1;
   tab1 (i) := 'Four';
   printall;
   --Add three elements with the same value as element 4 -- -
   tab1.EXTEND (3, 1);
   i := i + 3;
   printall;
END;

--TRIM[(n)]
--Used to decrease the size of a collection
• TRIM removes one element from the end of a collection.
• TRIM(n) removes n elements from the end of a collection.

DECLARE
   TYPE TYP_TAB IS TABLE OF VARCHAR2 (100);

   tab   TYP_TAB;
BEGIN
   tab := TYP_TAB ('One', 'Two', 'Three');

   FOR i IN tab.FIRST .. tab.LAST
   LOOP
      DBMS_OUTPUT.put_line (
         'tab(' || LTRIM (TO_CHAR (i)) || ') = ' || tab (i));
   END LOOP;

   -- add 3 element with second element value --
   DBMS_OUTPUT.put_line ('* add 3 elements *');
   tab.EXTEND (3, 2);

   FOR i IN tab.FIRST .. tab.LAST
   LOOP
      DBMS_OUTPUT.put_line (
         'tab(' || LTRIM (TO_CHAR (i)) || ') = ' || tab (i));
   END LOOP;

   -- suppress the last element --
   DBMS_OUTPUT.put_line ('* suppress the last element *');
   tab.TRIM;

   FOR i IN tab.FIRST .. tab.LAST
   LOOP
      DBMS_OUTPUT.put_line (
         'tab(' || LTRIM (TO_CHAR (i)) || ') = ' || tab (i));
   END LOOP;
END;

--If you try to suppress more elements than the collection contents, you get a SUBSCRIPT_BEYOND_COUNT exception.
DELETE[(n[,m])]
• 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(n,m) 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(n,m) does nothing
Caution : LAST returns the greatest subscript of a collection and COUNT returns the number of elements of a collection. If you delete some elements, LAST != COUNT.

--Suppression of all the elements
DECLARE
   TYPE TYP_TAB IS TABLE OF VARCHAR2 (100);

   tab   TYP_TAB;
BEGIN
   tab := TYP_TAB ('One', 'Two', 'Three');
   DBMS_OUTPUT.put_line ('Suppression of all elements');
   tab.DELETE;
   DBMS_OUTPUT.put_line ('tab.COUNT = ' || tab.COUNT);
END;

--Suppression of the second element
DECLARE
   TYPE TYP_TAB IS TABLE OF VARCHAR2 (100);

   tab   TYP_TAB;
BEGIN
   tab := TYP_TAB ('One', 'Two', 'Three');
   DBMS_OUTPUT.put_line ('Suppression of the 2nd element');
   tab.DELETE (2);
   DBMS_OUTPUT.put_line ('tab.COUNT = ' || tab.COUNT);
   DBMS_OUTPUT.put_line ('tab.LAST = ' || tab.LAST);

   FOR i IN tab.FIRST .. tab.LAST
   LOOP
      IF tab.EXISTS (i)
      THEN
         DBMS_OUTPUT.put_line (tab (i));
      END IF;
   END LOOP;
END;

--Caution: For Varrays, you can suppress only the last element. If the element does not exists, no exception is raised.

--Main collection exceptions
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)

--Multi-level Collections
DECLARE
   TYPE TYP_TAB IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER;

   TYPE TYP_TAB_TAB IS TABLE OF TYP_TAB
     INDEX BY PLS_INTEGER;

   tab1   TYP_TAB_TAB;
BEGIN
   FOR i IN 1 .. 3
   LOOP
      FOR j IN 1 .. 2
      LOOP
         tab1 (i) (j) := i + j;
         DBMS_OUTPUT.put_line (
               'tab1('
            || LTRIM (TO_CHAR (i))
            || ')('
            || LTRIM (TO_CHAR (j))
            || ') = '
            || tab1 (i) (j));
      END LOOP;
   END LOOP;
END;

--Collections of records
DECLARE
   TYPE TYP_TAB IS TABLE OF DEPT%ROWTYPE
      INDEX BY PLS_INTEGER;

   tb_dept   TYP_TAB;
   rec       DEPT%ROWTYPE;

   CURSOR CDEPT
   IS
      SELECT * FROM DEPT;
BEGIN
   OPEN CDEPT;

   LOOP
      FETCH CDEPT INTO rec;

      EXIT WHEN CDEPT%NOTFOUND;
      tb_dept (CDEPT%ROWCOUNT) := rec;
   END LOOP;

   FOR i IN tb_dept.FIRST .. tb_dept.LAST
   LOOP
      DBMS_OUTPUT.put_line (
         tb_dept (i).DNAME || ' - ' || tb_dept (i).deptno || ' - ' || F||tb_dept(i).LOC );
End loop;
End;


Best Blogger TemplatesBest Blogger Tips
Best Blogger Gadgets
Posted by Raju Chinthapatla at 2:51:00 PM
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)
If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect