Helena Markova, June 28, 2001 - 12:39 am UTC
I Agree with Tom
Pichaimani Balasubramanian, June 28, 2001 - 12:57 pm UTC
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
john, September 05, 2002 - 12:57 pm UTC
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
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
A reader, September 06, 2002 - 4:53 pm UTC
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.
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
Rama, September 06, 2002 - 11:46 pm UTC
Global Variables
phil, March 31, 2004 - 3:35 am UTC
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?
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.
A reader, May 29, 2004 - 1:12 am UTC
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
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
NOTNA, May 29, 2004 - 10:54 pm UTC
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
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?
Hari, May 30, 2004 - 2:52 am UTC
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
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?
Hari, May 31, 2004 - 7:26 am UTC
And what about performance? Does it help?
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
Siva, September 01, 2004 - 12:09 am UTC
Hi Tom,
Is it possible to declare public or private variables
in PL/SQL?
Please do reply.
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)
.
dxl, October 13, 2004 - 11:10 am UTC
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??
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
BC, October 28, 2005 - 2:56 pm UTC
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
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
BC, October 31, 2005 - 10:32 am UTC
Tom,
Once again, thank you very much for your detailed, informative and quick response.
BC
Is this a Bug with 10g R2 ?
Raghu, January 14, 2006 - 8:34 am UTC
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.
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?
A reader, January 17, 2006 - 1:38 am UTC
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?
January 17, 2006 - 8:40 am UTC
how about dbms_trace?
an optimization in 10gr2
A reader, January 17, 2006 - 10:03 am UTC
"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
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
A reader, January 17, 2006 - 10:33 am UTC
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.
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
A reader, January 18, 2006 - 2:45 am UTC
package variables are reseting in web application
Dilip Kodwani, August 04, 2006 - 5:15 am UTC
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
August 04, 2006 - 8:01 am UTC
are you using a connection pool?
package variables are reseting in web application
Dilip Kodwani, August 05, 2006 - 7:53 am UTC
We are using connection pool
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
DILIP KODWANI, August 07, 2006 - 1:26 am UTC
THANK YOU FOR SUGGESTION
CAN WE USE GLOBAL TEMPORARY TABLE TO RETAINED VALUES
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
Ejaz Sayyed, August 09, 2006 - 11:20 am UTC
it helps!
thanks
A reader, August 18, 2006 - 5:31 am UTC
global variables
A reader, September 21, 2006 - 5:19 pm UTC
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.
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?
Vedran, September 26, 2006 - 9:14 am UTC
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?
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
Scott Mattes, September 27, 2006 - 9:49 am UTC
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
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.
Scott, September 28, 2006 - 10:46 am UTC
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.
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
Scott, September 29, 2006 - 8:58 am UTC
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.
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?
B the new hire, November 03, 2006 - 9:58 am UTC
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?
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
James Su, January 03, 2007 - 10:43 am UTC
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
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?
Mark, August 16, 2007 - 10:26 am UTC
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)?
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
muktar, September 01, 2013 - 6:41 am UTC
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;
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.
Mike Tranate, June 29, 2016 - 1:41 pm UTC
Hi Tom,
What is the difference between global variable and parameters?.
June 30, 2016 - 1:42 am UTC
Isn't this answered just be scrolling back up through this question ?
Oracle Queries
Sampat Das, July 02, 2020 - 6:08 am UTC
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...??
July 02, 2020 - 3:50 pm UTC
How exactly are you initializing it? Show us your code!