Re: Caching within packages..

On Tue, 09 Dec 2003 22:43:37 GMT, andreyNSPAM_at_bookexchange.net (NetComrade) wrote:

>Either one of o'reilly books, or some other book, talked about how if
>you declare a function/procedure as part of a function, you can cache
>objects in an array for the life of a session, so u don't have to make
>a trip to a table again.. anyone remembers which one?
>
>Thanks
>.......
>We use Oracle 8.1.7.4 on Solaris 2.7 boxes
>remove NSPAM to email

Just declare the PL/SQL table either in the package specification outside any procedure or in the pl/sql body outside any procedure. However, of course your approach is going to contradict the transaction model, if you 'cache' table contents and the table changes your application won't see it. If you need this approach which should be considered a workaround, you are probably in bigger trouble, as you seem to think symptom-fighting will solve anything.

--
Sybrand Bakker, Senior Oracle DBA