Home » SQL & PL/SQL » SQL & PL/SQL » Create collection of nested table
Create collection of nested table [message #671962] Wed, 26 September 2018 06:18 Go to next message
purnima1
Messages: 79
Registered: June 2014
Member
Hi all,
As we all know there are several types of collection Oracle :
1) Nested table
2) Associative Arrays

Now I want to create the associative array of nested table

for eg associative array "a_1" is created .I want to store in following dormat

a(1) >>>> (1,2,3,4)
a(2)>>>> (9,90,78)

Now "a_1" has two rows and each row has stored nested table . I tried writing code but it is not giving correct result

currently in code I am storing one row with nested table having 2 elements . But first element is getting overwritten by second
DECLARE 

 TYPE T IS TABLE OF NUMBER;
 


TYPE C_t is table of T index by binary_integer;
C_TAB C_T;

BEGIN 

C_TAB(1):= T(500);
C_TAB(1).extend ;
C_TAB(1):= T(600);

FOR I IN C_TAB.FIRST..C_TAB.LAST LOOP
  --DBMS_OUTPUT.PUT_LINE ( i);
   DBMS_OUTPUT.PUT_LINE ( C_TAB(I).count );
  FOR J IN 1..C_TAB(I).count LOOP
     DBMS_OUTPUT.PUT_LINE ( j);
DBMS_OUTPUT.PUT_LINE ( C_TAB(i)(j));
-- 
 END LOOP;
--
END LOOP;

END ;
 
Re: Create collection of nested table [message #671963 is a reply to message #671962] Wed, 26 September 2018 06:55 Go to previous messageGo to next message
_jum
Messages: 573
Registered: February 2008
Senior Member
Try (note, that C_TAB can be sparse!):

DECLARE 

  TYPE t IS TABLE OF NUMBER;

  TYPE  c_t IS TABLE OF t INDEX BY binary_integer;
  C_TAB c_t;

  l_idx    INTEGER;

BEGIN 

C_TAB(1):= T(500);
C_TAB(2):= T(600);
C_TAB(4):= T(100,200,300);

l_idx := C_TAB.first;

  WHILE (l_idx is not null)

  LOOP
    dbms_output.put_line('I='|| l_idx);

    FOR j IN 1..C_TAB(l_idx).count 
    LOOP
      DBMS_OUTPUT.PUT_LINE ( '  J='||j);
      DBMS_OUTPUT.PUT_LINE ( '    VAL='|| C_TAB(l_idx)(j));
    END LOOP;

    l_idx := C_TAB.next(l_idx);
  END LOOP;  

END ;

I=1
J=1
VAL=500
I=2
J=1
VAL=600
I=4
J=1
VAL=100
J=2
VAL=200
J=3
VAL=300


[Updated on: Wed, 26 September 2018 06:58]

Report message to a moderator

Re: Create collection of nested table [message #671964 is a reply to message #671962] Wed, 26 September 2018 07:06 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
purnima1 wrote on Wed, 26 September 2018 04:18
Hi all,
As we all know there are several types of collection Oracle :
1) Nested table
2) Associative Arrays

Now I want to create the associative array of nested table

for eg associative array "a_1" is created .I want to store in following dormat

a(1) >>>> (1,2,3,4)
a(2)>>>> (9,90,78)

Now "a_1" has two rows and each row has stored nested table . I tried writing code but it is not giving correct result

currently in code I am storing one row with nested table having 2 elements . But first element is getting overwritten by second

IMO, above is effectively useless in the real world since this data structure can NOT be used within any SQL statement.
Re: Create collection of nested table [message #671965 is a reply to message #671963] Wed, 26 September 2018 07:24 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
Hi There,
Thanks for giving some direction just wanted to ask is it possible to extend the table stored at
C_TAB(4):= T(100,200,300) and add new value in it later on??
If that is possible it will resolve my problem
Re: Create collection of nested table [message #671966 is a reply to message #671965] Wed, 26 September 2018 07:35 Go to previous messageGo to next message
_jum
Messages: 573
Registered: February 2008
Senior Member
Quote:
just wanted to ask is it possible to extend the table stored at
C_TAB(4):= T(100,200,300) and add new value in it later on??
Did you try, is it possible?
Re: Create collection of nested table [message #671967 is a reply to message #671965] Wed, 26 September 2018 07:37 Go to previous messageGo to next message
flyboy
Messages: 1886
Registered: November 2006
Senior Member
Hi,

We all also know that the work with PL/SQL Collections is very well described in PL/SQL User's Guide and Reference.
It is available e.g. online on https://docs.oracle.com/. You would benefit from consulting it.

As it seems that you tried to get union of two nested tables as a result, why not read the relevant section "Assigning Set Operation Results to Nested Table Variables"?
You would end with something like this:
 C_TAB(1):= T(500);
 --C_TAB(1).extend ;
 C_TAB(1):= C_TAB(1) multiset union T(600);
Re: Create collection of nested table [message #671968 is a reply to message #671962] Wed, 26 September 2018 08:30 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
You have two-dimensional collection, so use index for both dimensions:

SQL> SET SERVEROUTPUT ON FORMAT WRAPPED
SQL> DECLARE
  2      TYPE T IS TABLE OF NUMBER;
  3      TYPE C_t is table of T index by binary_integer;
  4      C_TAB C_T;
  5  BEGIN
  6      C_TAB(1):= T(500);
  7      C_TAB(1).extend ;
  8      C_TAB(1)(C_TAB(1).COUNT) := 600; -- add second element to inner dimension
  9      FOR I IN C_TAB.FIRST..C_TAB.LAST LOOP
 10        DBMS_OUTPUT.PUT_LINE ('C_TAB(' || I || '):');
 11        FOR J IN 1..C_TAB(I).count LOOP
 12          DBMS_OUTPUT.PUT_LINE ('          C_TAB(' || I || ')(' || J || ') = ' || C_TAB(i)(j));
 13        END LOOP;
 14      END LOOP;
 15  END;
 16  /
C_TAB(1):
          C_TAB(1)(1) = 500
          C_TAB(1)(2) = 600

PL/SQL procedure successfully completed.

SQL>

SY.
Re: Create collection of nested table [message #671970 is a reply to message #671966] Wed, 26 September 2018 09:11 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
yes it is possible
Re: Create collection of nested table [message #671971 is a reply to message #671970] Wed, 26 September 2018 09:21 Go to previous message
purnima1
Messages: 79
Registered: June 2014
Member
Hi All,
Thanks for your input. below mention code is running fine .
DECLARE 

  TYPE t IS TABLE OF NUMBER;

  TYPE  c_t IS TABLE OF t INDEX BY binary_integer;
  C_TAB c_t;

  l_idx    INTEGER;

BEGIN 

C_TAB(1):= T(500);
C_TAB(1):= C_TAB(1) multiset union T(600);
C_TAB(4):= T(100,200,300);
C_TAB(4):= C_TAB(4) multiset union T(400);
l_idx := C_TAB.first;

  WHILE (l_idx is not null)

  LOOP
    dbms_output.put_line('I='|| l_idx);

    FOR j IN 1..C_TAB(l_idx).count 
    LOOP
      DBMS_OUTPUT.PUT_LINE ( '  J='||j);
      DBMS_OUTPUT.PUT_LINE ( '    VAL='|| C_TAB(l_idx)(j));
    END LOOP;

    l_idx := C_TAB.next(l_idx);
  END LOOP;  

END ;

I=1
J=1
VAL=500
J=2
VAL=600
I=4
J=1
VAL=100
J=2
VAL=200
J=3
VAL=300
J=4
VAL=400

Previous Topic: In Class with sub query
Next Topic: Using LISTAGG in a subquery
Goto Forum:
  


Current Time: Mon Sep 21 13:07:08 CDT 2020