Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL constants in packages/stored packages
dpcaldwell_at_sherwin.com wrote:
> Hello all.
>
> I have been attempting to store systemwide constants in a stored
> PL/SQL package. My first attempt was the following:
>
> CREATE PACKAGE const AS
> max_widgets_per_day CONSTANT INTEGER := 1000; END const;
>
> Application
> IF widgets > const.max_widgets_per_day THEN
> ...
>
> ***
> But this doesn't work. I get an "unknown column" error when
> accessing const.variable_name. In order to preserve this
> nomenclature, I wrote one-line functions, i.e.
>
> CREATE PACKAGE const AS
> FUNCTION max_widgets_per_day RETURN INTEGER;
> PRAGMA RESTRICT_REFERENCES (max_widgets_per_day, WNDS); END;
> CREATE PACKAGE BODY const AS
> FUNCTION max_widgets_per_day RETURN INTEGER IS BEGIN RETURN 100;
> END; END;
>
> ***
> A whole new class of systemwide constants is about to be created
> corresponding to error codes, so I am revisiting the issue: is there
> any way to do this using the first nomenclature, rather than the
> second? (I haven't tried it, but as I understand it the first method
> works for non-stored packages, just not for stored ones.)
>
No, at least not yet. You do not mention what tools you are using with
these
packages, but I am assuming you are using Developer/2000 FORMS and
REPORTS.
A FORMS workaround is to create a client side package (by the same name,
if you
like) which contains the constant. Put the package in a client-side
library (i.e. a
PLL).
From the client package, call the server packaged function to initialize
the
value of the constant and to deal with any error conditions raised by the
server.
In Dev/2000 V2 you can directly reference the constant from a client Form,
but
the approach I suggest above is probably better anyway. A constant
defined in a
client package has the lifetime of the Form for which it is loaded.
Therefore,
only one server call need be peformed per Form that requires the constant.
I have found it a good idea to hide server-side packages with client-side
packages
in a PLL. This limits the exposure to the server package among Forms and
gives you
some flexibility to change the implementation without affecting dependent
Forms.
Al
Al Lawson
Interactive Group, Inc.
www.interactive-group.com
lawsona_at_interactive-group.com
Received on Wed Jul 30 1997 - 00:00:00 CDT