Variable scope in plsql package
new2sqlJun 2 2008 — edited Jun 2 2008When 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.