Home » SQL & PL/SQL » SQL & PL/SQL » Initializing varray inside another varray
Initializing varray inside another varray [message #425158] Wed, 07 October 2009 18:42 Go to next message
prasenjit.dutta
Messages: 14
Registered: July 2007
Junior Member

Can any body guide me how to initialize one varray inside another varray? I am able to initialize the mother varray, but I have no idea how to initialize the child varray inside that varray. Also I don't know how to assign value from a cursor to the child varray.Same like above, I can assign value to the mother one, but unable to do that in child varray. I need just high level idea for this, I cannot paste the code here as it is a long procedure.
Re: Initializing varray inside another varray [message #425159 is a reply to message #425158] Wed, 07 October 2009 19:15 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>Can any body guide me how to initialize one varray inside another varray?
WHY?


PL/SQL is not VB or C++

When your only tool is a hammer, you treat every problem like a nail.
Re: Initializing varray inside another varray [message #425162 is a reply to message #425158] Wed, 07 October 2009 21:18 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
if by varray you mean the oracle objects based varray, then there may indeed be a way to do what you want. Look at the oracle manuals for the solution (I'd give it to you but I don't know it).

If you are talking about plsql associative arrays (aka. index by tables) then these do not have that functionality (unless it has been added very recently). You have to "simulate" nested arrays using basic math on your indexes.

On another note, following BlackSwan, that since oracle offered up nested varray capability and nested tables within nested tables, I have never had a need to use it and I have done a huge amount of plsql writing and trigger writing. That should tell you something. You may have a legitimate need for it, but you might also wish to reconsider your approach to your problem.

You might also wish to provide additional detail of what you are trying to do so we can help you evaluate your approach.

Good luck, Kevin
Re: Initializing varray inside another varray [message #425327 is a reply to message #425162] Thu, 08 October 2009 11:16 Go to previous message
prasenjit.dutta
Messages: 14
Registered: July 2007
Junior Member

Yes, this is a oracle object based varray. I also agree that it is a very typical scenario that I am facing in my long coding career in Oracle. The object structure is attached here with for reference. Also I am trying to explain you the business requirement in very high level. If this is not doable at all, I need to find out some different way out obviously. Basically this is an interface where Oracle will extract some data and populate some database structure, from where FMW (BPEL process) will listen and generate the payload from that structure. Initially the requirement was like that, one single delivery has to be interfaced to the destination via FMW along with its detail information containing the sales order line information. That's why, order_header and order_line are two component inside the main object, where order_line is a varray. But recently a new requirement has come up like, in the line level, if the ordered item is serialized, the serial numbers have to be populated for each item/ line. So, another varray's requirement has come up which will be inside the order_line, and that is serial_number_tbl_t (serial_number_t). So my grey area is how to initialize and populate this varray inside the main varray (order_line). Also I will appreciate of some one can show me some different option, without much changing the structure, because order_header and order_line are already built, the code is developed and tested, and it was running fine with BPEL, and was interfacing perfectly with target system.
Previous Topic: :NEW in cursor ?
Next Topic: ora 22275
Goto Forum:
  


Current Time: Wed Dec 07 04:38:28 CST 2016

Total time taken to generate the page: 0.10169 seconds