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.)
>
> Many thanks.
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Hmmm.. This worked fine for me. Here is what I did
dev_12> CREATE PACKAGE const AS
2 max_widgets_per_day CONSTANT INTEGER := 1000;
3 END const;
4 /
Package created.
dev_12> l
1 create or replace procedure bobproc as
2 widgets number := 1100;
3 begin
4 if widgets > const.max_widgets_per_day then
5 dbms_output.put_line('Greater');
6 else
7 dbms_output.put_line('Less than');
8 end if;
9* end;
dev_12> /
Procedure created.
dev_12> execute bobproc
Greater
When I changed the value of widgets to be lower than 1000, I did get the 'Less than' message.
I am running Oracle 7.3.2.3 on a Solaris 2.5 machine.
-- Bob Swisshelm Eli Lilly and Company swisshelm_at_lilly.comReceived on Wed Jul 16 1997 - 00:00:00 CDT