Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Error : Using DBMS_ALERT

Re: Error : Using DBMS_ALERT

From: Yong Huang <yong321_at_yahoo.com>
Date: 30 Aug 2003 12:45:10 -0700
Message-ID: <b3cb12d6.0308301145.d0af481@posting.google.com>


wonim.somaggio_at_lusis.fr (Petit Donghwa) wrote in message news:<ea4e9f9c.0308290052.42d6fab9_at_posting.google.com>...
> My goal is to notify automatically when ths table is inserted on my
> web_page written in PHP. (PHP and Oracle9)
> When I open this web_page it must run infinitely untile to to another
> web_page URL.
> So I made this function with PL/SQL function to use DBMS_ALERT.
...
> Could you help me Please what is a problem??
>
> **********************************************************************
> 1. table XYZ
> create table XYZ
> (
> DATA1 integer;
> );
>
>
> 2. Trigger for this table XYZ
> CREATE OR REPLACE TRIGGER XYZ_trig
> after insert on XYZ
> for each row
> begin
> dbms_alert.signal('xyz_alert', null);
> END XYZ;
> /
>
>
> 3. crete function to signal to my PHP page to notify this table is
> changed.
> create or replace function change_xyz_f
> return number
> declare
> alert_msg varchar2(20);
> alert_status number := 0;
> begin
> DBMS_ALERT.REGISTER('xyz_insert');
> DBMS_ALERT.WAITONE('xyz_insert', alert_msg, alert_status, 300);
>
> if alert_status =1
> then
> return 1;
> else
> return 0;
> end if;
> DBMS_ALERT.REMOVE('xyz_insert');
> end change_xyz_f;
> /
>
> 4. on my web-page written in PHP
> <?
> $connection_oracle = OCILogon("xxxxx","xxxxxx");
> $statement_oracle = OCIParse($connection_oracle, "BEGIN :s :=
> change_xyz_f(); END;");
> $statement_oracle = OCIParse($connection_oracle, $signal);
>
> OCIBindByName($statement_oracle, ":s", $s, 32);
> OCIExecute($statement_oracle, OCI_DEFAULT);
> OCIFreeStatement($statement_oracle);
> OCILogOff($connection_oracle);
>
> if ( $s == 1)
> echo "Table is changed!";
> else
> echo "Table is Not changed!";
>
>
>
> I make this PL/SQL function to use DBMS_ALERT.
> But many errors.

There may be several issues:
1. Your event in the function is 'xyz_insert' but the table trigger signals 'xyz_alert'. Just a typo?
2. You may already know. DBMS_ALERT sends the message out on commit. If you want it to be sent even on rollback after insert, the trigger needs an autonomous transaction.
3. DBMS_ALERT receives the last message you signaled if you have multiple sent. So FOR EACH ROW in the trigger probably won't work as expected.

Yong Huang Received on Sat Aug 30 2003 - 14:45:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US