Oracle bug/feature: global variable used as IN parameter, value of the IN parameter changes

[Claus Pedersen]

Hello all,

this is not related to PL/SQL Developer as such, but given the amount of collective brain power of all forum readers :grin:, I thought some of you might be able to help.

I have a database package with a package variable. I call a procedure with the value of this variable as IN parameter. When I change the value of the package variable, the value of the IN parameter changes accordingly(!).

Is this by design by Oracle? I have always trusted an IN variable not to be changed throughout the procedure call, but in some cases it apparently is not so.

The following code example illustrates the situation:

CREATE OR REPLACE PACKAGE tester IS
  PROCEDURE tester ;
END tester;
/
CREATE OR REPLACE PACKAGE BODY tester IS

  v_glob VARCHAR2(1); -- global package variable

  PROCEDURE doSomething (p_val IN VARCHAR2) IS
  BEGIN
    dbms_output.put_line ('Global in doSomething, before: ' || v_glob);
    dbms_output.put_line ('p_val in doSomething, before: ' || p_val);

    v_glob := 'Y'; -- here the global variable is changed

    dbms_output.put_line ('Global in doSomething, after: ' || v_glob);
    dbms_output.put_line ('p_val in doSomething, after: ' || p_val || ' NB NB'); -- expected X, got Y
  END doSomething;

  PROCEDURE tester IS
  BEGIN
    v_glob := 'X';

    dbms_output.put_line ('Global in tester, before: ' || v_glob);

    doSomething (p_val => v_glob);

    dbms_output.put_line ('Global in tester, after: ' || v_glob);
  END tester;

END tester;

The output when testing the procedure tester is

Global in tester, before: X
Global in doSomething, before: X
p_val in doSomething, before: X
Global in doSomething, after: Y
p_val in doSomething, after: Y NB NB
Global in tester, after: Y

In procedure "tester", the value of the global variable is set to X. Then procedure "doSomething" is called with the value of the global variable as parameter. Inside "doSomething", the value of the global variable is changed into Y. Now the value of the input parameter is also Y. It seems that instead of using a call-by-value approach, as one would normaly expect from an IN parameter, Oracle in this case uses call-by-reference. In the output line 5, I have would have expected the value X (the input value), but gets Y.

Can anybody please shine some light on this?

I was always taught that IN parameters were always copied by value onto the call stack.

OUT parameters were copied onto the call stack when the procedure finishes.

IN OUT parameters were copied into the call stack at the beginning of the procedure and the changed value was copied back onto the call stack when the procedure finishes.
IN OUT NOCOPY parameters were copied onto the call stack by reference (pointer value) and when the value of the parameter was changed inside the procedure, the actual value of the IN/OUT parameter was changed and not the copy (because it was called by reference). This also means that if a procedure fails, e.g. because of an Oracle error, the value was still changed, even when the procedure failed. This is not the case when using IN OUT parameters. If the procedure returns before the end, the changed value of the OUT parameter is never copied onto the stack and the original value is left unchanged.

 

[Benjamin L]

Hi Claus,

as i know, parameters in PLSQL are assigned in this ways:

IN call by reference
OUT call by result
IN OUT call by value and result

So you have to use "IN OUT"

 

[Claus Pedersen]

Hello Benjamin,

yes, you are right, I found the documentation at Oracle (for version 9): Summary of Subprogram Parameter Modes
Reading manuals is always the last thing you do, even writing long forum topics like this comes into mind first :/