Home » SQL & PL/SQL » SQL & PL/SQL » help needed in Collection (Oracle 10g)
help needed in Collection [message #391660] Thu, 12 March 2009 21:42
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
Hi All,
I need your help in building a collection of tabletype.
I have done most part of the code. I need to make a big large collection to hold the records. I have shown the result set at the bottom. Also I included my comments where i need logic in the code.

Acct_Nbr   Party_Id
-------    --------
00110	    1
00110       2
00101	    1
00111       2
01000       3

Party_Id   Name    state       family
--------   ----  ---------    ---------
  1        ABC     PA          single
  2        xyz     VA          Family
  3        pqr     De          single
  


Mstr_Accnt_nbr   Cust_accnt_nbr    Accnt_Id  Accnt_Type  Party_id
------------     --------------     -------   --------   ----------
 00110		   2345               1		Credit		1
 00110             1234               2		Saving		2
 01000             1345               1		Credit		3
 00111             1245               2		Saving		2
 00101             2356               1		Credit		1

Create type Party_id_type as object (party_id Number);
Create type accnt_nbr_type as object (mstr_accnt_nbr Varchar2);
Create Type  Accnt_info_type as Object (mstr_accnt_nbr Varchar2, Accnt_type Varchar2,
                                         cust_accnt_nbr [SIZE=1][/SIZE]varchar2, accnt_id Number);

Declare

Type party_tab is Table of party_id_type;
Type accnt_tab is Table of acccnt_nbr_type;
Type accnt_info_tab is Table of accnt_info_type;

l_party_id  party_tab := party_tab ();
accnt_rec  accnt_tab := accnt_tab ();
accnt_list accnt_info_tab := accnt_info_tab();
v_party_id  Number;
v_name varchar2;
v_st varchar2;
v_fml varchar2;

Begin

Select party_id_type (party_id) Bulk collect into l_party_id
               From (select party_id from table1 where acct_nbr = '00110');
For i in l_party_id.First..l_party_id.Last
Loop
   v_party_id := l_party_id (i).party_id;
   Select name,state,family Into v_name,v_st,v_fm From
           table2 where party_id = v_party_id;
   Select accnt_nbr_type(mstr_accnt_nbr) Bulk collect into accnt_rec
              From (Select mstr_accnt_nbr From table1 where party_id = v_party_id );

     For i In accnt_rec.First .. accnt_rec.Last
        Loop
	  v_accnt := accnt_rec (i).mstr_accnt_nbr;
            Select accnt_info_type (mstr_accnt_nbr,accnt_type,cust_accnt_nbr,accnt_id) Bulk Collect into accnt_list
               From (Select mstr_accnt_nbr,accnt_type,cust_accnt_nbr,accnt_id From table3 where mstr_accnt_nbr = v_accnt );
                
		 For I in accnt_list.First .. accnt_list.Last
			loop
  			   v_accnt_id := accnt_list (i).accnt_id;
			   v_cust_nbr := accnt_list (i).cust_accnt_nbr;
			   v_accnt_type := accnt_list (i).accnt_type;

                            ----Now I need to have all these variables in the one big large collection
			    ----Can you please give me the logic.
                        end loop;
	End loop;
  End Loop;
End;

Output Result Set should be like this in table type of collection.

Mstr_acnt_nbr  party_id  cust_accnt_nbr  accnt_type  accnt_id  name  state  
-------------  -------  -------------      ----------  ------  ---   ----- 
 00110          1          2345            credit       1       abc   pa
 00101          1          2356            credit       1       abc   pa
 00110          2          1234            saving       2       xyz   va
 00111          2          1245            saving       2       xyz   va
 01000          3          1345            credit       1       pqr   Dea            



[Updated on: Thu, 24 March 2011 05:56] by Moderator

Report message to a moderator

Previous Topic: ora-01735
Next Topic: How to Display N row for each person ID?
Goto Forum:
  


Current Time: Sat Dec 10 03:17:06 CST 2016

Total time taken to generate the page: 0.08057 seconds