Table - CONFI				Table - DEPT				Table - PER_DEPT	
											
ID 	Name	Dept_ID			Dept_ID	Desc				Dept_ID	Level
1	Tom	100			100	abc				100	3
2	Rick	100			101	def				100	4
3	Mary	101			102	acd				100	5
4	Cathy	101								101	1
5	Sarah	102								101	2
										102	6
										102	7

Table - CONFI				Table - DEPT				Table - PER_DEPT	
											
ID 	Name	Dept_ID			Dept_ID	Desc				Dept_ID	Level
1	Tom	100			100	abc				100	3
2	Rick	103			101	def				100	4
3	Mary	101			102	acd				100	5
4	Cathy	104			103	abc				101	1
5	Sarah	102			104	def				101	2
										102	6
										102	7
										103	3
										103	4
										103	5
										104	1
										104	2

create table dept_id_repeats(dept_id number(5), counts number(5))
as 
(select dept_id, count(*) as Counts
from CONFI
group by dept_id having count(*) > 1
order by dept_id)

Dept_id	Counts
100	2
101	2
102	1

create or replace procedure UPDATE_CONF
is
cursor c1 is select counts from dept_id_repeats;
begin
n number:=100
if n=100
if c1.counts>1

insert into dept (dept_id,desc)
values (dept_seq.nextval, (select desc from dept where dept_id=n));

insert into per_dept(dept_id,level)
values(dept_seq.currval, (select level from per_dept where dept_id=n) -- > these will be multiple values and I dont really know how I can insert them)

end if;
n=n+1;
end;  

end;  ----end procedure

Dept_id	Counts
100	2
101	2

create or replace procedure UPDATE_CONFI_TABLE
IS
cursor c1 is
SELECT name,dept_id,
ROW_NUMBER( ) OVER (PARTITION BY
dept_id ORDER BY dept_id
NULLS LAST) SRLNO
FROM confi;
dept1_rec confi%rowtype;
cursor c2 is 
select * from dept;
dept2_rec dept%rowtype;
a number:=100
begin
open c1,c2;
loop
fetch c1 into dept1_rec;
fetch c2 into dept2_rec;
    loop
    if dept1_rec.dept_id=a and tid1_rec.srlno=2
    then
    
    insert into dept
    select dept_seq.nextval,dept2_rec.desc
    from dept where dept_id=&&a;

    update confi set dept_id=dept_seq.currval 
    where dept_id=&&a and dept1_rec.srlno=2;

    Insert INTO per_dept
    select dept_seq.currval,level
    from per_dept where dept_id=&&a;

    end if;
    end loop;
a=a+1;
end loop;
close c1,c2;
end;
/