I'm a wonderin...

I have an application that will implement key business logic in PL/SQL,
and uses various configuration values, as stored in various database
tables, to control certain aspects of it's operations.

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

Like, if I were writing a C++, java, or C# application, I'd read the
various configuration tables at startup, (or on first use), and populate
a single application wide cache that would be used subsequently.


Is there no corresponding mechanism with PL/SQL?


Packages provide each individual session with it's own copy of whatever
package variables are defined, and that's a good thing, but it seems like
there needs to be some other intra-session store that can be used on
an application wide basis. No?


Do I have to re-read the tables each time? Maybe this is OK since PL/SQL
runs in the server, but it still seems wasteful. Can certain tables be
"pinned" or something like that?


I could use package variables, but this would duplicate the values for
each user session. Worse, it would be very much more complicated to
resyncronize these many caches in the event that the data is updated
(rare - but can happen)


Thanks for any thoughts and suggestions


roger

Comments embedded....


On Mon, 29 Sep 2003 17:29:45 GMT, roger <xrsr (AT) rogerware (DOT) com> wrote:

Sure, it's called the database buffer cache


We are talking databases here, not some crap 3GL
There is not. Tables are an intra-session store.

Yes, but your records are in the buffer cache, so no physical I/O
performed

Maybe this is OK since PL/SQL
You can configure the keep buffer pool and put them in the keep buffer
pool on 8.0 higher.


You are on your way to reinvent the wheel and you will fail miserably.
You seem to think I/O is bad, and that the buffer cache doesn't exist.




Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

Sybrand Bakker <gooiditweg (AT) sybrandb (DOT) demon.nl> wrote in
news:hh3hnvcltbqmtphvdgh4ft364k1uuim5b5 (AT) 4ax (DOT) com:

Not at all. That's why I post the question before proceeding.

Performance aside, the other reason one might wish to extract
these table-resident values, rather than re-select them every time,
they are needed, would be to provide a more convenient and robust
API for using them.

OOps, that must be my "crap 3gl" background showing through.
Sorry, I'll have to try and remember to shut off that half
of my brain when posting here in the future.
Seems a pity.

roger wrote:

Oracle's system triggers that
can be used to execute code and pin it in memory on startup and Oracle's
package initialization
section that, used in conjunction with the triggers, can maintain
persistent memory in variables during a
session.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Daniel Morgan <damorgan@x.washington.edu> wrote in news:1064877684.182592
@yasure:

System triggers - I get the idea on.
How they help me here is a bit more elusive...

"Execute code and pin it in memory"

Let's see. I guess you mean some PL/SQL code that I would
supply, and that what gets pinned in memory would not be
the code, but whatever my code would want to put there.
Right?

So, what's that mechanism then? Buffer caches as mentioned
previously? Or is there some other mechanism available here?
About all I can find is the CACHE clause for create table,
which says

For data that is accessed frequently, this clause indicates that the
blocks retrieved for this table are placed at the most recently used
end of the least recently used (LRU) list in the buffer cache when a
full table scan is performed. This attribute is useful for small
lookup tables.


That sounds like a good start and easy enough to do.
Is there anything more clever/subtle that I should be
looking for here?
(it's that old needle/haystack thing again)


I said previously that I didn't think package variables,
which I understand to be per-session, were appropriate
for my purposes.
Were you suggesting that there is yet some way to use
package variables to share state across sessions?


Thanks.

roger wrote:

See globally accessed application context in the documentation. You can
set these up on a user and/or client basis or across all users. They
are designed to be secure by specifying a single package or procedure
that can modify their state but you can specify sys.dbms_session to
essentially make them unsecure. For example ...

create context my_context using sys.dbms_session accessed globally;
dbms_session.set_context('MY_CONTEXT', 'MY_ATTRIBUTE', 'MY_VALUE');

then anytime anybody connects you can use the SYS_CONTEXT function to
retrieve values ...

value := sys_context('MY_CONTEXT', 'MY_ATTRIBUTE');


As I said, you can also make them user and client specific (see the
documentation for more information). Have a look at the GLOBAL_CONTEXT
system view as well.


Richard Kuhler

roger <xrsr (AT) rogerware (DOT) com> wrote

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