| Create collection of nested table [message #671962] |
Wed, 26 September 2018 06:18  |
 |
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  |
_jum
Messages: 577
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 #671967 is a reply to message #671965] |
Wed, 26 September 2018 07:37 |
flyboy
Messages: 1901
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 |
Solomon Yakobson
Messages: 3227
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 #671971 is a reply to message #671970] |
Wed, 26 September 2018 09:21 |
|
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
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
