DBMS_ALERT

From: ahase <ahase_at_md.xaxon.ne.jp>
Date: 2000/07/10
Message-ID: <8kcnvv$209s$1@news1.md.xaxon.ne.jp>#1/1

Hello!

I have a problem with DBMS_ALERT (Oracle 8.0.5 under NT4). I wrote a test program "dbms_alert.cpp".

When execute 3 processes "dbms_alert.exe" at same time, and update "FXTrade"table.
A process detects the alert at just update time, another detects the alert after timeout, another process detects error with:

ErrorNumber()= 8197
Error: ORA-20000: ORU-10024: there are no alerts registered. ORA-06512: "SYS.DBMS_ALERT", LINE: 241
ORA-06512: LINE: 1 what am I doing wrong?
Does this mean that all processes can't detect the alert at JUST UPDATE TIME?

<dbms_alert.cpp>
#include "stdafx.h"
#include <stdio.h>
#include <time.h>

#include "e:\orant\oo4o23\cpp\include\oracl.h"
#pragma comment(lib, "e:\\orant\\oo4o23\\cpp\\lib\\oraclm32.lib")

#define ORA_USERNAME "scott"
#define ORA_PASSWD "tiger"
#define ORA_SERVER ""
#define REG_ALERT "begin dbms_alert.register('FXTrade_ALERT'); end;"
#define WAIT_ALERT "begin dbms_alert.waitany(:NAME, :MESSAGE, :STATUS,
:TIMEOUT); end;"
#define RM_ALERT "begin dbms_alert.remove('FXTrade_ALERT'); end;"

int main(int argc, char* argv[])
{
 ODatabase m_database;
 oresult ores;
 OParameterCollection params;

 OParameter para_message;
 OParameter para_name;
 OParameter para_status;
 OParameter para_timeout;

 int status;
 const char *ptr_message;
 const char *ptr_name;
 int time_out;
 time_t ltime;

 time_out = 10;
 if (argc > 1) {
  time_out = atoi(argv[1]);
 }

 OStartup();

 /* Connect */
 if ((ores = m_database.Open(ORA_SERVER, ORA_USERNAME, ORA_PASSWD))   != OSUCCESS ){
  goto ERROR_EXIT;
 }

 /* Register */
 if ( (ores = m_database.ExecuteSQL(REG_ALERT)) != OSUCCESS ){   goto ERROR_EXIT;
 }

 /* Parameters */
 params = m_database.GetParameters();

 para_name = params.Add("NAME", "", OPARAMETER_OUTVAR, OTYPE_VARCHAR2);
 para_message = params.Add("MESSAGE", "", OPARAMETER_OUTVAR,
OTYPE_VARCHAR2);
 para_status = params.Add("STATUS", 0, OPARAMETER_OUTVAR, OTYPE_NUMBER);
 para_timeout = params.Add("TIMEOUT", 0, OPARAMETER_INVAR, OTYPE_NUMBER);

 ores = para_timeout.SetValue(time_out);

 /* Wait */
 while (1) {

  if ( (ores = m_database.ExecuteSQL(WAIT_ALERT)) != OSUCCESS ){    goto ERROR_EXIT;
  }
  ores = para_status.GetValue(&status);

  /* Current Time */
  time(&ltime);

  /* Timeout */
  if ( status != 0 ) {
   printf ("Timeout(%s)\n", ctime( &ltime ) );    continue;
  }
  /* Alert */
  else {
   ores = para_name.GetValue(&ptr_name);    ores = para_message.GetValue(&ptr_message);    printf ("Alert = %s, message = %s(%s)\n", ptr_name, ptr_message, time( &ltime ));
  }
 }

 ores = m_database.ExecuteSQL(RM_ALERT);  OShutdown();
 printf("Done.\n");
 return 0;

ERROR_EXIT:
 ores = m_database.ExecuteSQL(RM_ALERT);

 OShutdown();
 printf("Error: %s\n", m_database.GetServerErrorText());  return 1;
}

<FXTrade table Trigger>:
create trigger FXTrade_TRIG after insert or update on FXTrade for each row
declare
 send_msg varchar2(32);
 begin
 send_msg := :new.ID;
 dbms_alert.signal('FXTrade_ALERT', send_msg);  end;

Many thanks in advance,
AHASE Received on Mon Jul 10 2000 - 00:00:00 CDT