SQL & PL/SQL

No procedure needed.

A simply hierarchic SQL using the CONNECT BY clause should be suffice.

with my_table_name( a_number, a_name, a_group, primary_number ) as (

  select 1, 'TEST 1', 'ABC', null from dual union all

  select 2, 'TEST 2', 'ABC', 1 from dual union all

  select 3, 'TEST 3', 'ABC', 1 from dual union all

  select 4, 'TEST 4', 'ABC', 2 from dual union all

  select 5, 'TEST 5', 'ABC', 2 from dual union all

  select 6, 'TEST 6', 'ABC', 3 from dual union all 

  select 7, 'TEST 7', 'ABC', 4 from dual union all 

  select 8, 'TEST 8', 'ABC', 5 from dual

)

select * from my_table_name

connect by prior a_number = primary_number

start with a_number = 2

order by a_number;

Thanks for your response mike ,

how can i execute if it is dynamic table with random data !!

Don't be silly - random data doesn't have a pattern.

Moved from General questions

katta vijay wrote:

Thanks for your response mike ,

how can i execute if it is dynamic table with random data !!

As already mentioned, random data doesn't have a tree structure so such a question is pointless to ask.

Also, a properly designed application and database doesn't have "dynamic" table names.  You should know the tables you're accessing and the structure of those tables.  The moment you start thinking "how can I do this dynamically?" you should stop yourself and ask "what's wrong with the design?"

Mike already provided the answer for how to traverse hierarchical data in a table.  That answers your question, and there's no need for any PL code to do it, as SQL is perfectly capable of processing it.

Another method if you're on 11gR2 upwards, is to use recursive subquery factoring, for example:

SQL> ed
Wrote file afiedt.buf
  1  with my_table_name(a_number, a_name, a_group, primary_number) as
  2                    (select 1, 'TEST 1', 'ABC', null from dual union all
  3                     select 2, 'TEST 2', 'ABC', 1 from dual union all
  4                     select 3, 'TEST 3', 'ABC', 1 from dual union all
  5                     select 4, 'TEST 4', 'ABC', 2 from dual union all
  6                     select 5, 'TEST 5', 'ABC', 2 from dual union all
  7                     select 6, 'TEST 6', 'ABC', 3 from dual union all
  8                     select 7, 'TEST 7', 'ABC', 4 from dual union all
  9                     select 8, 'TEST 8', 'ABC', 5 from dual
 10                    )
 11  --
 12  -- end of test data
 13  --
 14      ,r as (select &starting_num as start_num from dual)
 15      ,rec(n) as
 16            (select a_number as n
 17             from my_table_name, r
 18             where primary_number = r.start_num
 19             union all
 20             select a_number as n
 21             from my_table_name, rec
 22             where primary_number = rec.n
 23            )
 24  select *
 25* from   rec
SQL> /
Enter value for starting_num: 2
old  14:     ,r as (select &starting_num as start_num from dual)
new  14:     ,r as (select 2 as start_num from dual)
         N
----------
         4
         5
         7
         8

Which is using recursion techniques rather than hierarchical techniquest to achieve the same.