Re: PL/SQL application wide state

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 30 Sep 2003 23:24:16 -0700
Message-ID: <1a75df45.0309302224.75b1c692@posting.google.com>

roger <xrsr_at_rogerware.com> wrote

> Is there not some way to read these configuration values into some
> sort of persistent application wide memory cache?

Yes. As Sybrand and Daniel indicated, that is exactly what tables are their for. But they are butt ugly when dealing with parameters, right?

Solution: PL/SQL Object wrappers. Makes the implementation many times neater and flexible. Instead of dealing with SQL and tables, you deal with an object class called something like TParamater, e.g. very simplictically

create or replace type TParameter as object (

    name      varchar2(20),
    value     varchar2(255),

    constructor function TParameter( name$ varchar2 )
        return self as result,

    member function AsDate return Date,
    member function AsBoolean return Boolean,     member function AsInteger return Number,     member function Exist return Boolean,     member procedure Save
)  

I leave the body type to your imagination. :-)

The PL/SQL will look someting like this:

declare
  p$ TParameter;
begin
  p$ := NEW TParameter('LAST PROCESS RUN');   

  if p$.AsDate < blah blah
    .. etc..
end;

The second solution is using the namespace feature of Oracle called context. A context contains name-value pairs. A context is much like a Unix/NT environment containing environmental variables. What is really nifty is that you can bind a single PL/SQL procedure to that context to populate it. Thus, no one can change the context - they have to run the procedure (aka read-only .profile/autoexec.bat) to populate the context with name-value pairs. This makes the context trusted and a great tool for implementing security (like Fine Grained Access Control).

Again - this can (and should) also be wrapped in PL/SQL OO wrappers. Making the actual implementation of the parameters (as tables or as namespace) totally transparent to the PL/SQL developer.

--
Billy

Received on Wed Oct 01 2003 - 01:24:16 CDT