Re: PL/SQL constants in packages/stored packages

Consider purchasing the O'Reilly book on PL/SQL programming (the Ants book). It has a exception handler package in it that is also on the diskette included with the book. We are using it with success.

quadtwin_at_aol.com (QuadTwin) wrote:

>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