Re: Using dbms_alert within SQL*FORMS

From: Martin Jensen <mj_at_dde.dk>
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

Original text of this message