Re: Resetting state of PL/SQL package

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Tue, 21 Jan 2003 16:34:36 GMT
Message-ID: <wEeX9.1263$Iu2.650@newssvr19.news.prodigy.com>

Vladimir M. Zakharychev wrote:
> "Karsten Farrell" <kfarrell_at_belgariad.com> wrote in message
> news:DPZW9.42$Lo2.2_at_newssvr19.news.prodigy.com...
>

>>Do you just want to allow any session to *set* a package variable and
>>allow any session to *see* the current value of that variable? If so,
>>can you provide procedures/functions in the package to:
>>
>>set_variable(new_value);
>>cur_value := get_variable;
>>
>>Then grant execute on the package to the world.
>>
>>As long as you don't close the session that's running the package, other
>>sessions can use the get/set routines.

>
>
> Hmm... Don't think it works this way - every session gets its own instance
> of the package and they cannot interact directly.
>
> --
> Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
> All opinions are mine and do not necessarily go in line with those of my employer.
>

That's true - every session gets its own instance of the package. But I can request information from another session's package ... I just can't change its variables directly. The sending and requesting packages can be on a different sessions, schemas, databases, or hosts.

For example, I once wrote a package that sent back or modified info about an Oracle user's account - stuff like their roles or password. A copy of the package ran on each instance/database. Suppose, for example, I entered the information (such as a new password) while connected to DB_ONE, then called each of the "remote" packages on DB_TWO, DB_THREE, and so on. They each updated the account on their respective local database. So not only was I able to enter the info once, but I was able to affect databases thru db links. I could also request, and receive, account info from these remote databases. I just couldn't directly update any package variables remotely. And the packages had to be running all the time or the whole thing would fall apart.

That was in Oracle7 days ... and there are better ways to do it with the more recent versions. But that was a simple solution to allow me to enter the info once and not have to connect to each database and enter the same ALTER USER command multiple times. Received on Tue Jan 21 2003 - 10:34:36 CST