Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Irene.

Asked: June 27, 2001 - 11:22 am UTC

Answered by: Tom Kyte - Last updated: July 02, 2020 - 3:50 pm UTC

Category: Developer - Version: forms 6i

Viewed 50K+ times! This question is

You Asked

Hi Tom,
could you explain advantages and disadvantages
of using global variables and parameters in Forms
in terms of efficiency of application and system resourses.
What would you recommend for passing values between
blocks and program units in one form.
Thank you.
Irene


and we said...



Avoid global variables whenever possible (they are OK to some degree in a package body to maintain state and because they use of them is confined to the pacakge body).

Global Globals should be avoided whenever you can. Use them only when you have to. They are a BUG just waiting to happen.

Formal parameters are the way to go.

and you rated our response

  (35 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

June 28, 2001 - 12:39 am UTC

Reviewer: Helena Markova from Bratislava, Slovakia


I Agree with Tom

June 28, 2001 - 12:57 pm UTC

Reviewer: Pichaimani Balasubramanian from San Jose, CA USA

In the past I've encountered some serious bugs because of Global Variables. Its better to avoid it unless it is very necessary.

pacakge body global variables

September 05, 2002 - 12:57 pm UTC

Reviewer: john

are the variables declared on top of PACKAGE BODY that is the variables that are global to a package body are also main tain state

i have :
SQL> create or replace package my_pack AS
  2  
  3  PROCEDURE P1;
  4  END;
  5  /

SQL> Create or replace package BODY my_pack AS
  2  
  3  v1 varchar2(1) := 'N';
  4  
  5  PROCEDURE P1 AS
  6  BEGIN
  7   DBMS_OUTPUT.PUT_LINE(V1);
  8   IF v1 = 'N' THEN
  9    V1 := 'Y';
 10   END IF;
 11   DBMS_OUTPUT.PUT_LINE(V1);
 12  END;
 13  END;
 14  /

SQL> EXEC MY_PACK.P1;
N
Y

PL/SQL procedure successfully completed.

SQL> EXEC MY_PACK.P1;
Y
Y

any idea why is this happening 

Tom Kyte

Followup  

September 05, 2002 - 9:29 pm UTC

any variable defined outside of a procedure/function is a global variable and maintains its state for the duration of the session.

constatnt global variables

September 06, 2002 - 4:53 pm UTC

Reviewer: A reader

thanks tom
i understand now that we should try avoiding global varaibles.

in our project, to globally access the constants, we created a package that will have only constatns declared. other packages can refer these constants and use them in their body.

now will that create any problem, we are only in the development process, if that is a problem we can remove it.
please guide.

Tom Kyte

Followup  

September 06, 2002 - 7:33 pm UTC

constants are constants - they are perfect for global variables. Just define them as constant!

Global variables vs parameters

September 06, 2002 - 11:46 pm UTC

Reviewer: Rama from CT , USA


Global Variables

March 31, 2004 - 3:35 am UTC

Reviewer: phil from Paris

Hi Tom
I have a package spec as follows...

CREATE OR REPLACE PACKAGE inte AS

c_exact CONSTANT NUMBER := 0.99;

END;


But when I try and access this from a trigger I get
and error on this line...
IF l_prev.it >= inte.c_exact THEN

The error is PLS-00302: component 'C_EXACT' must be declared

Is this not available from the trigger?

Tom Kyte

Followup  

March 31, 2004 - 8:42 am UTC

ops$tkyte@ORA9IR2> create or replace package inte
  2  as
  3    c_exact  constant number := 0.99;
  4  end;
  5  /
 
Package created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2> create or replace trigger t_trigger
  2  before insert on t for each row
  3  begin
  4          if ( :new.x >= inte.c_exact )
  5          then
  6             null;
  7          end if;
  8  end;
  9  /
 
Trigger created.
 


<b>please give me the full test case</b>
 

Global Variables to Parameters.

May 29, 2004 - 1:12 am UTC

Reviewer: A reader

Hi Tom,

I'm in the process of converting the global variables into parameters (as you suggested). I have these 2 packages that access the global variable from PKG1 and then assigned some values on PKG2.

OLD WAY :

create or replace procedure pkg1 AS

type g_dflt_vals_tbl is table of varchar2(400) index by varchar2(400);
g_dflt_vals g_dflt_vals_tbl;


procedure init_global;
procedure process_prc;
...
..
.
end pkg1;
/

create or replace package body pkg1 AS
..
.
procedure process_prc
is
begin
.... some logic here
pkg2.get_dtls;
if g_dflt_vals('emp_blk.sa_grade') := 12
then
...
...
end if;
end;

end pkg1;
/

Now in pkg2

create or replace package body pkg2 AS
..
.
procedure get_dtls
is
begin
.... some logic here
pkg1.g_dflt_vals('emp_blk.salary') := <some computation>;
pkg1.g_dflt_vals('emp_blk.addr') := <xxx>;
pkg1.g_dflt_vals('emp_blk.sal_grade') := 12;
....
end get_dtls;

end pkg2;
/


And i want to convert the above code to :

create or replace procedure pkg1 AS

procedure init_global;
procedure process_prc;
...
..
.
end pkg1;
/

create or replace package body pkg1 AS
type g_dflt_vals_tbl is table of varchar2(400) index by varchar2(400);
g_dflt_vals g_dflt_vals_tbl;


procedure process_prc
is
begin
.... some logic here
pkg2.get_dtls(g_dflt_vals);
if g_dflt_vals('emp_blk.sa_grade') := 12
then
...
...
end if;
end;

end pkg1;
/



create or replace package body pkg2 AS

type g_dflt_vals_tbl is table of varchar2(400) index by varchar2(400);
g_dflt_vals g_dflt_vals_tbl;

procedure get_dtls(p_dflt_vals in out nocopy g_dflt_vals%type);
is
begin
.... some logic here
p_dflt_vals('emp_blk.salary') := <some computation>;
p_dflt_vals('emp_blk.addr') := <xxx>;
p_dflt_vals('emp_blk.sal_grade') := 12;
....
end get_dtls;

end pkg2;
/


What do you think about the changes that I'm proposing?


Regards,
NOTNA

Tom Kyte

Followup  

May 29, 2004 - 11:25 am UTC

globals are dangerous when they are "writable".

it looks like your globals are more like "constants"? if so, they are pretty "safe". it is when many things can "write" to them that globals get nasty.

Globals are not constant

May 29, 2004 - 10:54 pm UTC

Reviewer: NOTNA from Phil.

Actually the global variables are not constant, that's why I'm modifying it to pass only as parameters. but when I try to compile the pkg1 i'm recieving ang error:

823/18 PL/SQL: Statement ignored
824/18 PLS-00306: wrong number or types of arguments in call to "get_dtls"

but it's has the same set of parameters:

IN pkg1:

create or replace package body pkg1 AS
type g_dflt_vals_tbl is table of varchar2(400) index by varchar2(400);
g_dflt_vals g_dflt_vals_tbl;


procedure process_prc
is
begin
.... some logic here
pkg2.get_dtls(g_dflt_vals);
....
..


and in pkg2
create or replace package body pkg2 AS

type g_dflt_vals_tbl is table of varchar2(400) index by varchar2(400);
g_dflt_vals g_dflt_vals_tbl;

procedure get_dtls(p_dflt_vals in out nocopy g_dflt_vals%type);
is
begin
.... some logic here
p_dflt_vals('emp_blk.salary') := <some computation>;
p_dflt_vals('emp_blk.addr') := <xxx>;
p_dflt_vals('emp_blk.sal_grade') := 12;

..
.

what could be wrong here?

Cheers,
NOTNA



Tom Kyte

Followup  

May 30, 2004 - 10:39 am UTC

they are not the same type. they could only be the same type if there were one type defined and they both use it.


create package foo
as
type myType is ....;

......


and everywhere in the system you wanted the same type - you would use "foo.myType".

Everytime you issue the "type .... is...." you are creating a brand new type different from any existing type.

Agree. But any impact on performance?

May 30, 2004 - 2:52 am UTC

Reviewer: Hari from Kolkota

Hi Tom,
thanks for continuously guiding the Oracle community.
I have a question on using global variables vs parameter within a package body. Does it have any impact on performance.. say if I need to pass a large PL/SQL table many times in a loop.
In my case, I have to pass a PL/SQL table of roughly 50 x 50 (rows x cols) to multiple functions in a loop for some BOM processing. In order to avoid this I preferred having a temporary global variable (table) and using it within these functions. I do not update this table within these functions.
Do you think this will make it run faster and take less memory, particularly if it is to be used in OLTP system, called many times a day.

Thanks,
Hari

Tom Kyte

Followup  

May 30, 2004 - 10:41 am UTC

you can use NOCOPY to pass by reference (search for nocopy on this site before doing so so you understand the ramifications)


I prefer not to use globals OUTSIDE of a single package (eg: if you keep the globals in a package body, not in the spec) then it is manageable -- sort of like having "private class data" in java or c++.

And performance?

May 31, 2004 - 7:26 am UTC

Reviewer: Hari from Kolkota, India

And what about performance? Does it help?

Tom Kyte

Followup  

May 31, 2004 - 1:15 pm UTC

if you can live with the side effects of nocopy - yes, you will move lots less bytes back and forth in RAM for each function call.

make sure you understand the side effects first though!

OK

September 01, 2004 - 12:09 am UTC

Reviewer: Siva from Bangalore,India

Hi Tom,
Is it possible to declare public or private variables
in PL/SQL?
Please do reply.


Tom Kyte

Followup  

September 01, 2004 - 7:59 am UTC

yes -- when using packages you can put variables in the specification (global, public) or in the body (global, private) or in the local procedures/functions themselves (local, not global, private)

.

October 13, 2004 - 11:10 am UTC

Reviewer: dxl from uk

om

I have a package. I am using a cursor to define a "template" record type (as i have seen you
use this technique before). I need to do this because the record variable is going to be bulk fetched
into and inserted into a table.


CREATE OR REPLACE PACKAGE MY_PKG AS
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

cursor t_pat is select * from tmp_table;

TYPE pat_array IS TABLE OF t_pat%ROWTYPE index by binary_integer;



----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

FUNCTION F_Format__Data ( P_Batch_ID IN NUMBER,
rec_t_pat IN t_pat%ROWTYPE)


RETURN NUMBER;


..
..

END MY PKG;


The reason the cursor needs to be in the spec is because i need to reference the rowtype in other
functions and procedures in the package.

So then in a procedure within the package body i have


PROCEDURE P1 ( )

IS


l_source pat_array;
l_good pat_array;

BEGIN

open t_pat;
loop

fetch t_pat bulk collect into l_source limit 1000;
l_good.delete;
l_bad.delete;

for i in 1 .. l_source.count
loop

if ( F_Format_Data ( P_Batch_ID, l_source(i) ) = 1 )
then

end if;

end loop;


forall i in 1 .. l_good.count

insert into Data_Details values l_good(i);


...
... etc






However I would like to pass a parameter to the cursor which the template record is based on.
Can this be done??

ie in the package spec i need:


cursor t_pat is select * from tmp_table where col = :col_parameter;


but don't know how to do this if its posssible??


Tom Kyte

Followup  

October 13, 2004 - 12:10 pm UTC

ops$tkyte@ORA9IR2> create or replace package demo_pkg
  2  as
  3          cursor c ( p_inputs in varchar2 )
  4          is
  5          select * from dual where dummy = p_inputs;
  6  end;
  7  /
 
Package created.



and then you 

  open demo_pkg.c( 'whatever...' );

 

Initializing global variables

October 28, 2005 - 2:56 pm UTC

Reviewer: BC from Macomb Twp, MI

Tom,

When are variables defined in the package spec initialized ? It seems like they are initialized only once per session.

create or replace 
package p1 is
   cursor cur1(c_ctr number) is select object_name 
                                from all_objects 
                                where rownum < c_ctr + 1;
   rec1 cur1%rowtype;
   nread number := 0;
  function f1 ( p_ctr number ) return number;
end; -- package spec
/

create or replace 
package body p1 is
function f1 ( p_ctr number ) return number is
begin
   for rec1 in cur1(p_ctr)
   loop
      nread := nread + 1;
   end loop;
   return nread;
end;
end;
/

create or replace 
function f1 ( p_ctr number ) return number is
   cursor cur1(c_ctr number) is select object_name 
                                from all_objects 
                                where rownum < c_ctr + 1;
   rec1 cur1%rowtype;
   nread number := 0;
begin
   for rec1 in cur1(p_ctr)
   loop
      nread := nread + 1;
   end loop;
   return nread;
end;
/

declare
       result   number := 0;
begin

  for ctr in 1 .. 5
  loop
      result := p1.f1(10);
      dbms_output.put_line('p1.f1 Returned -> ' || result);
  end loop;
  
  for ctr in 1 .. 5
  loop
      result := f1(10);
      dbms_output.put_line('p1.f1 Returned -> ' || result);
  end loop;
end;

SQL> connect abc/def@oradb
Connected.
SQL> set serverout on size 100000
SQL> declare
  2         result   number := 0;
  3  begin
  4    for ctr in 1 .. 2
  5    loop
  6        result := p1.f1(10);
  7        dbms_output.put_line('p1.f1 Returned -> ' || result);
  8    end loop;
  9    for ctr in 1 .. 2
 10    loop
 11        result := f1(10);
 12        dbms_output.put_line('f1 Returned -> ' || result);
 13    end loop;
 14  end;
 15  /
p1.f1 Returned -> 10
p1.f1 Returned -> 20
f1 Returned -> 10
f1 Returned -> 10

PL/SQL procedure successfully completed.

SQL> /
p1.f1 Returned -> 30
p1.f1 Returned -> 40
f1 Returned -> 10
f1 Returned -> 10

PL/SQL procedure successfully completed.

SQL> connect abc/def@oradb
Connected.
SQL> set serverout on size 100000
SQL> /
p1.f1 Returned -> 10
p1.f1 Returned -> 20
f1 Returned -> 10
f1 Returned -> 10

PL/SQL procedure successfully completed.

Thanks 

Tom Kyte

Followup  

October 29, 2005 - 10:41 am UTC

variables defined outside of procedures or funtions in packages by definition are initialized only once.

Just like variables in any language. They maintain a state, they are valid from call to call to the database. They are global variables, persistent for the life of your session.

Thanks

October 31, 2005 - 10:32 am UTC

Reviewer: BC from Macomb Twp, MI

Tom,

Once again, thank you very much for your detailed, informative and quick response.

BC

Is this a Bug with 10g R2 ?

January 14, 2006 - 8:34 am UTC

Reviewer: Raghu from Bangalore, India

after resetting the package variable, i am doing a create or replace of the package again. next when i run, the package variable does not get re-initialized and still holds the last value set to it.
Once i explicitly drop the package and run again, it works.
This works well with 9i too.
Your thoughts on this.

SQL> create or replace package my_pak as
  2   g_day   varchar2(5) := 'SAT';
  3   procedure set_day(p_day in varchar2);
  4  end;
  5  /

Package created.

SQL> 
SQL> create or replace package body my_pak as
  2   procedure set_day(p_day in varchar2) is
  3   begin
  4      g_day := p_day;
  5   end set_day;
  6  end;
  7  /

Package body created.

SQL> set serveroutput on
SQL> 
SQL> begin
  2    dbms_output.put_line('global variable '|| my_pak.g_day);
  3    my_pak.set_day('SUN');
  4    dbms_output.put_line('global variable '|| my_pak.g_day);
  5  end;
  6  /
global variable SAT
global variable SUN

PL/SQL procedure successfully completed.

SQL> create or replace package my_pak as
  2   g_day   varchar2(5) := 'SAT';
  3   procedure set_day(p_day in varchar2);
  4  end;
  5  /

Package created.

SQL> 
SQL> create or replace package body my_pak as
  2   procedure set_day(p_day in varchar2) is
  3   begin
  4      g_day := p_day;
  5   end set_day;
  6  end;
  7  /

Package body created.

SQL> set serveroutput on
SQL> 
SQL> begin
  2    dbms_output.put_line('global variable '|| my_pak.g_day);
  3    my_pak.set_day('SUN');
  4    dbms_output.put_line('global variable '|| my_pak.g_day);
  5  end;
  6  /
global variable SUN
global variable SUN

PL/SQL procedure successfully completed.

SQL> drop package my_pak;

Package dropped.

SQL> create or replace package my_pak as
  2   g_day   varchar2(5) := 'SAT';
  3   procedure set_day(p_day in varchar2);
  4  end;
  5  /

Package created.

SQL> 
SQL> create or replace package body my_pak as
  2   procedure set_day(p_day in varchar2) is
  3   begin
  4      g_day := p_day;
  5   end set_day;
  6  end;
  7  /

Package body created.

SQL> set serveroutput on
SQL> 
SQL> begin
  2    dbms_output.put_line('global variable '|| my_pak.g_day);
  3    my_pak.set_day('SUN');
  4    dbms_output.put_line('global variable '|| my_pak.g_day);
  5  end;
  6  /
global variable SAT
global variable SUN

PL/SQL procedure successfully completed. 

Tom Kyte

Followup  

January 15, 2006 - 3:38 pm UTC

It is an optimization in 10gr2

it is recognizing that the code is bit for bit, byte for byte "the same", it did nothing therefore.

Add a newline to the second package spec, it will change the behavior.


as far as the database is concerned - you didn't create or replace anything.

If we query out last_ddl_time, we can see this:

ops$tkyte@ORA10GR2> set serveroutput on
ops$tkyte@ORA10GR2> select object_type, last_ddl_time from user_objects where object_name = 'MY_PAK';

OBJECT_TYPE         LAST_DDL_TIME
------------------- --------------------
PACKAGE             15-jan-2006 15:29:13
PACKAGE BODY        15-jan-2006 15:29:13

ops$tkyte@ORA10GR2> begin
  2    dbms_output.put_line('global variable '|| my_pak.g_day);
  3    my_pak.set_day('SUN');
  4    dbms_output.put_line('global variable '|| my_pak.g_day);
  5  end;
  6  /
global variable SAT
global variable SUN

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> exec dbms_lock.sleep(3);

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> create or replace package my_pak as
  2   g_day   varchar2(5) := 'SAT';
  3   procedure set_day(p_day in varchar2);
  4  end;
  5  /

Package created.

ops$tkyte@ORA10GR2> create or replace package body my_pak as
  2   procedure set_day(p_day in varchar2) is
  3   begin
  4      g_day := p_day;
  5   end set_day;
  6  end;
  7  /

Package body created.

ops$tkyte@ORA10GR2> set serveroutput on
ops$tkyte@ORA10GR2> select object_type, last_ddl_time from user_objects where object_name = 'MY_PAK';

OBJECT_TYPE         LAST_DDL_TIME
------------------- --------------------
PACKAGE             15-jan-2006 15:29:13
PACKAGE BODY        15-jan-2006 15:29:13

ops$tkyte@ORA10GR2> begin
  2    dbms_output.put_line('global variable '|| my_pak.g_day);
  3    my_pak.set_day('SUN');
  4    dbms_output.put_line('global variable '|| my_pak.g_day);
  5  end;
  6  /
global variable SUN
global variable SUN

PL/SQL procedure successfully completed.

<b>see, it did "nothing", but if we just:</b>

ops$tkyte@ORA10GR2> select object_type, last_ddl_time from user_objects where object_name = 'MY_PAK';

OBJECT_TYPE         LAST_DDL_TIME
------------------- --------------------
PACKAGE             15-jan-2006 15:30:57
PACKAGE BODY        15-jan-2006 15:30:57

ops$tkyte@ORA10GR2> begin
  2    dbms_output.put_line('global variable '|| my_pak.g_day);
  3    my_pak.set_day('SUN');
  4    dbms_output.put_line('global variable '|| my_pak.g_day);
  5  end;
  6  /
global variable SAT
global variable SUN

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> exec dbms_lock.sleep(3);

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> create or replace package my_pak as
  2   g_day   varchar2(5) := 'SAT';
  3
  4   procedure set_day(p_day in varchar2);
  5  end;
  6  /

Package created.

<b>Note the additional newline, nothing else...</b>

ops$tkyte@ORA10GR2> create or replace package body my_pak as
  2   procedure set_day(p_day in varchar2) is
  3   begin
  4      g_day := p_day;
  5   end set_day;
  6  end;
  7  /

Package body created.

ops$tkyte@ORA10GR2> set serveroutput on
ops$tkyte@ORA10GR2> select object_type, last_ddl_time from user_objects where object_name = 'MY_PAK';

OBJECT_TYPE         LAST_DDL_TIME
------------------- --------------------
PACKAGE             15-jan-2006 15:31:00
PACKAGE BODY        15-jan-2006 15:31:00

ops$tkyte@ORA10GR2> begin
  2    dbms_output.put_line('global variable '|| my_pak.g_day);
  3    my_pak.set_day('SUN');
  4    dbms_output.put_line('global variable '|| my_pak.g_day);
  5  end;
  6  /
global variable SAT
global variable SUN

PL/SQL procedure successfully completed.
 

Can we somehow use the global variables in this case?

January 17, 2006 - 1:38 am UTC

Reviewer: A reader

if dbms_profiler is not an option for Oracle on mainframe, can we can global variables to record

1) what packages are being used during a period of test
(start database on T0, start test on T1, finish test on T2), need a report a list of packages having been used during T1 and T2.

2) It would be "nice to have" to add to the above report what functions/procedures inside each packages appeared in the list have been used between T1 and T2, and how many time they were called?

Tom Kyte

Followup  

January 17, 2006 - 8:40 am UTC

how about dbms_trace?

an optimization in 10gr2

January 17, 2006 - 10:03 am UTC

Reviewer: A reader

"as far as the database is concerned - you didn't create or replace anything."

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> create table temp as select * from all_objects;


SQL> select m.value
  2  from v$mystat m, v$sysstat y
  3  where y.statistic#=m.statistic#
  4  and name='redo size';

     VALUE
----------
   1318888

SQL> update temp
  2  set owner=owner, object_name=object_name,subobject_name=subobject_name;

SQL> select m.value
  2  from v$mystat m, v$sysstat y
  3  where y.statistic#=m.statistic#
  4  and name='redo size';

     VALUE
----------
  18526856



as far as the database is concerned - I didn't update anything.

why the redo size  

Tom Kyte

Followup  

January 17, 2006 - 10:20 am UTC

sure you did, you updated every block in the table. As far as the system is concerned you did a ton of work.

The updates to any indexes are object_name or subobject_name would be skipped but surely you realize you locked every row, started a transaction entry on every block right? Those rows are locked.

Sure I did

January 17, 2006 - 10:33 am UTC

Reviewer: A reader

Fully agree, I told Oracle to update the table and it
updated it.

Now, when I tell Oracle to
create or replace
my package (or package body),
how can it be so cheeky to decide

"ha, I am clever, you didn't change anything, so I will
tell you
Package body created.
(which is a lie, but who cares, I am Oracle
and what I say, counts)"

and does nothing ?

Sorry,
that's not an optimization, it is a bug.

Tom Kyte

Followup  

January 17, 2006 - 3:58 pm UTC

no, it is an optimization.

create table t ( x int );
create index t_idx on t(x);
insert into t values ( 1 );
commit;

update t set x = 1;

I just told oracle to modify my table and maintain the indexes.

But it won't. The indexes remain (thankfully) untouched. Optimization.

It is sqlplus that says "package created", it should just really say "command completed without error" since that is the truth. I mean, if it replaced it - it surely didn't create it did it? Arguing semantics here though.

I'm sure if you hate this feature (me, love it - everyone just recompiles everything regardless, this avoids lots of work) I'm sure there is likely to be an event to disable it. You can contact support and verify that and see if it is something that can be used.



thx

January 18, 2006 - 2:45 am UTC

Reviewer: A reader


package variables are reseting in web application

August 04, 2006 - 5:15 am UTC

Reviewer: Dilip Kodwani from INDIA

We have declared global variable in package specification
we are setting these variables at the time of application start. but after some time when we are using these global variables in the application , their values are returned as null.However,if we refresh the jsp,values are obtained correctly. We are unable to understand this strange behavior. kindly suggest some means to retain the value


Tom Kyte

Followup  

August 04, 2006 - 8:01 am UTC

are you using a connection pool?

package variables are reseting in web application

August 05, 2006 - 7:53 am UTC

Reviewer: Dilip Kodwani

We are using connection pool


Tom Kyte

Followup  

August 05, 2006 - 10:53 am UTC

think about what a connection pool means for a moment - and then you'll understand why you cannot use "state" that is stored in a SESSION....


grab a connection from the pool (say this gets oracle SID=123)
use it
return it to the pool
...
grab a connection from the pool (say this gets oracle SID=456)
use it
return it to the pool
...
grab a connection from the pool (say this gets oracle SID=123 again)....


you are NOT maintaining a consistent connection - by the very design of a connection pool

package variables are reseting in web application

August 07, 2006 - 1:26 am UTC

Reviewer: DILIP KODWANI from INDIA

THANK YOU FOR SUGGESTION

CAN WE USE GLOBAL TEMPORARY TABLE TO RETAINED VALUES



Tom Kyte

Followup  

August 07, 2006 - 7:50 am UTC

NO - FOR SAME REASON.

if you use a connection pool, you flip flop from session to session. global temporary tables are session or transaction specific.

You either have to retain this state

a) in a database table (like I do), with a sessionid to identify the rows for your session
b) in a global application context
</code> http://asktom.oracle.com/pls/ask/search?p_string=%22global+application+context%22 <code>
c) in your middle tier
d) in your cookies.

global variable

August 09, 2006 - 11:20 am UTC

Reviewer: Ejaz Sayyed from India

it helps!
thanks

August 18, 2006 - 5:31 am UTC

Reviewer: A reader


global variables

September 21, 2006 - 5:19 pm UTC

Reviewer: A reader

Hi

I am use a global variable like

Create or replace package body pkg_abc is

v_1 varchar2(16):=fn_get_ref();

proceduer_1 ....
procedure_2...

end;

DO I need to define global variable in package specifications also.


Tom Kyte

Followup  

September 22, 2006 - 2:49 am UTC

I don't know - do you?

only you can really answer that

I can say - it would be considered a bad practice, in any programming language, to have many global variables and globals in package specifications would be considered a really bad practice since anyone could modify it

How to find packages with state?

September 26, 2006 - 9:14 am UTC

Reviewer: Vedran from Croatia, Europe

Tom,
how to find all packages that have global variables or global constants (either in package header or package body)?
I have a db with many packages, and it's not very practical to read tons of package headers and bodyes to find globals. Is there a SQL query that can do it?

Tom Kyte

Followup  

September 26, 2006 - 3:52 pm UTC

not really, you'd have to figure out a sql query that sort of "parsed" the sql - not a trivial task at all.

how to find global variables in packages

September 27, 2006 - 9:49 am UTC

Reviewer: Scott Mattes from F'burg, VA (I go thru L'burg twice a week on the way to work, never seen you at stuff mart though)

Might this be a starting point for finding global variables defined in packages? I had to type this on one PC, print it to bring to this PC, so you get to add the 'upper' around the 'text' field references and add the other datatypes.

select *
from sys.all_source
where owner like '&owner'
-- stuff I want to find
and ( instr( text, 'INTEGER ' ) > 0
OR instr( text, ' NUMBER ' ) > 0 )
and
-- stuff I don't want to find
and not instr( text, 'PROCEDURE ' ) > 0
and not instr( text, 'FUNCTION ' ) > 0
and not instr( text, ' CONSTANT ' ) > 0

Tom Kyte

Followup  

September 27, 2006 - 3:29 pm UTC

well, that would sort of find local variables, global variables - well, variables and lots of other stuff in general

no, it would not be a starting part.

September 28, 2006 - 10:46 am UTC

Reviewer: Scott

After I posted I went and refined the query a little;

- you have to add the 'upper()' function around the text field

- you need to add 'and type = 'PACKAGE' to limit to just the package spec (this limits it to just global variables)

- added 'and not upper( text ) like '%--%INTEGER%'

- did the same for /* and IN and OUT

- more data types

This limited the output to a much more manageable size for human scanning.

I could copy what I ended up with and post it here (or email it), if anyone is interested.

Tom Kyte

Followup  

September 29, 2006 - 7:27 am UTC

create package body my_pkg
as

procedure p
is
x1 number;
x2 number;
......
x300000 number
begin


you are 'grepping out' every variable, not sure I see the use, if the goal is to find "globals"


me, I would assume they are at the top of the package (convention), hence, read top of source code.

Guess I missed the point

September 29, 2006 - 8:58 am UTC

Reviewer: Scott

Sorry, I must have misread. I thought 'globals' meant variables defined in the package spec.

The original SQL I posted did in fact go against all code present in the table, but with the mod in my second post it only goes against the package spec.

On weeks where I'm not having a good day I should learn to keep my mouth (fingers) out of gear.



Tom Kyte

Followup  

September 30, 2006 - 7:28 am UTC

global variables are anything defined outside of a procedure or function...

they are global variables in the package body, if they meant globals JUST EXPOSED IN THE SPEC, then your approach would get the low hanging fruit.

Can globals split across packages?

November 03, 2006 - 9:58 am UTC

Reviewer: B the new hire from Washington, DC

Thanks Tom,

I was told to split up an old package that has been patched for years. It uses tons of global variables (not constants). Is it possible to easily split up the code between 3 packages & still have the old global variables be shared or do I have to convert all of them to parameters? These are not forms, they are being called by Oracle Apps Concurrent programs. They want me to have common subroutine packages be called by each main package & share global variables.

My time estimate for the rewrite seems to require quite a lot of time to convert all these globals to parameters. Is there a faster way?

Tom Kyte

Followup  

November 03, 2006 - 11:06 am UTC

</code> http://asktom.oracle.com/Misc/something-new-i-learned-this-week.html <code>

avoid globals like the plague.

You'll never ever be sorry you did, you'll only be really happy you have.

I would take the time to re-factor the code properly since you are going to do a major "lobotomy" here anyway.

Might as well end up with less buggy, easier to understand and more maintainable code (that must be the goal of doing the split right? I mean there are no other logical reasons for doing it - so you must be refactoring for maintenance further on down the road - might as well do it right)

storing constant variables in a table

January 03, 2007 - 10:43 am UTC

Reviewer: James Su from Toronto, Canada

Hello Tom,
I save my constant variables in a table and access them by their names through a function. In this way I can add new constant variables by adding new entries to this table without recompiling the package. And I can also use this function in DDL (for example, where clause of a view).

Can you tell me whether it's a good approach? Thank you.

CREATE TABLE my_status (
id NUMBER(10) PRIMARY KEY
,name VARCHAR2(30)
);

INSERT INTO my_status VALUES (1,'ONLINE');
INSERT INTO my_status VALUES (2,'OFFLINE');


CREATE OR REPLACE PACKAGE my_const AS

FUNCTION status (p_key IN VARCHAR2) RETURN NUMBER DETERMINISTIC;

END my_const;
/


CREATE OR REPLACE PACKAGE BODY my_const AS

TYPE my_type_number IS TABLE OF NUMBER INDEX BY VARCHAR2(30);

all_statuses my_type_number;

FUNCTION status (p_key IN VARCHAR2) RETURN NUMBER DETERMINISTIC
IS
lv_ret NUMBER;
BEGIN
BEGIN
lv_ret := all_statuses(p_key);
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
SELECT id
INTO all_statuses(p_key)
FROM my_status
WHERE name = p_key;

lv_ret := all_statuses(p_key);

EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
END;
RETURN lv_ret;
END status;

BEGIN
------------------------------------------------
-- package initialization, load all statuses into memory
FOR lv_rec IN (SELECT id, name
FROM my_status
)
LOOP
all_statuses(lv_rec.name) := lv_rec.id;
END LOOP;

END my_const;
/


SELECT my_const.status('ONLINE') FROM DUAL;

MY_CONST.STATUS('ONLINE')
-------------------------
1

Tom Kyte

Followup  

January 05, 2007 - 8:18 am UTC

In releases PRIOR to 10gR2, I would always reference this function using a scalar subquery, eg:


create view vw
as
select ...
from ...
where column = (select my_const.status('ONLINE') from dual)


NOT:


create view vw
as
select ...
from ...
where column = my_const.status('ONLINE')



prior to 10gr2, the query processor would not use the deterministic quality of this function and we'd be free to invoke that function many times (overhead).

using the scalar subquery will invoke scalar subquery caching.

Are we guaranteed that a global variable's state will remain throughout session?

August 16, 2007 - 10:26 am UTC

Reviewer: Mark from San Francisco

If I have a global variable that I set in the package initialization section, and I have a function in that package that does nothing more than return that global variable, can I be 100% sure that the global variable will retain its state throughout the session? Even if the session is active for many hours and package is not called again for several hours within that session (could it get pushed out of cache/pga if there are lots of active users and processes and lots of other packages being executed)?
Tom Kyte

Followup  

August 20, 2007 - 10:04 pm UTC

... can I be 100% sure that the global variable will retain its
state throughout the session? ...

no, of course not. It is a global variable - anything that has access to the specification (if defined there) can modify it.

if defined in the body, you would need to add "and there are NO OTHER routines in the body that write to it"



Now, if it is a global in the body and no one writes to it - your session STATE (not the plsql code) contains the value and that never gets aged out - so the value would stay the same

just like a real program (because plsql is a real programming environment)

fatal error

September 01, 2013 - 6:41 am UTC

Reviewer: muktar from bangladesh

dear tom
i have a function ,when i run this show error fatal error,how i avoid this error

function CF_in_wordsFormula return Char is
v_words varchar2(100);
begin
logic
into v_words
from dual;
return v_words;
end;
Tom Kyte

Followup  

September 04, 2013 - 6:31 pm UTC

do not run this function.

simple - we are done.



you supply nothing here that could cause anyone to give you any useful answer. what are you thinking? what possible feedback could you expect?


This is in the top ten least useful questions I've ever seen phrased. honestly.


think about it - think about what you've written and what possible feedback anyone could seriously give you.



"i have a function" - ok, that's nice, we have no clue what it is since the function below is gibberish.


"when I run this show fatal error" - you mean it dies? it actually lived and died? that is all i can think of because you don't care to share the 'fatal' error. Is that an ora-600, an ora-7445 or something else?????????

we have no way to see your code
we have no clue what it is that is going wrong.



I should just tell you "my car won't start" and ask you why - you'd have the same amount of information I do.



June 29, 2016 - 1:41 pm UTC

Reviewer: Mike Tranate from Pagsanjan,Laguna,Philippines

Hi Tom,

What is the difference between global variable and parameters?.

Connor McDonald

Followup  

June 30, 2016 - 1:42 am UTC

Isn't this answered just be scrolling back up through this question ?

Oracle Queries

July 02, 2020 - 6:08 am UTC

Reviewer: Sampat Das from India

Hi Tom,
Hope you are doing well.

I have a query regarding oracle global/package variables.
I have a package where i define a global variable(g_cweb_process PLS_INTEGER type) in pkg spec and in package body i am initializing it to 1 or 0 based on a parameter (if it is on then g_cweb_process :=1 else g_cweb_process :=0).

But while calling this variable(g_cweb_process) in another package
the variable value is set to NULL.
But it should give us the value 0 or 1.

Can you please help me on this...??

Chris Saxon

Followup  

July 02, 2020 - 3:50 pm UTC

How exactly are you initializing it? Show us your code!