Re: Restricting a Package to one instance.

Yes, I could store it in a table. I just wasn't aware that packages did not share a common state, and I've been designing an important one with that belief.

My first idea is to use a table. But I am wondering if two users start the procedure at the same time...I guess a SELECT FOR UPDATE should handle that, and I can CACHE the entire table in memory. It should have very few rows, with very few columns. I doubt I'd even notice.

I just assumed that there would be a cleaner way of doing it. That is, that the package knows its own state.

Brian

On Wed, 26 Sep 2001 02:16:57 GMT, "Brandon" <oracle.admin_at_home.com> wrote:

>Couldn't you store state information in a table? If speed is an issue then
>maybe that isn't option but I am not aware of any other way this could be
>done effectively within Oracle since each call to a procedure is per session
>and not per instance.
>
>Brandon
>
>"Brian Tkatch" <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK> wrote in
>message news:3bb0f037.3046000625_at_news.alt.net...
>> I have a package that, as a requirement, may not have multiple
>> instantiations. Further, multiple sessions must be able to check its
>> state (which can be via a function that returns various variables from
>> the package).
>>
>> Is there a way to:
>>
>> 1) make a package have only one instantiation?
>> 2) for mutiple sessions (by multiple users) see a common state?
>>
>> On another note, once the package starts, away it goes looping. It
>> stops in one of two cases. When the loop iterates:
>>
>> 1) it checks a package variable to see if it should continue.
>> 2) it retrieves a value from a queue. When empty, it stops.
>>
>> I wanted to add a third method. This would be a forced stop. I am
>> assuming to force a stop on a procedure (E.G. in middle of doing an
>> UPDATE that will take a half-hour) its session would need to be
>> killed.
>>
>> 1) Is killing it the only way?
>> 2) Is there a way for a procedure to get its session id?
>> 3) Can a procedure be killed and keep the state of its package?
>>
>> Brian
>>
>
>
Received on Wed Sep 26 2001 - 08:55:03 CDT