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.
Recent comments
1 year 45 weeks ago
2 years 5 weeks ago
2 years 9 weeks ago
2 years 10 weeks ago
2 years 15 weeks ago
2 years 36 weeks ago
3 years 4 weeks ago
3 years 34 weeks ago
4 years 18 weeks ago
4 years 18 weeks ago