Posted by Here’s a simple little PL/SQL block where we call an inner procedure PARAMETER_TESTER from its parent block. Pay particular attention to the parameter we pass to the procedure, and it’s value throughout the execution of that procedure.
SQL> set serverout on
SQL> declare
2
3 glob_var int := 0;
4 local_var int;
5
6 procedure PARAMETER_TESTER(param int) is
7 begin
8 dbms_output.put_line('Param came in as: '||param);
9 glob_var := glob_var + 1;
10 dbms_output.put_line('Param left as : '||param);
11 dbms_output.put_line('glob_var is now : '||glob_var);
12 end;
13
14 begin
15 parameter_tester(100);
16 end;
17 /
Param came in as: 100
Param left as : 100
glob_var is now : 1
PL/SQL procedure successfully completed.
Now I’ll slowly extend the code, and just by eyeballing it, see if you can predict what the output will be before looking past the end of the PL/SQL block.
SQL> set serverout on
SQL> declare
2
3 glob_var int := 0;
4 local_var int;
5
6 procedure PARAMETER_TESTER(param int) is
7 begin
8 dbms_output.put_line('Param came in as: '||param);
9 glob_var := glob_var + 1;
10 dbms_output.put_line('Param left as : '||param);
11 dbms_output.put_line('glob_var is now : '||glob_var);
12 end;
13
14 begin
15 local_var := glob_var;
16 parameter_tester(local_var);
17 end;
18 /
Param came in as: 0
Param left as : 0
glob_var is now : 1
PL/SQL procedure successfully completed.
So far so good I imagine. The parameter came in as zero, we incremented the global variable which of course had no impact on the parameter. Let’s now up the ante a little.
SQL> set serverout on
SQL> declare
2
3 glob_var int := 0;
4 local_var int;
5
6 procedure PARAMETER_TESTER(param int) is
7 begin
8 dbms_output.put_line('Param came in as: '||param);
9 glob_var := glob_var + 1;
10 dbms_output.put_line('Param left as : '||param);
11 dbms_output.put_line('glob_var is now : '||glob_var);
12 end;
13
14 begin
15 parameter_tester(glob_var);
16 end;
17 /
Param came in as: 0
Param left as : 1
glob_var is now : 1
PL/SQL procedure successfully completed.
This is perhaps the first one that you might find a little unexpected. Notice that the value of the parameter passed to the procedure has changed within the inner procedure even though it was passed (implicitly) as an IN parameter. People often assume that if you pass anything to a procedure without the IN OUT or OUT specification, then the parameter is “read only” and cannot be touched by code. This is true to the extent that you cannot perform an assignment to that parameter as you can see below
SQL> set serverout on
SQL> declare
2
3 glob_var int := 0;
4 local_var int;
5
6 procedure PARAMETER_TESTER(param int) is
7 begin
8 param := param + 1;
9 end;
10
11 begin
12 parameter_tester(glob_var);
13 end;
14 /
param := param + 1;
*
ERROR at line 8:
ORA-06550: line 8, column 8:
PLS-00363: expression 'PARAM' cannot be used as an assignment target
ORA-06550: line 8, column 8:
PL/SQL: Statement ignored
but that is not the same as saying that the parameter is fixed in value throughout the duration of the call. This behaviour is documented in the PL/SQL language manual in that an IN parameter can be passed by reference rather than a static value.
Now I’ll explore some other examples of how you might get caught out by this. I’ll modify the example just slightly now so that I’m passing an expression rather than just “glob_var”.
SQL> set serverout on
SQL> declare
2
3 glob_var int := 0;
4 local_var int;
5
6 procedure PARAMETER_TESTER(param int) is
7 begin
8 dbms_output.put_line('Param came in as: '||param);
9 glob_var := glob_var + 1;
10 dbms_output.put_line('Param left as : '||param);
11 dbms_output.put_line('glob_var is now : '||glob_var);
12 end;
13
14 begin
15 parameter_tester(glob_var+1);
16 end;
17 /
Param came in as: 1
Param left as : 1
glob_var is now : 1
PL/SQL procedure successfully completed.
Now “normal” service has been resumed, in that the expression is evaluated first and hence is passed by value to the procedure leaving the parameter value unchanged throughout the procedure.
So it would appear an expression will disable the “pass by reference” mechanism? Well, let’s try two more examples
SQL> set serverout on
SQL> declare
2
3 glob_var int := 0;
4 local_var int;
5
6 procedure PARAMETER_TESTER(param int) is
7 begin
8 dbms_output.put_line('Param came in as: '||param);
9 glob_var := glob_var + 1;
10 dbms_output.put_line('Param left as : '||param);
11 dbms_output.put_line('glob_var is now : '||glob_var);
12 end;
13
14 begin
15 parameter_tester(to_char(glob_var));
16 end;
17 /
Param came in as: 0
Param left as : 0
glob_var is now : 1
PL/SQL procedure successfully completed.
SQL>
SQL> set serverout on
SQL> declare
2
3 glob_var int := 0;
4 local_var int;
5
6 procedure PARAMETER_TESTER(param int) is
7 begin
8 dbms_output.put_line('Param came in as: '||param);
9 glob_var := glob_var + 1;
10 dbms_output.put_line('Param left as : '||param);
11 dbms_output.put_line('glob_var is now : '||glob_var);
12 end;
13
14 begin
15 parameter_tester(to_number(glob_var));
16 end;
17 /
Param came in as: 0
Param left as : 1
glob_var is now : 1
PL/SQL procedure successfully completed.
Notice the last one in particular. Even though I had a TO_NUMBER expression around “glob_var”, it was still passed by reference and hence the parameter value changed throughout the execution of the inner procedure. This is because the PL/SQL compiler detected that the the TO_NUMBER function was redundant and optimized it out during the compilation process. That left just a parameter input of “glob_var” which could then be passed by reference.
Bottom line – it’s generally considered poor programming practice in any language to be mix and matching the scope of variables, in this case, using glob_var both within and outside the inner procedure. Trying to subvert the behaviour of the PL/SQL engine by using expressions such as to_char(glob_var) is a recipe for disaster. Who knows what additional optimizations the next release of the PL/SQL compiler will have? Perhaps it will optimize out “glob_val+1” or to_char(glob_var) and hence pass them by reference etc. If your functionality really demands on coding with these blurred scopes, then make sure you perform an assignment to a local variable and pass that to avoid unexpected side-effects.
SQL> set serverout on
SQL> declare
2
3 glob_var int := 0;
4 local_var int;
5
6 procedure PARAMETER_TESTER(param int) is
7 begin
8 dbms_output.put_line('Param came in as: '||param);
9 glob_var := glob_var + 1;
10 dbms_output.put_line('Param left as : '||param);
11 dbms_output.put_line('glob_var is now : '||glob_var);
12 end;
13
14 begin
15 local_var := glob_var;
16 parameter_tester(local_var);
17 end;
18 /
Param came in as: 0
Param left as : 0
glob_var is now : 1
PL/SQL procedure successfully completed.
Learning is not a spectator sport 
I guess an [IN] COPY extension would only obfuscate more.
May be, it would be better if there was prаgmas like “DO NOT OPTIMIZE MY EXPRESSION”
or/and “Preserve expression semantic”.
in such a case I could say to compiler “I know, what I am doing in that place” and use
parameter_tester(glob_var+0);
or just
parameter_tester((glob_var));
And, if compiler decided to “optimize” expression AND DESTROY expression sematic – so do not use any intermediate inner variables for expression result,
it would be better if it could to fire something like PLW-05003
It can do it in a case of aliasing fields of plsql records in input parameters.
PS;
Sorry for my eng…
I think, at least, parenthesis should not be eliminated, at least – in parameters…
in common – “optimizing expressions” – how far it goes?
In extreme case it means – “you have no any warrantis in calculation sequence of your expression”.
It is not always so good, if I can’t say: “do not touch my expression”…
:))