I am trying to use a simple test case to depict my problem. The problem is value of an IN parameter can be changed (although theoretically it is “read only” within the scope). The problem has not detected during the unit testing phase and now I have to provide some suitable explanation.
I know what has happened, but looking for some more conceptual and concrete manner to articulate the same.
I am not very good at English, So trying to show the problem in technical manner only. I think the problem has to do with “pass by reference” nature of the IN parameter.
The PACKAGE SPECIFICATION:
create or replace package pack_515
as
--One Global varriable, latter some logic is used to populate this.
vg1 varchar2(1);
--One procedure with one IN parameter.
--This particular IN parameter is used latter to call some other packaged procedure also.
procedure p1(
par1 in varchar2);
end pack_515;
The PACKAGE BODY:
create or replace package body pack_515
as
procedure p1(
par1 in varchar2)
as
begin
-- We are priting the IN parameter.
dbms_output.put_line('Our IN parameter par1: '|| par1);
--After some calculation, we are assigning some value to the
-- global varriable.
pack_515.vg1 :='B';
-- We are priting again the IN parameter.
-- But this should not change, Read Only!!
dbms_output.put_line('Our IN parameter par1, second time: '||par1);
--Here this PAR1 is passed to some other packaged procedure.
-- So the value is important.
end;
end;
SCOTT@orclSB 31-JAN-17> --my database version.
SCOTT@orclSB 31-JAN-17> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SCOTT@orclSB 31-JAN-17> set serverout on
SCOTT@orclSB 31-JAN-17> ed
Wrote file afiedt.buf
1 --You can see the IN parameter actually
2 --converted from A to B!
3 -- This is done during Unit Testing.
4 begin
5 pack_515.vg1 :='A';
6 --This is passed as IN parameter!!
7 pack_515.p1(pack_515.vg1);
8* end;
SCOTT@orclSB 31-JAN-17> /
Our IN parameter par1: A
Our IN parameter par1, second time: B
PL/SQL procedure successfully completed.
SCOTT@orclSB 31-JAN-17> ed
Wrote file afiedt.buf
1 begin
2 pack_515.vg1 :='A';
3 --Here the same value A has passed
4 -- But in a different way from fusion middleware during ST!
5 pack_515.p1('A');
6* end;
SCOTT@orclSB 31-JAN-17> /
Our IN parameter par1: A
Our IN parameter par1, second time: A
PL/SQL procedure successfully completed.
SCOTT@orclSB 31-JAN-17>