SQL> select id,sal,status
2 from test;

ID SAL S
---------- ---------- -
1 3000
2 4000
3 3500
4 4300

SQL> create or replace package pkg_test is
2
3 procedure p1 (p_id in test.id%TYPE);
4 procedure p2 (p_id in test.id%TYPE);
5
6 end pkg_test;
7 /

Package created.

SQL> create or replace package body pkg_test is
2
3 cs_status char(1) := 'N';
4
5 procedure p1 (p_id in test.id%TYPE)
6 is
7 lv_sal test.sal%TYPE;
8 begin
9
10 select sal
11 into lv_sal
12 from test
13 where id = p_id;
14
15 if lv_sal>3800 then
16 cs_status := 'Y';
17 end if;
18
19 pkg_test.p2(p_id);
20
21 exception
22 when others then
23 return;
24 end p1;
25
26 procedure p2 (p_id in test.id%TYPE)
27 is
28 begin
29
30 update test
31 set status = cs_status
32 where id = p_id;
33
34 commit;
35
36 exception
37 when others then
38 rollback;
39 end p2;
40 end pkg_test;
41 /

Package body created.

SQL> select id,sal,status
2 from test;

ID SAL S
---------- ---------- -
1 3000
2 4000
3 3500
4 4300

SQL> exec pkg_test.p1(1);

PL/SQL procedure successfully completed.

SQL> select id,sal,status
2 from test;

ID SAL S
---------- ---------- -
1 3000 N
2 4000
3 3500
4 4300

SQL> exec pkg_test.p1(2);

PL/SQL procedure successfully completed.

SQL> select id,sal,status
2 from test;

ID SAL S
---------- ---------- -
1 3000 N
2 4000 Y
3 3500
4 4300

SQL> exec pkg_test.p1(3);

PL/SQL procedure successfully completed.

SQL> select id,sal,status
2 from test;

ID SAL S
---------- ---------- -
1 3000 N
2 4000 Y
3 3500 Y
4 4300

SQL>

**********************************************

SQL> create or replace package body pkg_test is
2
3 cs_status char(1) := 'N';
4
5 procedure p1 (p_id in test.id%TYPE)
6 is
7 lv_sal test.sal%TYPE;
8 begin
9
10 select sal
11 into lv_sal
12 from test
13 where id = p_id;
14
15 if lv_sal>3800 then
16 cs_status := 'Y';
17 end if;
18
19 pkg_test.p2(p_id);
20
21 exception
22 when others then
23 return;
24 end p1;
25
26 procedure p2 (p_id in test.id%TYPE)
27 is
28 begin
29
30 update test
31 set status = cs_status
32 where id = p_id;
33
34 commit;
35 cs_status := 'N'; --reset variable
36
37 exception
38 when others then
39 rollback;
40 end p2;
41 end pkg_test;
42 /

Package body created.

SQL> update test set status=null;

4 rows updated.

SQL> commit;

Commit complete.

SQL> select * from test;

ID SAL S
---------- ---------- -
1 3000
2 4000
3 3500
4 4300

SQL> exec pkg_test.p1(1);

PL/SQL procedure successfully completed.

SQL> select * from test;

ID SAL S
---------- ---------- -
1 3000 N
2 4000
3 3500
4 4300
SQL> exec pkg_test.p1(2);

PL/SQL procedure successfully completed.

SQL> select * from test;

ID SAL S
---------- ---------- -
1 3000 N
2 4000 Y
3 3500
4 4300

SQL> exec pkg_test.p1(3);

PL/SQL procedure successfully completed.

SQL> select * from test;

ID SAL S
---------- ---------- -
1 3000 N
2 4000 Y
3 3500 N
4 4300