Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL constants in packages/stored packages
dpcaldwell_at_sherwin.com wrote:
>
> 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.)
>
I have run into this problem with SQL*Forms 3 applications. The PL/SQL compiler does not "see" constants (or exceptions) defined within package specifications. Your solution is the one I use except I keep the constants:
PACKAGE const IS
kMaxWidgetsPerDay constant integer := 1000;
FUNCTION MaxWidgetsPerDay return integer;
END const;
The body of MaxWidgetsPerDay consists only of RETURN kMaxWidgetsPerDay;
This way, when I have finished converting all my old SQL*Forms 3 apps to Delphi or what have you, I can delete the functions and remove the "k" that precedes the constant names and everything should work fine.
There are other inconsistancies between SQL*Forms 3 and packages, one being an inability to handle Boolean values from package functions. For instance, a package contains the following function:
FUNCTION IsLegal ( ... ) RETURN Boolean;
In the application, the following compiles and seems to work:
IF PackageName.IsLegal( ... ) THEN ...
However, the IF always detects TRUE no matter what the function returns. A big fat pain in the hmmhmm! My work-around:
FUNCTION IsLegal ( ... ) RETURN char;
and
IF PackageName.IsLegal( ... ) = 'Y' THEN ...
If anyone has any better answers to *this* problem, I would like to hear it.
-- Tomm Carr -- "Can you describe your assailant?" "No problem, Officer. That's exactly what I was doing when he hit me!"Received on Wed Jul 16 1997 - 00:00:00 CDT