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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

NESTED FOR LOOP WITH PARAMATERIZED CURSORS

AB115Jun 1 2019 — edited Jun 4 2019

Hello Experts,

we have a requirement as mentioned below,

Declare

cursor c1 is

select job_id,

job_name

from table1;

cursor c2(i_job_id number)

is

select order_no,

order_item

from table2

where job_number = i_job_id  ;

cursor c3(i_ord_no number)

is

select end_ord_no,

end_ord_item

from table3

where order_number = i_ord_no;

v_flag varchar2(1) := 'N';

v_flag1 varchar2(1) := 'N';

v_ord_flag varchar2(1) := 'N';

begin

if v_flag = v_flag1

then

v_ord_flag := 'Y'

else

v_ord_flag := 'N'

end if;

for c1_rec in c1

loop

...

..

for c2_rec in c2(c1_rec.job_id)

loop

...

for c3_rec in c3 (c2_rec.order_no)

loop

if v_ord_flag = 'N' then

...

...

insert into temp_data

values

();

elsif v_ord_flag = 'Y' then

..

..

insert into temp_data

values

();

end if;

end loop;

end loop;

end loop;

commit;

end;

this script is inserting the data when v_ord_flag = 'Y' but when v_ord_flag = 'N' occurs, it's not inserting the data.

please suggest, how to proceed further?

thanks

Comments
Post Details
Added on Jun 1 2019
14 comments
1,241 views