Test case
1. Launch two instances of SQL*PLus. In the first enter
set serverout on size 1000000
DECLARE
c_alert_name CONSTANT CHAR(10) := 'test alert';
v_message VARCHAR2(2000);
v_status NUMBER;
c_wait CONSTANT NUMBER := 60;
v_exit PLS_INTEGER := 0;
BEGIN
-- resgister interest in the alert
dbms_alert.register(c_alert_name);
-- Loop constantly until the alert is received
WHILE (v_exit = 0) LOOP
dbms_alert.waitone(c_alert_name, v_message, v_status, c_wait);
IF (v_status = 0) THEN
dbms_output.put_line('Alert Raised!!!');
dbms_output.put_line('Message: '||substr(v_message,1,200));
v_exit := 1;
ELSE
dbms_output.put_line('No alert raised...');
END IF;
END LOOP;
END;
/
2. In the second enter:
exec dbms_alert.signal('test alert','hello world');
nothing happens
Then enter
commit;
And the alert is recived in the first session.
According to The Oracle 8i Supplied Packages Reference:
"Alerts are transaction-based. This means that the waiting session does not
get alerted until the transaction signalling the alert commits." (p 2-1).
dbms_job is transaction based too. I don't know if there are others.
Dan
-----Original Message-----
From: Tim Bunce [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 27, 2001 9:31 PM
To: Dan Horne
Cc: [EMAIL PROTECTED]
Subject: Re: Autocomit and oracle packages
On Wed, Sep 26, 2001 at 03:12:53PM +1200, Dan Horne wrote:
> Hi all
>
> I don't know if this has been discussed on this list before, but I just
> thought I'd mention it as it took me a while to figure out why my code
> wasn't working. If you call an Oracle supplied package that requires a
> commit after being called (such as dbms_alert), Autocommit will not take
> care of it for you - I guess because it's not a DML statement. To save you
> having to type commit after all of your other SQL, create a separate
> connection for the procedure calls, and commit on that handle
Odd.
Is that behaviour documented by Oracle?
Are you using OCI 7 or OCI 8?
Can you provide a small self-contained test case?
Tim.
