Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question.

Asked: October 11, 2002 - 7:52 pm UTC

Answered by: Tom Kyte - Last updated: May 19, 2005 - 2:48 pm UTC

Category: Developer - Version: 920

Viewed 1000+ times

You Asked

Hi Tom

Objective
Im trying to write a procedure for truncating table or matching tables that the user pass and also truncate the child tables.

You cannot truncate a table if it has child tables referencing it. So you need to disable their FKs & then truncate them & finally Truncate the parent table. But the child tables can also be refrenced by other child tables & so on. SO we have a recursive situation here.

My procedure results in an error as follows:


Create or Replace Procedure rec(tabname in user_tables.table_name%Type, tier number:=1) is
-- Get the child constraint information
Cursor curPK(t in Varchar2) is select table_name ,constraint_name from user_constraints
where table_name =t and constraint_type='P';

CURSOR curFK(pcPKName IN VARCHAR2) IS
SELECT constraint_name, table_name FROM user_constraints WHERE r_constraint_name = pcPKName;
STMT VARCHAR2(255);
Begin

For pk in CurPK(tabname) loop
For fk in CurFK(pk.constraint_name) loop
If tier =1 Then
STMT:= 'Alter table ' || FK.table_name || ' Disable Constraint '|| FK.constraint_name ;
Execute immediate stmt;
STMT:='Truncate Table ' ||FK.table_name;
Execute immediate stmt;
End if;

For ee in CurPk(FK.table_name) loop
STMT:= 'Alter table ' || ee.table_name || ' Disable Constraint '|| ee.constraint_name ;
Execute immediate stmt;
STMT:='Truncate Table ' ||ee.table_name;
Execute immediate stmt;

REC(ee.table_name, tier+1);

End Loop;
End Loop;
End loop;
End;
/


*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at "TEST.P2", line 2
ORA-06512: at "TEST.P2", line 14
ORA-06512: at line 1


Could you please help me in figuring this out?
Thanks



and we said...

Here is the procedure I have for doing this:


ops$tkyte@ORA920.US.ORACLE.COM> create table p1 ( x int primary key );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> create table p2 ( x int primary key );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create table c1 ( x int primary key, y references p1, z references p2 );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> create table c2 ( x int primary key, a references c1, b references p1 );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> create table c3 ( x int primary key, m references c2, n references p2, o references c3 );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create table c4 ( x int primary key, y references p2 );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> create table c5 ( x int primary key, z references c4 );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> create table c6 ( x int primary key, a references c5, b references p1 );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace package util
2 as
3 procedure recursive_truncate( p_tname in varchar2, p_level in number default 1 );
4 end;
5 /

Package created.

ops$tkyte@ORA920.US.ORACLE.COM> create or replace package body util
2 as
3
4 type stack is table of varchar2(32000) index by binary_integer;
5 g_stack stack;
6 g_empty_stack stack;
7
8 procedure do_ddl( p_stmt in long, p_level in number )
9 is
10 begin
11 dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) || p_stmt );
12 execute immediate p_stmt;
13 end do_ddl;
14
15 procedure recursive_truncate( p_tname in varchar2, p_level in number default 1 )
16 as
17 begin
18 if ( p_level = 1 )
19 then
20 g_stack := g_empty_stack;
21 end if;
22
23 dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) || 'Processing Table ' || p_tname );
24 for x in ( select *
25 from user_constraints
26 where constraint_type = 'R'
27 and table_name <> p_tname
28 and r_constraint_name in ( select constraint_name
29 from user_constraints
30 where table_name = p_tname
31 and constraint_type in ( 'P', 'U' )
32 )
33 )
34 loop
35 do_ddl( 'alter table ' || x.table_name || ' disable constraint ' || x.constraint_name, p_level );
36 g_stack( g_stack.count+1 ) := 'alter table ' || x.table_name || ' enable constraint ' || x.constraint_name;
37 recursive_truncate( x.table_name, p_level+1 );
38 do_ddl( 'truncate table ' || x.table_name, p_level );
39 end loop;
40 if ( p_level = 1 )
41 then
42 for i in 1 .. g_stack.count
43 loop
44 do_ddl( g_stack(i), p_level );
45 end loop;
46 end if;
47 end recursive_truncate;
48
49 end util;
50 /

Package body created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> exec util.recursive_truncate( 'P1' );
Processing Table P1
alter table C1 disable constraint SYS_C003158
Processing Table C1
alter table C2 disable constraint SYS_C003161
Processing Table C2
alter table C3 disable constraint SYS_C003164
Processing Table C3
truncate table C3
truncate table C2
truncate table C1
alter table C2 disable constraint SYS_C003162
Processing Table C2
alter table C3 disable constraint SYS_C003164
Processing Table C3
truncate table C3
truncate table C2
alter table C6 disable constraint SYS_C003173
Processing Table C6
truncate table C6
alter table C1 enable constraint SYS_C003158
alter table C2 enable constraint SYS_C003161
alter table C3 enable constraint SYS_C003164
alter table C2 enable constraint SYS_C003162
alter table C3 enable constraint SYS_C003164
alter table C6 enable constraint SYS_C003173

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM> exec util.recursive_truncate( 'P2' );
Processing Table P2
alter table C1 disable constraint SYS_C003159
Processing Table C1
alter table C2 disable constraint SYS_C003161
Processing Table C2
alter table C3 disable constraint SYS_C003164
Processing Table C3
truncate table C3
truncate table C2
truncate table C1
alter table C3 disable constraint SYS_C003165
Processing Table C3
truncate table C3
alter table C4 disable constraint SYS_C003168
Processing Table C4
alter table C5 disable constraint SYS_C003170
Processing Table C5
alter table C6 disable constraint SYS_C003172
Processing Table C6
truncate table C6
truncate table C5
truncate table C4
alter table C1 enable constraint SYS_C003159
alter table C2 enable constraint SYS_C003161
alter table C3 enable constraint SYS_C003164
alter table C3 enable constraint SYS_C003165
alter table C4 enable constraint SYS_C003168
alter table C5 enable constraint SYS_C003170
alter table C6 enable constraint SYS_C003172

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM>

and you rated our response

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Most useful - but...

October 12, 2002 - 1:01 pm UTC

Reviewer: andrew from ca, usa

Merits of the following structure aside (although I have encountered one of these) the code can be made to break.

create table x1 (col1 number, col_fk number);
alter table x1 add constraint x1_pk primary key (col1);

create table x2 (col1 number, col_fk number);
alter table x2 add constraint x2_pk primary key (col1);

alter table x1 add constraint x1_x2_fk foreign key (col_fk)references x2(col1);
alter table x2 add constraint x2_x1_fk foreign key (col_fk)references x1(col1);

insert into x1 values (1, null);
insert into x2 values (1, null);
insert into x1 values (2, 1);
insert into x2 values (2, 1);

begin
util.recursive_truncate( 'X1' );
end;

Processing Table X1
alter table X2 disable constraint X2_X1_FK
Processing Table X2
alter table X1 disable constraint X1_X2_FK
Processing Table X1
alter table X2 disable constraint X2_X1_FK
Processing Table X2
alter table X1 disable constraint X1_X2_FK
Processing Table X1
alter table X2 disable constraint X2_X1_FK
Processing Table X2
alter table X1 disable constraint X1_X2_FK
Processing Table X1
alter table X2 disable constraint X2_X1_FK
Processing Table X2
...

ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 84
ORA-06512: at "SYS.DBMS_OUTPUT", line 58


Tom Kyte

Followup  

October 12, 2002 - 3:36 pm UTC

cool -- now, whats the fix?

(and do you really use mutually referencing tables like that -- they are quite painful to actually insert into!)

Add some code, maybe something like this?

October 12, 2002 - 1:36 pm UTC

Reviewer: Jan van Mourik from Houston, TX

package body util
as

type stack is table of varchar2(32000) index by binary_integer;
g_stack stack;
g_empty_stack stack;
type tabs is table of varchar2(30) index by binary_integer;
g_tab_to_truncate tabs;
g_empty_tabs tabs;

procedure do_ddl( p_stmt in long, p_level in number )
is
begin
dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) || p_stmt );
execute immediate p_stmt;
end do_ddl;

procedure recursive_truncate( p_tname in varchar2, p_level in number default 1 )
as
e_already_truncated exception;
begin
if ( p_level = 1 )
then
g_stack := g_empty_stack;
g_tab_to_truncate := g_empty_tabs;
end if;

dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) || 'Processing Table ' || p_tname );
g_tab_to_truncate( g_tab_to_truncate.count+1 ) := p_tname;

for x in ( select *
from user_constraints
where constraint_type = 'R'
and table_name <> p_tname
and r_constraint_name in ( select constraint_name
from user_constraints
where table_name = p_tname
and constraint_type in ('P', 'U')
)
)
loop
begin
do_ddl( 'alter table ' || x.table_name || ' disable constraint ' || x.constraint_name, p_level );
g_stack( g_stack.count+1 ) := 'alter table ' || x.table_name || ' enable constraint ' || x.constraint_name;

for i in reverse 1 .. g_tab_to_truncate.count
loop
if g_tab_to_truncate(i) = x.table_name
then
raise e_already_truncated;
end if;
end loop;

recursive_truncate( x.table_name, p_level+1 );
do_ddl( 'truncate table ' || x.table_name, p_level );
exception
when e_already_truncated
then
dbms_output.put_line('Table '|| x.table_name ||' already truncated, skipping...');
end;
end loop;
if ( p_level = 1 )
then
do_ddl( 'truncate table ' || p_tname, p_level );
for i in 1 .. g_stack.count
loop
do_ddl( g_stack(i), p_level );
end loop;
end if;
end recursive_truncate;

end util;
/

exec util2.recursive_truncate( 'X1' );

Processing Table X1
alter table X2 disable constraint X2_X1_FK
Processing Table X2
alter table X1 disable constraint X1_X2_FK
Table X1 already truncated, skipping...
truncate table X2
truncate table X1
alter table X2 enable constraint X2_X1_FK
alter table X1 enable constraint X1_X2_FK

Also added the truncate of p_tname at the end...

Tom Kyte

Followup  

October 12, 2002 - 3:37 pm UTC

that was my first thought -- look in the stack I had....

thanks

October 16, 2002 - 6:10 pm UTC

Reviewer: A reader

Hi Tom
Im in a situation where I cant seem to use packages. So Instead of using do_ddl I locally used Execute Immediate statement . But this does not let the disabled constraints to be enabled at one level deeper. i.e
If x1 <- x2
<- x3
<-x4
<-x5
<-x6
Running the following procedure would enable x2,x3,x4 but not x5 & x6. I dont get it, even if I create a separate procedure do_ddl & call it from within the following procedure it does not Enable the embedded constraints.

create table x1(id number primary key);
create table x2 (id number , constraint fk2 foreign key(id) references x1);
create table x3 (id number , constraint fk3 foreign key(id) references x1);
create table x4 (id number primary key , constraint fk4 foreign key(id) references x1);
create table x5 (id number PRIMARY KEY, constraint fk5 foreign key(id) references x4);
create table x6 (id number , constraint fk6 foreign key(id) references x5);


Create or Replace procedure recursive_truncate( p_tname in varchar2, p_level in number default 1 )
AS
type stack is table of varchar2(32000) index by binary_integer;
g_stack stack;
g_empty_stack stack;
type tabs is table of varchar2(30) index by binary_integer;
g_tab_to_truncate tabs;
g_empty_tabs tabs;
e_already_truncated exception;
stmt LONG;
begin

if ( p_level = 1 )
then
g_stack := g_empty_stack;
g_tab_to_truncate := g_empty_tabs;
end if;
--Dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) || 'Processing Table ' || p_tname );
g_tab_to_truncate( g_tab_to_truncate.count+1 ) := p_tname;
for x in ( select *
from user_constraints
where constraint_type = 'R'
and table_name <> p_tname
and r_constraint_name in ( select constraint_name
from user_constraints
where table_name = p_tname
and constraint_type in ('P', 'U'))
)
Loop
Begin
--do_ddl( 'alter table ' || x.table_name || ' disable constraint ' || x.constraint_name, p_level );
stmt:='alter table ' || x.table_name || ' disable constraint ' || x.constraint_name ;
execute immediate stmt;
--Dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) || stmt );
g_stack( g_stack.count+1 ) := 'alter table ' || x.table_name || ' enable constraint ' || x.constraint_name;
for i in reverse 1 .. g_tab_to_truncate.count
loop
if g_tab_to_truncate(i) = x.table_name
then
raise e_already_truncated;
end if;
end loop;
recursive_truncate( x.table_name, p_level+1 );
-- do_ddl( 'truncate table ' || x.table_name, p_level );
stmt:='truncate table ' || x.table_name;
execute immediate stmt;
Dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) ||stmt);
exception
when e_already_truncated
then
dbms_output.put_line('Table '|| x.table_name ||' already truncated, skipping...');

End;
end loop;
if ( p_level = 1 )
then
-- do_ddl( 'truncate table ' || p_tname, p_level );
stmt:= 'truncate table ' || p_tname ;
execute immediate stmt;
Dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) ||stmt);
for i in 1 .. g_stack.count
loop
-- do_ddl( g_stack(i), p_level );
stmt:= g_stack(i);
execute immediate stmt;
Dbms_output.put_line( g_stack(i));
--Dbms_output.put_line( rpad(chr(9), p_level-1, chr(9)) ||stmt);
end loop;
end if;
end recursive_truncate;
/

Thanks

Tom Kyte

Followup  

October 16, 2002 - 6:52 pm UTC

why cannot you use packages?

lets fix the PROBLEM here.

October 16, 2002 - 7:13 pm UTC

Reviewer: A reader

In our application due to some legacy code practices we can only have stored procedures & not packages. Currently our application code that is dependent on the database has to be in the form of SPs. Believe me no amt of arguing with my bosses is of any use.

I am very curious of Oracle's behavior regarding this issue . Is it related to locally defined variable "stmt" that is dynamically executed?

Appreciate the response.

Tom Kyte

Followup  

October 16, 2002 - 7:32 pm UTC

Well, sorry -- i cannot compete with what I will call "ignorance"

write a package -- create a standalone procedure that calls the package and makes it look like a standalone procedure. Dont' tell your managers you wrote a "bad old package"

I'm not even going to look at making this code a single procedure. (ok, so I peeked -- the problem is really quite simple -- you need a GLOBAL STACK, you have a LOCAL STACK, the stuff you push in the recursive calls all "disappears" as you unwind out of the recursion. You'll have to totally redo the logical to remove recursion.

FUNNY your managers let you do recursion, which many people out law, but won't let you use packages which only make things *better* -- never worse.... sigh, sigh, double sigh)

Because we did it in the past isn't any reason to keep propagating bad practices into the future. I firmly believe that ALL real production code MUST be in a package -- there should be NO standalone procedures or functions beyond simple utilities.




Why is not using bind variable faster sometimes?

March 27, 2003 - 8:52 am UTC

Reviewer: Jacques from UK

Tom,
Dont know if I understand Bind variables correctly but I tried the following test and it seems that not using bind variables works faster 2 out of 3 times;

Am I missing the point - or doing something wrong?

SQL>Create or replace Procedure P4
  2  as
  3  begin
  4  for i in 1 .. 1000
  5  loop
  6  execute immediate
  7  'insert into t (x,y,z) values (:x,:y,:z)'
  8  using i, sysdate+i, 'this is row ' || i;
  9  end loop;
 10* end;
 11  /

Procedure created.

Elapsed: 00:00:00.11
SQL> create or replace procedure NO_BINDS
  2  as
  3  begin
  4  for i in 1..1000
  5  loop
  6  insert into t(x,y,z) values(i, sysdate + i, 'test '||i);
  7  end loop;
  8  end;
  9  /

Procedure created.

Elapsed: 00:00:00.71
SQL> truncate table t;

Table truncated.

Elapsed: 00:00:00.50
SQL> exec p4;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.81
SQL> truncate table t;

Table truncated.

Elapsed: 00:00:00.00
SQL> exec NO_BINDS;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.90
SQL> truncate table t;

Table truncated.

Elapsed: 00:00:00.61
SQL> exec p4;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.82
SQL> truncate table t;

Table truncated.

Elapsed: 00:00:00.11
SQL> exec NO_BINDS;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.41
SQL> truncate table t;

Table truncated.

Elapsed: 00:00:00.50
SQL> exec p4;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.82
SQL> truncate table t;

Table truncated.

Elapsed: 00:00:00.51
SQL> exec NO_BINDS;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.81
SQL>  

Tom Kyte

Followup  

March 27, 2003 - 10:16 am UTC

You were comparing

P4 -- dynamic sql using bind variables

with

NO_BINDS -- static sql That most certainly did use bind variables

static sql is more efficient then dynamic sql. You would expect the second routine to be more performant.


In this:

insert into t(x,y,z) values(i, sysdate + i, 'test '||i);

it was really:

insert into t(x,y,z) values( :i, sysdate + :i, 'test '|| :i );


All references to plsql variables in SQL in plsql are in fact bind variables.

That is the sheer beauty of doing it in plsql -- it makes it so hard to NOT use bind variables!!!




Truncate tables in 24x7 system

October 15, 2004 - 2:25 pm UTC

Reviewer: David Pujol from Catalonia, Europe

Hi Tom,

I want truncate tables but I cannot do it because there are a lot of processes inserting data in these tables (I use it for save query results) . Since now, I stoped pass to processes and then I trucated these tables. I cannot use temporary tables because same user (IAS) uses distinct database sessions.

How can I truncate these tables without stop insert processing?, can I use partitioning?, and if I use partitioning (range 1 hour for example), If I execute "alter table xxx drop partition" statement, will have problems with object invalidations?. How can I implement it?

A lot of thanks in advance!

Tom Kyte

Followup  

October 15, 2004 - 5:49 pm UTC

why are you saving query results? On my systems, i just run queries, the only thing I remember is "what page you are on"

Might that be a better solution?

sure, you can use partitioning -- that'll work -- it'll invalidate cursors (the structure changes) but that should be it with a truncate. BUT do your queries all have "where this_magic_partition_key = :x"? if not, they will tend to want to get at this data you are trying to truncate too.

What is wrong with a delete everynow and again -- and a periodic "online redefine"? (if you are using 8i, perhaps this table should be an IOT, you can rebuild that online as well)

Restricting Truncate table

December 20, 2004 - 1:20 am UTC

Reviewer: Farrukh Mahmood

Tom,

Is it possible to restrict any oracle user for truncating his table? I mean an owner cannot truncate his table accidentaly or forcibly. If i am Oracle DBA and i want my development team cannot truncate any of the tables in their respective schemas. One way round is that i will create all the tables in some other Oracle user and give my development team access to those tables via synonyms and only allow them to do insert/update/delete/select on these synonyms. In this way i can prohibit truncate table.

Any direct way to restrict truncate table?

Tom Kyte

Followup  

December 20, 2004 - 8:14 am UTC

why stop at truncate?

what about delete?
what about drop?
what update update? (i mean, if I update a column to null, i might as well have just deleted that column)


You can write DDL event triggers "before <ddl operation>" to fail the operation if you like.

Recursive truncate

February 22, 2005 - 10:28 pm UTC

Reviewer: Spawn

Hi Tom. I tried your package. How come this is what happens
when I execute it.

SQL> execute util.recursive_truncate('CS_OR_LOG');

PL/SQL procedure successfully completed.

SQL> @count
Enter value for table: CS_OR_LOG
old   1: SELECT COUNT(*) FROM &TABLE
new   1: SELECT COUNT(*) FROM CS_OR_LOG

  COUNT(*)
----------
    693529

I'm just wondering why it didnt output any message and why it didnt work for me. Any parameters I need to enable?
Thanks. 

Tom Kyte

Followup  

February 23, 2005 - 2:03 am UTC

SQL> set serveroutput on size 1000000

and looks like there is a missing truncate for the parent that needs be added after line 39



 

Recursive Truncate

February 23, 2005 - 10:11 pm UTC

Reviewer: Spawn

Thanks Tom. I added the truncate for the parent and set serveroutput. It's working great.

Enable Constraint without check

May 19, 2005 - 2:29 pm UTC

Reviewer: Anil Babu. K from INDIA

How I can enable a constraint without the checks for its vialotion?

If i use the sql command

alter table EES_LES_AAREPR enable constraint SYS_C0016282;

It will check for each recod. I want to bypass it. Is there any option in Oracle 8i to do it. In MS-SQL we can use the key work "nocheck", then it won't check the validation.

Tom Kyte

Followup  

May 19, 2005 - 2:48 pm UTC

ops$tkyte@ORA9IR2> create table t ( x int constraint t_check check(x>0) );
 
Table created.
 
ops$tkyte@ORA9IR2> alter table t disable constraint t_check;
 
Table altered.
 
ops$tkyte@ORA9IR2> insert into t values ( -1 );
 
1 row created.
 
ops$tkyte@ORA9IR2> alter table t enable novalidate constraint t_check ;
 
Table altered.
 
ops$tkyte@ORA9IR2> insert into t values ( -1 );
insert into t values ( -1 )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.T_CHECK) violated
 
 
ops$tkyte@ORA9IR2> select * from t;
 
         X
----------
        -1
 

Drill-Down Report

March 28, 2008 - 5:05 am UTC

Reviewer: Kumar from Bangalore, India

Hi Tom,

I was given a problem to resolve. A table has 2 columns CUSTID and CUSTACC. I need to give the complete drill-down result. The input would be custid.

I will try to explain the problem:

Say the table is like this:
CUSTID CUSTACC

------------------------------ ------------------------------

Robert 4725
Robert 5425
Bill 4725
Bill 1111
James 1111
James 3465
Eddy 4725
Alan 5555


Say for eg., My input is 'Robert' then I should get all the below records:

1st LEVEL - This set is because SELECT custid, custacc
FROM CUST
WHERE custacc IN (SELECT custacc
FROM cust
WHERE custid = 'Robert')

---------------------------------------------------------------------------------------------------
Robert 4725
Robert 5425
Bill 4725
Eddy 4725

2nd LEVEL - I take 'Bill' now from 1st level
SELECT custid, custacc
FROM CUST
WHERE custacc IN (SELECT custacc
FROM cust
WHERE custid = 'Bill')
----------------------------------------------------------------------------------------------------
Bill 4725
Bill 1111
James 1111

2nd LEVEL - I take 'Eddy' now from 1st level
SELECT custid, custacc
FROM CUST
WHERE custacc IN (SELECT custacc
FROM cust
WHERE custid = 'Eddy')
----------------------------------------------------------------------------------------------------
Eddy 4725

3rd LEVEL - I take 'James' from 2nd Level
SELECT custid, custacc
FROM CUST
WHERE custacc IN (SELECT custacc
FROM cust
WHERE custid = 'James')
----------------------------------------------------------------------------------------------------

James 1111
James 3465


My result should be the distinct values of all the above as shown below:

Robert 4725
Robert 5425
Bill 4725
Eddy 4725
Bill 1111
James 1111
James 3465



I tried recursion like below but was not successful.

create or replace PROCEDURE CUST_CHAIN(p_custid IN VARCHAR2, cv_1 IN OUT SYS_REFCURSOR) AS
TYPE pls_num_type IS TABLE OF NUMBER;
TYPE pls_vchr_type IS TABLE OF VARCHAR2(100);
Cust_collection CustTableType := CustTableType() ;
cnt NUMBER := 0;
arr_custid pls_vchr_type;
arr_custacc pls_num_type;
v_custid cust.custid%TYPE := p_custid;
BEGIN
IF p_custid IS NULL OR Cust_collection.COUNT = 20
THEN
OPEN cv_1 FOR
SELECT *
FROM TABLE(CAST( Cust_collection AS CustTableType));
ELSE
SELECT custid, custacc BULK COLLECT INTO arr_custid, arr_custacc
FROM CUST
WHERE custacc IN (SELECT custacc
FROM cust
WHERE UPPER(custid) = UPPER(v_custid);

FOR i IN 1..arr_custid.COUNT
LOOP
Cust_collection.EXTEND;
Cust_collection(I) := CustRecType(arr_custid(i),arr_custacc(i));
IF UPPER(arr_custid(i)) <> UPPER(v_custid)
THEN
CUST_CHAIN(arr_custid(i),cv_1);
END IF;
END LOOP;
END IF;
END CUST_CHAIN;

IS there a best way to achive this?

Use the below stmt:

Create table cust
(custid varchar2(100),
custacc number);

insert into cust values ('ROBERT', 4725);
insert into cust values ('ROBERT', 5425);
insert into cust values ('BILL', 4725);
insert into cust values ('BILL', 1111);
insert into cust values ('JAMES', 1111);
insert into cust values ('EDDY', 4725);
insert into cust values ('ALAN', 5555);

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here