Initializing Public Variables

David Clement

September 2004

Oracle versions 7-8

One of the advantages of using packages is that a package can serve as an interface to a library of values, roughly the same way a header can do in a C or C++ program. The values can be constants or can be set in the initialization section of a package, which can save considerable overhead. It's possible to eliminate dozens of references to trunc(sysdate) by referencing that function once.

In PL/SQL, a constant is assigned its value in its declaration, as follows.

create or declare package p
as
    d constant date := trunc(sysdate);
end p;

This constant will retain the same value from the first time the package is referenced to the end of the session.

The initialization section of a package is an anonymous PL/SQL block at the bottom of the package body, emphasized in the following code.

create or declare package p 
as 
    d date;
end p;

create or declare package body p
as 
begin
        d := trunc(sysdate);
end p;

The first time a session refers to this package, the variable is initialized, and it retains its value for the rest of the session if there is no assignment to it.

In either case, since the variable has been declared in the package specification, it is a publicly available element of the package and can be referred to with its package name:


begin
    dbms_output.put_line(to_char(p.d));
end;