Re: automatic update of a variable in pl/sql

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Sat, 26 Mar 2005 09:26:34 -0800
Message-ID: <1111857780.253919@yasure>

Mark C. Stock wrote:

> "DA Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1111593806.118041_at_yasure...
>

>>Laura Martignas wrote:
>>
>>
>>>Hi,
>>>
>>>In PL/SQL, is it possible to declare a variable that will be updated each 
>>>time a field of a table will have its value changed ?
>>>
>>>For example, the attribute %TYPE update the type of a variable each time 
>>>the type of a field is changed. Is it possible to have the same thing not 
>>>for the type of the field, but for its value ?
>>>
>>>Thanks
>>>Laura
>>
>>Not in the variable's declaration but the result can be accomplished by
>>a different mechanism.
>>
>>First create a global variable by defining it in a package header and
>>write a function in the package body that updates it.
>>
>>Then write an AFTER INSERT OR UPDATE trigger on the table setting the
>>variable when the trigger fires and no exception is raised.
>>
>>If you do this test it carefully as there may be issues related to your
>>implementation.
>>-- 
>>Daniel A. Morgan
>>University of Washington
>>damorgan_at_x.washington.edu
>>(replace 'x' with 'u' to respond)

>
>
> Daniel,
>
> That was my first thought, but that approach is limited to changes made by
> the current session only, as the trigger would be writing to the package
> variable of the session that issues the DML.
>
> I was thinking DBMS_ALERT would be a more likely candidate for this type of
> functionality -- but then that could get messy with the waits.
>
> Perhaps one approach would be to always access the variable via a function,
> which checks for the event (without waiting) before returning the value.
> With a little forethought this approach could be implemented in a fairly
> generic way (perhaps even via a single overloaded package function).
>
> ++ mcs

Or certainly using Advanced Queuing but that would be a lot of work for something that may be better handled with reconsidering the design.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Sat Mar 26 2005 - 11:26:34 CST