Geek Answers Handbook

When is code running between the start / end block of a PL / SQL package?

I have PL / SQL code similar to the following snippet:

create or replace package body MY_PACKAGE as type array_type is table of char index by varchar2(1); lookup_array array_type; function DO_SOMETHING(input nvarchar2) return varchar2 as begin -- Do something here with lookup_array end DO_SOMETHING; procedure init_array as begin lookup_array('A') := 'a'; lookup_array('B') := 'b'; -- etc end init_array; begin init_array; end MY_PACKAGE;

It uses a static search array to process the data supplied by DO_SOMETHING. My question is: when is init_array and lookup_array loaded into memory? When is the package compiled? When is it called for the first time? Is this called more than once? Is there a better way to implement a static search array?

Thanks!

+4
source share
2 answers

You can refer to this link: http://www.dba-oracle.com/plsql/t_plsql_lookup_tables.htm

"This means that the procedure is executed during the initialization of the package. As a result, during the life of the session, the procedure is never called manually unless updating the cached table is required."

+4
source

Q1. "When is init_array called and lookup_array loaded into memory? When is the package compiled? When is it called for the first time? Is it called more than once?"

init_array is called when any functions or procedures in the package are called - i.e. "just in time". It will be called whenever the state of the packet is lost (i.e., it can be called more than once per session).

This has consequences for a scenario where the state of a package is lost - for example, when someone recompiles a package. In this case, the following sequence occurs:

  • First, the do_something call is called - init_array , then do_something is executed - your session now has some memory allocated in PGA to store the array.

  • My session recompiles the package. At this point, your session memory allocated for this package is marked as "invalid."

  • Your do_something session calls - Oracle detects that your session memory is marked as invalid and problems ORA-04061 "existing xxx state was invalid."

  • If your session calls do_something again, it runs without errors - it first calls init_array and then does do_something .

Q2. "Is there a better way to implement a static search array?"

I do not see any real problems with this approach as long as you take into account the behavior described above.

In some cases, I saw people launch an init call at the beginning of every function / procedure that needs an array - i.e. whenever do_something is called, it checks to see if it needs to initialize, and if it calls init_array . The advantage of this approach is that you can only configure init_array to initialize the bits that this function / procedure needs, which can be beneficial if init_array does a lot of work, which can help avoid a lot of one-time startup overhead for each session.

+3
source

More articles:


All Articles