A reader, December 08, 2001 - 12:46 pm UTC
How does it work like this way?
Sean Bu, December 08, 2001 - 11:38 pm UTC
December 09, 2001 - 9:37 am UTC
sorry, that comment does not compute -- what do you mean.
Sean Bu, December 09, 2001 - 5:59 pm UTC
I wanted to say, how Oracle only executes one block instead of the two in the procedure as the user exec a more than once?
December 09, 2001 - 7:47 pm UTC
The anonymous block in the package is "startup" code.
By design, by the documented behavior, this code runs ONCE upon the initialization of the package. Only the first time you touch the package in your session will this code run.
Wonderful
Pullaiah Yetagella, December 09, 2001 - 8:28 pm UTC
Clever
Jim, December 10, 2001 - 7:08 am UTC
Pretty tricky Tom....
Might throw that one in for an interview question
I wonder how many people would get a question about when
are both dbms_output executed ?
A reader, December 10, 2001 - 5:00 pm UTC
Nifty.
Kashif, November 04, 2002 - 10:54 am UTC
Way cool. Great way to initialize pl/sql tables/arrays as well. Any performance considerations on this? Thanks.
Kashif
November 04, 2002 - 11:04 am UTC
no, not really - other then of course the code will run and if the code is slow.... the first hit on the package will be slow.
Nothing wrong with calling its own procedure in init ?
Robert, November 04, 2002 - 1:47 pm UTC
Tom, there is nothing wrong, logically or technically, with
running "foo" in initialization like below ?
create package body foo
as
procedure foo
.... ;
BEGIN
.... block of code to be run the first time the package
.... is referenced in a session
foo;
end;
/
November 05, 2002 - 8:44 am UTC
no problem.
ops$tkyte@ORA920.US.ORACLE.COM> create or replace package demo_pkg
2 as
3 procedure p;
4 end;
5 /
Package created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create or replace package body demo_pkg
2 as
3
4 procedure foo
5 is
6 begin
7 dbms_output.put_line( 'First time!!!' );
8 end;
9
10 procedure p
11 is
12 begin
13 dbms_output.put_line( 'p to the rescue' );
14 end;
15
16 begin
17 foo;
18 end;
19 /
Package body created.
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> exec demo_pkg.p
First time!!!
p to the rescue
PL/SQL procedure successfully completed.
ops$tkyte@ORA920.US.ORACLE.COM> exec demo_pkg.p
p to the rescue
PL/SQL procedure successfully completed.
Bhagat Singh, November 04, 2002 - 10:32 pm UTC
Simply great.......
Big problem solved in ticks. Excellent.
Error
priyaramnan2k2, February 28, 2003 - 7:27 am UTC
i am getting an error what shall i do ??
create or replace package pkg_samp as
procedure a;
end;
/
create or replace package body pkg_samp as
procedure a
begin
null;
end;
end;
/
Warning: Package Body created with compilation errors.
SQL> show error
end;
*
ERROR at line 6:
ORA-00942: table or view does not exist
exec pkg_samp.a;
begin pkg_samp.a; end;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "CMKTG.PKG_SAMP" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
February 28, 2003 - 10:06 am UTC
get your dba to correctly install your database. If you get a ora 942 on show errors, that sort of means that your database isn't quite right.
Also -- one would expect a "Package Created" message after the create or replace package pkg_same command - so, i would guess the error is that you are somehow improperly submitting the code -- so that sqlplus if that is what you are using is seeing the entire
create or replace package pkg_samp as
procedure a;
end;
/
create or replace package body pkg_samp as
procedure a
begin
null;
end;
end;
/
as a single piece of code -- perhaps you have a blank in column one or something so that the first / is not actually running that create statement.
Part of a job
Pat, October 06, 2003 - 11:51 am UTC
Can this work as part of a package that is run by a dbms job (the job running say every 5 seconds ) ?
You said "Only the first time you touch the package in
your session will this code run. " , therefore how will a sheduled job handle this ?
Thanks
October 06, 2003 - 1:41 pm UTC
the package states are reset, the entire session state is reset, after a job is run. consider:
ops$tkyte@ORA920> create table msg( txt varchar2(80) );
Table created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace package foo
2 as
3 procedure bar;
4 end;
5 /
Package created.
ops$tkyte@ORA920> create or replace package body foo
2 as
3 procedure bar
4 is
5 begin
6 null;
7 end;
8 begin
9 insert into msg values ( 'package loaded at ' || systimestamp );
10 end;
11 /
Package body created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> variable n number
ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from msg;
no rows selected
ops$tkyte@ORA920> exec dbms_job.submit( :n, 'foo.bar;', sysdate, 'sysdate+1/24/60/60' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA920> commit;
Commit complete.
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec dbms_lock.sleep(60)
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from msg;
TXT
--------------------------------------------------------------------------------
package loaded at 06-OCT-03 01.37.53.818367 PM -04:00
package loaded at 06-OCT-03 01.37.59.024645 PM -04:00
package loaded at 06-OCT-03 01.38.04.132548 PM -04:00
package loaded at 06-OCT-03 01.38.09.253830 PM -04:00
package loaded at 06-OCT-03 01.38.14.374901 PM -04:00
package loaded at 06-OCT-03 01.38.19.495797 PM -04:00
package loaded at 06-OCT-03 01.38.24.616762 PM -04:00
package loaded at 06-OCT-03 01.38.29.738252 PM -04:00
package loaded at 06-OCT-03 01.38.34.859002 PM -04:00
package loaded at 06-OCT-03 01.38.39.980311 PM -04:00
package loaded at 06-OCT-03 01.38.45.101583 PM -04:00
package loaded at 06-OCT-03 01.38.50.222504 PM -04:00
12 rows selected.
ops$tkyte@ORA920>
ops$tkyte@ORA920> exec dbms_job.remove( :n );
PL/SQL procedure successfully completed.
<b>see how the package startup code</b> was run over and over again...
Sub Procedures
Mohan, November 03, 2003 - 2:46 am UTC
A friend asked me this question, whether it is possible to create a sub function/program inside a function/program like in Pascal.
Mohan
November 03, 2003 - 7:16 am UTC
yes
create procedure p1
as
procedure p2
is
begin
...
end;
begin
p2;
end;
/
Memory issues when populating arrays in the package init
Stephane, November 26, 2003 - 9:28 am UTC
Hi Tom,
I've been using the package init section in some of my packages to populate some associative arrays for things like my gui text snippets, things that are retrieved on every page call... to avoid context switches between PL/SQL and SQL, kind of like a data cache I guess (a typical PSP page can lookup the array 10 - 15 times) I bulk collect the data.
We're probably dealing with about 100K of data in total...
We're running 9iAS (9.0.3) and 9iR2 database on AIX. My concern is regarding memory. I read in this thread that the package init code is run the first time the package is invoked in a session. Is there a chance we run into a situation with our configuration where we may use up all available memory? How are sessions handled in a stateless web app when dealing with multiple concurrent users? Is there a standard ratio, ie 10 sessions for every 1000 users.. is this something that can be controlled?
Thanks for the info
November 26, 2003 - 10:38 am UTC
of course you can use up all of your memory -- memory is a finite resource.
I cannot answer the last question as it depends entirely on the technology used to connect to the database (using a java connection pool? mod_plsql? something else?) and how ACTIVE you are. Asktom (which runs mod_plsql 100% and supports many applications) does about 5,000 'concurrent' users (if you look at a one hour window), but:
sys@ASKUS> select * from v$license
2 /
SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER USERS_MAX
------------ ---------------- ---------------- ------------------ ----------
0 0 49 79 0
at most, I have had 80 sessions since our last bounce 6 weeks ago. Other systems might have more, others less.
Thanks
Stephane, November 26, 2003 - 12:05 pm UTC
Sorry, I forgot to include some important details. We are using mod_plsql 100% as well... no JAVA (trying to keep it simple)
I don't think we'll ever have as many concurrent sessions as asktom... so I think we'll be ok.
I'll take a look a the view and see what we get...
Thanks
Pros &* Cons of Init
thiru, March 09, 2005 - 4:46 pm UTC
Tom,
If a package is not initialized and this package is touched/accessed frequently, what is the downside? Could that lead to performance issues?
March 09, 2005 - 6:20 pm UTC
not sure what you mean
A reader, March 10, 2005 - 9:43 am UTC
Let's say a package is accessed frequently by an application. And that package has not been initialized as pointed out in this thread. My question then is : what's the impact on performance? In other words, what are the benefits of initializing a package?
create package body test_pkg
is
begin
...Procedures and Functions here..
end;
begin
--Initializing
null; ( if this step is omitted, what is the effect ?)
end test_pkg
Thanks.
March 10, 2005 - 7:08 pm UTC
there is no effect. if the code is there, it is run the first time, if it is not -- nothing happens.
I know Intialization
Pranav Shah, June 22, 2006 - 9:27 am UTC
My question is there an initialization, such that "Procedure1" will always be called first, no matter what the call is. I know it is somewhat stupid, but I am trying to create a log for a Package. I waould like to separate the calls, if possible. The Package might be in chace when the user ask for the same Procedure with different parameters. Example:
Pkg1.Pr2('a', 'b')
--Some process
Pkg1.Pr2('x', 'y')
-- some process.
The idea is to log these as individual instances and obtain a unique sequence number. I don't want to ask a new sequence number inside the procedure, because that could be called by a different procedure and would not link everything together.
I hope my question makes sense
June 22, 2006 - 2:29 pm UTC
does not make sense, not sure what you mean - what the context is here.
I know Intialization- Followup
Pranav, June 23, 2006 - 10:26 am UTC
The reason for calling a particualr Procedure(A) everytime anything is called is for logging purposes. How much time the entire task took, and how much time did each procedure take, etc.
I can put code in the intialization section and I will know that UserA called the package and performed some action using variables x, y, z. If I ask for a Sequence No, I get 1.
However, once UserA finishes the task and now issues the same command with variables a, b, c, the Intializtion code won't be called, because the package is still in cache(or am I wrong to assume that) so I will not get a second sequence number, and to take it a step further, assume that he calls the same package but instead of starting with Procedure(A) UserA starts with Procedure(B) and so on.
Am I just confusing myself too much or is there a better way of keeping logs of a task/application.
--Thanks
June 23, 2006 - 1:13 pm UTC
this is called instrumentation and we need to put this in the code to be instrumented.
Every other line of really good code is "our code". The other code does stuff for those end users. I only care about the code that tells me what the code is doing...
Will it be initialed once with connection pooling?
Tom, September 16, 2007 - 3:32 pm UTC
Will it be initialized once with connection pooling?
I have read this section and have a concern about package initializations and web connections. I want to add debug/tracing control in a package initialization. I would like for the initialization section to be executed with every call from the web API to my package. I was told that since it is setup as connection pooling that my initialization would only execute the first time and my variables would be stuck until the connection is completely broken from the database. Is this true? Is there any way around this? I have a .net API that does connection pooling and I would like to be able to turn on and off debug by using a query in the initialization that selects from a debug flag table.
/*
||Package initialization
*/
BEGIN
SELECT trace_flag
, log_flag
INTO log_is_on_g
, trace_is_on_g
FROM application_debug;
END;
Code is called 100K times an hour.
September 18, 2007 - 12:45 pm UTC
the initialization code is invoked once per Oracle session - or until the package is physically removed from the session (recompiled or a call to dbms_session.reset_package).
OR, you can use a global application context
ops$tkyte%ORA10GR2> create or replace context my_debug_context using my_procedure accessed globally
2 /
Context created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure my_procedure( p_nam in varchar2, p_val in varchar2 )
2 as
3 begin
4 dbms_session.set_context( 'my_debug_context', p_nam, p_val );
5 end;
6 /
Procedure created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec my_procedure( 'trace_flag', 'this is trace flag' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> exec my_procedure( 'log_flag', 'this is log flag' );
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
2 dbms_output.put_line( sys_context( 'my_debug_context', 'trace_flag' ) );
3 dbms_output.put_line( sys_context( 'my_debug_context', 'log_flag' ) );
4 end;
5 /
this is trace flag
this is log flag
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
2 dbms_output.put_line( sys_context( 'my_debug_context', 'trace_flag' ) );
3 dbms_output.put_line( sys_context( 'my_debug_context', 'log_flag' ) );
4 end;
5 /
this is trace flag
this is log flag
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> host sqlplus /
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Sep 18 12:26:37 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ops$tkyte%ORA10GR2> exec my_procedure( 'trace_flag', 'this is NEW TRACE!!!!' )
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
2 dbms_output.put_line( sys_context( 'my_debug_context', 'trace_flag' ) );
3 dbms_output.put_line( sys_context( 'my_debug_context', 'log_flag' ) );
4 end;
5 /
this is NEW TRACE!!!!
this is log flag
PL/SQL procedure successfully completed.
Just have the package initialization code check to see if the context is NULL and if so, load it from the table - then just reference sys_context
and to change the value, have a procedure that updates the row and resets the context - other sessions will see it change.
pragma SERIALLY_REUSABLE
Gints Plivna, November 17, 2007 - 5:11 pm UTC
Will it be initialized once with connection pooling?
I have read this section and have a concern about package initializations and web connections. I
want to add debug/tracing control in a package initialization. I would like for the initialization
section to be executed with every call from the web API to my package.Isn't pragma SERIALLY_REUSABLE
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/seriallyreusable_pragma.htm#LNPLS01346 just for this case?
Excerpt:
The pragma SERIALLY_REUSABLE indicates that the package state is needed only for the duration of one call to the server. An example could be an OCI call to the database or a stored procedure call through a database link. After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.