Packages

When a PL/SQL-packaged construct is referenced for the first time, the whole package is loaded
into memory.
You can code and compile a specification without its body.
The package specification may also include PRAGMAs, which are directives to the compiler.
If a package specification does not contain subprogram declarations, then there is no
requirement for a package body.
Variables declared in the package specification are initialized to NULL by default.

definition of package level data: variable,cursor etc declared either in spec or body
but outside the procedure/function.

Package level data is visible only during the user session but across transactions. Eg an open
cursor either defined in package body or spec will remain open for the whole
session and different programs (proc/functions) can access the open cursor. You could open in one program , fetch from second and close in third.

use DBMS_PIPE or Oracle Advanced Queueing to share data across sessions.


Place items in the declaration part of the package body
when you must maintain them throughout
a session or across transactions.
The fine-grain dependency management reduces the
need to recompile referencing subprograms when a
package specification changes.

package variables good example http://www.dba-oracle.com/plsql/t_plsql_global_data.htm

Visibility of Package Components
The visibility of a component describes whether that component can be seen, that is, referenced
and used by other components or objects. The visibility of components depends on whether they
are locally or globally declared.
Local components are visible within the structure in which they are declared, such as:
• Variables defined in a subprogram can be referenced within that subprogram, and are not
visible to external components—for example, local_var can be used in procedure A.
• Private package variables, which are declared in a package body, can be referenced by other
components in the same package body. They are not visible to any subprograms or objects
that are outside the package. For example, private_var can be used by procedures A
and B within the package body, but not outside the package.
Globally declared components are visible internally and externally to the package, such as:
• A public variable, which is declared in a package specification, can be referenced and
changed outside the package (for example, public_var can be referenced externally).
• A package subprogram in the specification can be called from external code sources (for
example, procedure A can be called from an environment external to the package).


Overloading Subprograms
Restrictions
You cannot overload:
• Two subprograms if their formal parameters differ only in data type and the different data
types are in the same family (NUMBER and DECIMAL belong to the same family.)
• Two subprograms if their formal parameters differ only in subtype and the different
subtypes are based on types in the same family (VARCHAR and STRING are PL/SQL
subtypes of VARCHAR2.)
• Two functions that differ only in return type, even if the types are in different families

What are possible side effects of using a stored function in a sql query (related to purity level)
It is important to control side effects because they can prevent the proper parallelization of a
query, produce order-dependent and therefore indeterminate results, or require impermissible
actions such as the maintenance of package state across user sessions.

Add PRAGMA SERIALLY_RESUABLE to the package
specification, then the database stores package variables in the System Global Area (SGA)
shared across user sessions. In this case, the package state is maintained for the life of a
subprogram call or a single reference to a package construct. The SERIALLY_REUSABLE
directive is useful if you want to conserve memory and if the package state does not need to
persist for each user session.

This PRAGMA is appropriate for packages that declare large temporary work areas that are used
once and not needed during subsequent database calls in the same session.
You can mark a bodiless package as serially reusable. If a package has a spec and body, you
must mark both. You cannot mark only the body.
The global memory for serially reusable packages is pooled in the System Global Area (SGA),
not allocated to individual users in the User Global Area (UGA). That way, the package work
area can be reused. When the call to the server ends, the memory is returned to the pool. Each
time the package is reused, its public variables are initialized to their default values or to NULL.
Note: Serially reusable packages cannot be accessed from database triggers or other PL/SQL
subprograms that are called from SQL statements. If you try, the Oracle server generates an
error.

Initializing Packages
1) The block at the end of the package body executes once
and is used to initialize public and private package
variables.
2) Also any package level data (such as number variable or string constant).
package level data are of 2 types , public ie declared in the package spec outside of any subprogram
and private ie declared in the package body outside any stored proc/function.
3) Also default values to variable and constants as specified in their declarations

The first time the package is used/referenced the package data (above 3) is initialized.