Hi,
I encounter a problem while inserting data into a table.
Providing sample data for testing -
create table sub_svc (sub_svc_id varchar2(20), parent_sub_svc_id varchar2(20), svc_id varchar2(20));
create table sub_addr(sub_svc_id varchar2(20), sub_addr_id varchar(20));
alter table sub_addr add constraint sub_svc_addr_pk primary key (sub_svc_id, sub_addr_id);
insert into sub_svc values (110806, 22167, 113);
insert into sub_svc values (110807, 110806, 213);
insert into sub_svc values (143995, 110806, 213);
insert into sub_svc values (110808, 110806, 179);
using sub_svc table i need to populate the sub_addr table.
I will be provided with a sub_svc_id. This sub_svc_id can have child records
like here i'll be provided with a sub_svc_id = 110806
using this sub_svc_id i need to fetch the sub_svc_id for parent-svc and its child svc
this is for parent-svc -
select sub_svc_id from sub_svc where sub_svc_id = 110806;
for child svc
select sub_svc_id from sub_svc where parent_sub_svc_id = 110806;
after fetching these sub_svc_id i need to insert these ids into sub_addr table along with new generated(using sequence or other method) sub_addr_id which has to be same for all 4 sub_svc_id's we fetch here (in this case these are 4 there could less or more for other sub_svc_id which is provided as input).
please ask if some more information is required.
I have created this block for the requirement -
declare
cursor c_chld_sub is select sub_svc_id from sub_svc where PARENT_SUB_SVC_ID = 110806;
cursor c_prnt_sub is select sub_svc_id from sub_svc where sub_svc_id = 110806;
l_sub_addr_id number;
l_sub_svc_chld number;
l_sub_svc_prnt number;
begin
select sub_addr_seq_tmp.nextval into l_sub_addr_id from dual;
for l_prnt_rec in c_prnt_sub
loop
dbms_output.put_line('parent svc'||' '||l_prnt_rec.sub_svc_id);
l_sub_svc_prnt := l_prnt_rec.sub_svc_id;
insert into sub_svc_addr_tmp values (l_prnt_rec.sub_svc_id, l_sub_addr_id);
exit when c_prnt_sub%notfound;
end loop;
for l_chld_rec in c_chld_sub
loop
dbms_output.put_line('child svc'||' '||l_chld_rec.sub_svc_id);
l_sub_svc_chld := l_chld_rec.sub_svc_id;
insert into sub_svc_addr_tmp values (l_sub_svc_chld, l_sub_addr_id);
exit when c_chld_sub%notfound;
end loop;
end;
I am using -
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production