please explain in a lay man's language
Kulguru, December 22, 2001 - 2:24 pm UTC
Tom
What do you mean by
an OCI call to the server or a server-to-server RPC
December 22, 2001 - 6:36 pm UTC
Well, that was a quote but it basically just means a "call".
OCI is Oracles Call Interface, a C API. there i might make a call to a procedure in the database, that would be a call
Or i might make a call over a database link to another database- that is a server to server RPC (remote procedure call)
SERIALLY_REUSABLE vs. DBMS_SESSION.reset_package
A reader, September 09, 2002 - 12:55 pm UTC
In an environment with hundreds of database sessions, and hundreds of PL/SQL packages, most of which have package variables (including PL/SQL records and tables) that all need to be cleared out for subsequent server calls, which approach would usually be better for performance - using the PRAGMA SERIALLY_REUSABLE in the packages, or including a call to DBMS_SESSION.reset_package for every call to the server? The purpose of using one of these features is to clear out the package variables so that no values will be left over from a prior call to the server, which is especially important with connection pooling.
If one is sometimes better but also sometimes worse than the other (as I expect it is), what are the conditions that would influence which is better or worse?
September 09, 2002 - 7:32 pm UTC
If you don't need the package variables to persist across ANY calls, not a single call -- serially reusable (but then again, hmm, why did you use package globals in the first place???? confusion from me there -- seems like local variables would have done it???)
dbms_session.reset_package, while MUCH faster then logging in and out, has some performance implications (not as fast as not doing it obviously!). That and it'll rip down the entire session state, eg: any opened cursors you have (prepared statements) as well.
My follow-up to your follow-up
A reader, September 09, 2002 - 8:10 pm UTC
Thanks a lot for your help.
Package-level variables were used because the code was originally written for a client-server environment. It made sense in that environment because the same database session would be maintained as the user moved from canvas to canvas or form to form.
But now there is a requirement to have the code called from other environments such as an application server or web server. Package state will need to be maintained across multiple procedure calls within the scope of a single anonymous PL/SQL block passed in from the app/web server, but package state is specifically _not_ to persist across multiple calls to that block. SERIALLY_REUSABLE seems to meet that criteria, according to the Oracle documentation.
September 10, 2002 - 7:08 am UTC
As long as you do not call any packages not marked serially reusable yes (eg: all dbms_, utl_, htp, htf, owa, owa_util, etc -- they all maintain a state as well and it might not work in your environment if they do (or it might))
State in Oracle packages
A reader, September 10, 2002 - 9:42 am UTC
Yes, that is something important to think about because the Oracle packages cannot be made serially reusable. I figure that as long as they are used in such a way that their externally observable state is cleaned out before a connection goes back into the pool, the internal state of the Oracle packages should not hurt the application's behavior when the next end-user grabs the connection from the pool. For example, if all UTL_FILE files are closed out, it should not matter to the next end-user that the previous user who had the same database session was using UTL_FILE. Of course, any code using the stateful Oracle packages will have to be reviewed and tested to death to ensure that their state does not cause a problem.
pragma SERIALLY_REUSABLE for web applications
Kamal Kishore, June 24, 2003 - 10:31 am UTC
Hi Tom,
Would you recommend using this PRAGMA for our packages used in our web applications? Typically, the client calls a packaged procedure, some job is done and result is returned. In this case we do not care about the session state.
1). Is it advisible to use this for packages used in web applications?
2). Are there any cavets (problems) if we do decide to use it?
3). Any performance issues? (rutime penalties or runtime efficiency or ...) Would its use be more efficient or would we have performance problems?
Thanks,
June 25, 2003 - 9:06 am UTC
no need in mod_plsql, it already takes care of that.
I would not recommend it, not heavily used. I've not used it myself
OK
Gerhard, March 09, 2004 - 1:29 am UTC
Dear Sir,
I would like to declare a variable in a package and upon
each call it must be incremented.But the variable is resetting itself to its initialized value after each call.
How to get around with this?Any solution you have?
for example
sql>exec dbms_output.put_line(mypack.i)//put as 1
sql>exec dbms_output.put_line(mypack.i)//put as 2
sql>exec dbms_output.put_line(mypack.i)//put as 3
Is it possible to declare a static variable in Oracle?
Please do reply.
Bye!
March 09, 2004 - 11:48 am UTC
put it in the package body, not in the procedure itself (eg: make it global)
ops$tkyte@ORA9IR2> create or replace package demo_pkg
2 as
3 function f return number;
4 end;
5 /
Package created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace package body demo_pkg
2 as
3 g_global_number number := 0;
4
5 function f return number
6 is
7 begin
8 g_global_number := g_global_number+1;
9 return g_global_number;
10 end;
11
12 end;
13 /
Package body created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_output.put_line( demo_pkg.f );
1
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_output.put_line( demo_pkg.f );
2
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> exec dbms_output.put_line( demo_pkg.f );
3
PL/SQL procedure successfully completed.
Still Not Recommended?
Yuan, November 29, 2005 - 2:02 pm UTC
You replied that you wouldn't recommend this feature back in mid 2004 because it's not widely used. Has that changed? The fact that this thread has lain dormant for so long does not bode well for a positive response to my question. 8^(
November 30, 2005 - 10:56 am UTC
no, I haven't seen any increase in use personally.
Oops
Yuan, November 29, 2005 - 2:03 pm UTC
D'oh! I meant back in mid 2003.
Avoiding ORA-04068
Albert Nelson A, June 03, 2010 - 5:19 am UTC
Hi Tom,
There are some packages defined like below:
create table tmp_tst (n number);
create or replace package tmp_pk_stateful is
function getval return number;
procedure proc;
end tmp_pk_stateful;
/
create or replace package body tmp_pk_stateful is
l_var varchar2(10) := 'Dummy';
function getval return number is
l_n number;
begin
select count(*)
into l_n
from tmp_tst;
return l_n;
end;
procedure proc is
begin
dbms_output.put_line('Hello World');
end;
begin
-- Initialization
null;
end tmp_pk_stateful;
/
Note that this package is stateful just because we have defined l_var varchar2(10) := 'Dummy'; in the package body.
When we do any ddl (for eg, add a column to tmp_tst) the package gets invalidated and the next time the package is called we get ORA-04068.
18:12:22 Session 1> exec tmp_pk_stateful.proc;
Hello World
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
18:12:25 Session 1>
18:12:57 Session 2> alter table tmp_tst add nt number;
Table altered.
Elapsed: 00:00:00.01
18:13:15 Session 2>
18:15:55 Session 1> exec tmp_pk_stateful.proc;
BEGIN tmp_pk_stateful.proc; END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "FMS_DBA.TMP_PK_STATEFUL" has been
invalidated
ORA-06508: PL/SQL: could not find program unit being called:
"FMS_DBA.TMP_PK_STATEFUL"
ORA-06512: at line 1
Elapsed: 00:00:00.01
18:15:58 Session 1> SET SERVEROUTPUT ON
18:16:08 Session 1> exec tmp_pk_stateful.proc;
Hello World
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.03
18:16:11 Session 1>
To avoid this, since we know that we are not keeping any state across calls, if we include pragma serially_resusable in the specification and body, we no longer get ORA-04068. Do you consider this as a legitimate use?
June 08, 2010 - 11:35 am UTC
I cannot recommend serially reusable as that is so infrequently used (like never) that I think the odds of you hitting something untoward would be high. And it would add overhead to each call as it reinstantiates the package.
IF you have a state that is not necessary
THEN
you do not have to have a state, get rid of it
END IF
Just get rid of the globals, what use are they to you? None. So stop using them. It is wrong to use them.
Global variable within a package body
Albert Nelson A, June 09, 2010 - 2:55 am UTC
Hi Tom,
There are some scenarios like below:
create or replace package body pkg is
begin
pb_l varchar2(10) := 'SOMEVAL';
procedure p1 is
begin
select ...
from ...
where col_val = pb_l;
end;
procedure p2 is
begin
select ...
from ...
where col_val = pb_l;
end;
procedure p3 is
begin
select ...
from ...
where col_val = pb_l;
end;
end;
ie., If same constant value is being used at various places in different procedures / functions of the same package bocy, instead of coding the value at all the different places, I find the value is assigned to a global variable within the package body and the variable is used throughout. Is it not a good practice?
Here the package becomes a stateful package just because of these global variables within the package body. That is why I was asking about the pragma.
If using that pragma for this purpose is not correct, what is the recomended way of coding above type of package bodies?
Thanks and regards,
Albert Nelson A.
June 10, 2010 - 11:31 am UTC
option 1: move the globals into a package by themself, one that will not change with the re-implementation of the body.
option 2: use the getter/setter concept
create or replace package body pkg is
begin
function pb_l return varchar2
is
begin
return 'SOMEVAL';
end;
procedure p1 is
l_pb_1 := pb_1;
begin
select ...
from ...
where col_val = l_pb_l;
end;
procedure p2 is
l_pb_1 := pb_1;
begin
select ...
from ...
where col_val = l_pb_l;
end;
procedure p3 is
l_pb_1 := pb_1;
begin
select ...
from ...
where col_val = l_pb_l;
end;
end;
Thanks
Albert Nelson A, June 11, 2010 - 12:51 am UTC
An example from Steven Feuerstein
Parthiban Nagarajan, July 01, 2010 - 5:22 am UTC
Hi Tom
The following script is an example for the PRAGMA SERIALLY_REUSABLE, from Steven Feuerstein. Can you give some explanation on this? If I am not closing the SERIALLY_REUSABLE cursor, how it is allowing me to open it again?
<quote src="10gR1_Doc">
Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, Oracle generates an error.
</quote>
Why this restriction?
SQL> CREATE OR REPLACE PACKAGE serial_package
AS
CURSOR emps_cur IS
SELECT * FROM employees;
END serial_package;
/
Package created.
SQL> BEGIN OPEN serial_package.emps_cur; END;
/
PL/SQL procedure successfully completed.
SQL> BEGIN OPEN serial_package.emps_cur; END;
/
ora_server_error: 6511
BEGIN OPEN serial_package.emps_cur; END;
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at "IBAEHIS.SERIAL_PACKAGE", line 4
ORA-06512: at line 1
SQL> BEGIN CLOSE serial_package.emps_cur; END;
/
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE PACKAGE serial_package
AS
PRAGMA SERIALLY_REUSABLE;
CURSOR emps_cur IS
SELECT * FROM employees;
END serial_package;
/
Package created.
SQL> BEGIN OPEN serial_package.emps_cur; END;
/
PL/SQL procedure successfully completed.
SQL> BEGIN OPEN serial_package.emps_cur; END;
/
PL/SQL procedure successfully completed.
SQL> BEGIN CLOSE serial_package.emps_cur; END;
/
ora_server_error: 1001
BEGIN CLOSE serial_package.emps_cur; END;
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 1
July 06, 2010 - 2:17 pm UTC
a serially reusable package instantiates itself once per call - instead of once per session.
Normally, a package instantiates itself the first time you reference it in a session and it remains instantiates (loaded, global state maintained) for the life of the session.
You can deinstantiate the package by using dbms_session.reset_package_state for example - that unloads all packages and makes it appear to plsql as if the session was brand new. When packages are deinstantiated - all global variables disappear, all plsql managed cursors are closed automagically by plsql (just like they are when a cursor goes out of scope, deinstantiation takes everything "out of scope).
Or, if you use serially reusable, then we deinstantiate the serially reusable packages at the end of the call - so everytime you call into the database - we instantiate them - use them - and upon returning deinstantiate them - unload them. All globals "go out of scope" and are freed. All plsql managed cursors (but not dbms_sql cursors!!) are closed for you.