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!

Variable scope in plsql package

new2sqlJun 2 2008 — edited Jun 2 2008
When I run the following package, proc2 is always printing the value of i = 1 even though proc1 is incrementing the value of i correctly? Could any one explain to me what is the problem with this code?

CREATE OR REPLACE PACKAGE test_pkg
is
PROCEDURE proc1 (p_fetch_limit in number := 200);
PROCEDURE proc2 (rec_id out number);
END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg
IS
cursor test_cur
IS
select new_app,
card_number,
process_flag,
process_date,
seq_number
from wm_opt_scan_temp
where process_flag = 'N' and process_date IS null
and new_app = 'Y'
order by seq_number;

type test_cur_type IS table of test_cur%rowtype;
cur_rec test_cur_type;

i number := 1;
l_rec_id number := 0;
-----------
-- PROC1 --
-----------
PROCEDURE proc1 (p_fetch_limit in number := 200)
IS

BEGIN
open test_cur;
loop
fetch test_cur bulk collect into cur_rec limit p_fetch_limit;
exit WHEN cur_rec.count = 0;

for i in 1..cur_rec.count
loop
DBMS_OUTPUT.put_line('proc1 - i<'||i||'> seq#<'||cur_rec(i).seq_number||'> card#<'||cur_rec(i).card_number||'>');
l_rec_id := 0;
proc2(l_rec_id);
END loop;
END loop;
CLOSE test_cur;
COMMIT;
DBMS_OUTPUT.put_line('proc1 procedure finished...');
END proc1;
-----------
-- PROC2 --
-----------
PROCEDURE proc2 (rec_id out number)
IS

BEGIN
DBMS_OUTPUT.put_line('proc2 started - i<'||i||'> seq#<'||cur_rec(i).seq_number||'> card#<'||cur_rec(i).card_number||'>');
END proc2;

END test_pkg;
/

output is:
Connecting to the database Test.
proc1 - i<1> seq#<7841> card#<40992814376>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<2> seq#<8041> card#<40992779256>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<3> seq#<8241> card#<40992745696>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<4> seq#<12681> card#<40992814376>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<5> seq#<12682> card#<40992814375>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<6> seq#<12683> card#<40992814378>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<7> seq#<12684> card#<40992814379>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<8> seq#<12685> card#<40992745756>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<9> seq#<12686> card#<40992745757>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<10> seq#<12689> card#<40992814377>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<11> seq#<12690> card#<40992745755>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<12> seq#<12691> card#<40992745767>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<13> seq#<12692> card#<40992745771>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<14> seq#<12693> card#<40992745612>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<15> seq#<12694> card#<40992145673>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<16> seq#<12695> card#<40992745611>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<17> seq#<12697> card#<40992745661>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<18> seq#<12698> card#<40992745689>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 - i<19> seq#<12700> card#<40992745771>
proc2 started - i<1> seq#<7841> card#<40992814376>
proc1 procedure finished...
Process exited.
Disconnecting from the database Test.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 30 2008
Added on Jun 2 2008
4 comments
734 views