Reason to avoid global variables

Learnt something new about oracle, its about having global variables in packages.

Watch what happens to my parameter p_val in my below code.

rajesh@ORA11G> create or replace package pkg
  2  is
  3     procedure p;
  4  end;
  5  /

Package created.

Elapsed: 00:00:00.01
rajesh@ORA11G> create or replace package body pkg
  2  is
  3     g_global number ;
  4
  5     procedure prc_local(p_val number)
  6     as
  7     begin
  8             dbms_output.put_line(' value of p_val is = '||p_val);
  9             g_global := 7;
 10             dbms_output.put_line(' value of p_val was = '||p_val);
 11     end;
 12
 13     procedure p
 14     as
 15     begin
 16             g_global := 51;
 17             prc_local(g_global);
 18     end;
 19  end;
 20  /

Package body created.

Elapsed: 00:00:00.10
rajesh@ORA11G>
rajesh@ORA11G> exec pkg.p;
 value of p_val is = 51
 value of p_val was = 7

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11G>

It comes from the fact that IN parameters are passed by REFERENCE by default. so how can we defend this, one approach is to never use globals.

The other approach, is to change globals as expressions when passed to a parameters. (like below)

 13     procedure p
 14     as
 15     begin
 16             g_global := 51;
 17             prc_local(g_global+0);
 18     end;

rajesh@ORA11G> exec pkg.p;
 value of p_val is = 51
 value of p_val was = 51

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11G>