PL/SQL Parameter Value Being Set To Null Or Corrupted (Bug 12670034) (Doc ID 1342813.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

A formal parameter aliases a global variable can see changes in the global variable even if the formal parameter is of mode IN.

1) Record Type
create or replace PROCEDURE short IS
type The_Record is record(The_Attr1 number);
type The_Array is table of The_Record index by binary_integer;
Global_Array The_Array;

procedure Primary(arg1 IN number) is
begin
dbms_output.put_line('Before Initializer, arg1: ' || arg1);
Global_Array(1).The_Attr1 := 1000; -- basically, some other value
dbms_output.put_line('After Initializer, arg1: ' || arg1);
end;

BEGIN
Global_Array(1).The_Attr1 := 2;
Primary(Global_Array(1).The_Attr1);
dbms_output.put_line('After Call : ' || Global_Array(1).The_Attr1);
END;
/

SQL> set serveroutput on
SQL> exec short();
Before Initializer, arg1: 2
After Initializer, arg1: 1000
After Call : 1000

2) Scalar Variable

create or replace PROCEDURE short1 IS
globval number;
vval number;
procedure Primary(arg1 IN number) is
begin
dbms_output.put_line('Before Initializer, arg1: ' || arg1);
globval := 1000; -- basically, some other value
dbms_output.put_line('After Initializer, arg1: ' || arg1);
end;

BEGIN
globval := 2;
Primary(globval);
dbms_output.put_line('After Call : ' || globval);
END;
/

SQL> exec short1();
Before Initializer, arg1: 2
After Initializer, arg1: 1000
After Call : 1000

PL/SQL procedure successfully completed.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms