Why Serially Reusable Packages?

Because the state of a non-reusable package persists for the lifetime of the session, this locks up UGA memory for the whole session. In applications, such as Oracle Office, a log-on session can typically exist for days together. Applications often need to use certain packages only for certain localized periods in the session and would ideally like to de-instantiate the package state in the middle of the session, after they are done using the package.

With SERIALLY_REUSABLE packages, application developers have a way of modelling their applications to manage their memory better for scalability. Package state that they care about only for the duration of a call to the server should be captured in SERIALLY_REUSABLE packages.

A package that is marked SERIALLY_REUSABLE has the following properties:

• Its package variables are meant for use only within the work boundaries, which correspond to calls to the server (either OCI call boundaries or PL/SQL RPC calls to the server).

  Note: If the application programmer makes a mistake and depends on a package variable that is set in a previous unit of work, then the application program can fail. PL/SQL cannot check for such cases.

• A pool of package instantiations is kept, and whenever a "unit of work" needs this package, one of the instantiations is "reused", as follows:
  • The package variables are reinitialized (for example, if the package variables have default values, then those values are reinitialized).
  • The initialization code in the package body is run again.
• At the "end work" boundary, cleanup is done.
  • If any cursors were left open, then they are silently closed.
  • Some non-reusable secondary memory is freed (such as memory for collection variables or long VARCHAR2s).
  • This package instantiation is returned back to the pool of reusable instantiations kept for this package.
• Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, then Oracle Database generates an error.