Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

self referencing variable (Recursive)

68077Nov 10 2005 — edited Nov 10 2005
Hi all,
I am executing an anonymous pl/sql block of code and need to build a tree structure in memory.
eg

type Level2Node is record(Total Number);
type Level2List is table of Level2Node index by varchar2(8);
type Level1Node is record (Children Level2List, Total number);
type Level1List is table of Level1Node index by varchar2(8);
type MainNode is record (Children Level2List, Total number);

mynode MainNode;

Ok this is great if I have a fixed number of levels and perform different operations at each level. Unfortunately my number of levels may vary and I need to perform the same operations (Which are quite large) at each level.

As each level is a different datatype I have to write a new copy of the procedure for each level and change every level each time I need to change the calculations.

The access I have to the system is query only so I cant create a database type with a reference object and need to build the structures purely in the session.

I can write the recursive pl/sql without a problem if anyone knows how to build a "recursive variable"? Something like the code below which does not work. (Chicken and egg problem. and the reference keyword only workd in objects)

type node is record (children nodelist, total number);
type nodelist is table of node index by varchar2(8);
mynode node;

Does anyone know how to achieve this?

Thanks
Rod
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 8 2005
Added on Nov 10 2005
5 comments
630 views