Re: Using dbms_alert within SQL*FORMS
Date: 1995/08/21
Message-ID: <1995Aug21.064145.128_at_dde.dk>#1/1
peters_at_mitre.org (Peter Sylvester) writes:
>I'm interested in being able to "catch" an alert from within SQL*FORMS.
>The alert (signasl) would be set off from a table trigger, and I would like
>this to "trigger" an update to a status field.
>Is this sort of thing possible, and how might it be accomplished?
Yes it is indeed possible, you would have to create a timer in Forms though,
to __see__ if any alerts are there to be cought.
Assume a trigger have been placed to catch changes to the salery column of the 'emp' table, like this:
create or replace trigger emp_sal_change
after insert or delete or update of sal on emp
for each row
declare
string Varchar2( 80 );
begin
if updating then
string := 'Updating emp '||To_Char( :new.empno )||
', old sal: '||To_Char( :old.sal )||' changed to: '||To_Char( :new.sal );
else
if inserting then
string := 'Inserting emp '||To_Char( :new.empno )||', new sal: '||To_Char( :new.sal );
else
string := 'Deleting emp '||To_Char( :old.empno )||', old sal: '||To_Char( :old.sal );
end if;
end if;
dbms_alert.Signal( 'emp_sal_alert', string );
end;
It is certainly not good programming style to include language dependent
text in the messages, but as an example ...
Note that the trigger will signal an alert called 'emp_sal_alert' with
a proper message.
Note also that alerts instead of pipes are used since we would only like to get
the signal if the transaction changing the salery column actually commits.
A client (forms) may now show interest in this specific RDBMS alert with
the 'register' call.
We also need a timer to check is an alert has arived.
Unfortunately we would have to do this in a polling fassion, since
there is no concept of 'threads' in Oracle Forms.
It will however not be very expensive to do the polling, since only little
database activity is involved.
The 'when-new-form-instance' trigger may look like this:
declare
timer_id Timer;
begin
dbms_alert.Register( 'emp_sal_alert' );
timer_id := Create_Timer( 'CHECK_TIMER', 10000, REPEAT );
if Id_Null( timer_id ) then
Message( 'Error creating the CHECK_TIMER timer' );
end if;
end;
.S +4 D
Now when the 'CHECK_TIMER' timer expire, we need to see if
any 'emp_sal_alert' alerts are pending,
so we set the timeout parameter to 0.
Note that the 'Waitany' procedure is used to allow for other alerts as well.
The 'when-timer-expired' trigger may look like this:
declare
alert_message Varchar2( 2000 );
alert_name Varchar2( 2000 );
status Number;
alert_id Alert;
alert_result Number;
begin
:global.timer_name := Get_Application_Property( TIMER_NAME );
if :global.timer.name = 'CHECK_TIMER' then
dbms_alert.Waitany( alert_name, alert_message, status, 0 );
if status = 0 then
alert_id := Find_Alert( 'EMP_SAL_ALERT' ); if Id_Null( alert_id ) then Message( 'Alert EMP_SAL_ALERT does not exist!' ); else Set_Alert_Property( alert_id, ALERT_MESSAGE_TEXT, alert_message ); alert_result := Show_Alert( alert_id ); end if;
end if;
else
Message( 'Another timer did expire: '||:global.timer_name ); end if;
end;
Hopes this clarifies things.
Best wishes - Martin Jensen
>Thanks for any suggestions.
>--
>Peter Sylvester
>MITRE Corp.
>Bedford, MA
>(peters_at_mitre.org)
-- Martin Jensen, Dansk Data Elektronik A/S, Herlev, Denmark E-mail: mj_at_dde.DK --- Don't take life too seriously -- you'll never get out if it alive.Received on Mon Aug 21 1995 - 00:00:00 CEST