Re: Using DBMS_ALERT

From: Brian P. Mac Lean <brian.maclean_at_teldta.com>
Date: 1996/09/03
Message-ID: <322C5D30.35A1_at_teldta.com>

Adrian Mulligan wrote:

> 
> has anyoone had success using the dbms_alert package?
> 
> I seem to be having problems getting it to pick up an alert with the
> waitone procedure...
> 
> thanks in advance
> 
> Adrian.

Some time ago I tested alerts in sqlplus with spooling on. This is the *.lst output. I didn't edit out errors, etc., so please just read over them. The waitone is tested around line 100 (of 400):

SQL> select * from dbms_alert_info;

no rows selected

SQL> desc dbms_alert_info

 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                            NOT NULL VARCHAR2(30)
 SID                             NOT NULL VARCHAR2(30)
 CHANGED                                  VARCHAR2(1)
 MESSAGE                                  VARCHAR2(1800)
 
SQL> variable status number;
SQL> variable name varchar2;

SQL> variable message varchar2
SQL> ececute dbms_alert.register('BART1'); unknown command beginning "ececute db..." - rest of line ignored. SQL> execute dbms_alert.register('BART1');

PL/SQL procedure successfully completed.

SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C


------------------------------ ------------------------------ -

MESSAGE

BART1                          007000570000                   N
 
 
 
SQL> col name format a30

SQL> col message format a30
SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   N

SQL> execute dbms_alert.register('BART2');

PL/SQL procedure successfully completed.

SQL> l
1* select * from dbms_alert_info
SQL> /
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   N
BART2                          007000570000                   N

SQL> execute dbms_alert.signal('BART1','HELLO WORLD');

PL/SQL procedure successfully completed.

SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   Y HELLO WORLD
BART2                          007000570000                   N

SQL> commit;

Commit complete.

SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   Y HELLO WORLD
BART2                          007000570000                   N

SQL> execute dbms_alert.waitone('BART1', :message, :status); begin dbms_alert.waitone('BART1', :message, :status); end;

*
ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_ALERT", line 315
ORA-06512: at line 1

SQL> variable message varchar2;
SQL> variable status integer;
Usage: VARIABLE [ variable [ NUMBER | CHAR | CHAR (n) |

                             VARCHAR2 (n) | REFCURSOR ] ]
SQL> variable message varchar2(128);

SQL> variable status number;
SQL> execute dbms_alert.waitone('BART1', :message, :status);

PL/SQL procedure successfully completed.

SQL> print message, status
Hit ENter To Continue...
MESSAGE

HELLO WORLD Usage: PRINT variable ...
SQL>
SQL> print message status
Hit ENter To Continue...
MESSAGE

HELLO WORLD Hit ENter To Continue...

STATUS

SQL> execute dbms_alert.waitone('BART1', :message, :status, 0);

PL/SQL procedure successfully completed.

SQL> print message status
Hit ENter To Continue...
MESSAGE

Hit ENter To Continue...

STATUS

SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   N HELLO WORLD
BART2                          007000570000                   N

SQL> execute dbms_alert.signal('BART1','SHIVER ME TIMBERS');

PL/SQL procedure successfully completed.

SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   Y SHIVER ME TIMBERS
BART2                          007000570000                   N

SQL> execute dbms_alert.waitone('BART1', :message, :status, 0); begin dbms_alert.waitone('BART1', :message, :status, 0); end;

*
ERROR at line 1:

ORA-20000: ORU-10037: attempting to wait on uncommitted signal from same session
ORA-06512: at "SYS.DBMS_ALERT", line 303
ORA-06512: at line 1

SQL> commit;

Commit complete.

SQL> execute dbms_alert.waitone('BART1', :message, :status, 0);

PL/SQL procedure successfully completed.

SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   N SHIVER ME TIMBERS
BART2                          007000570000                   N

SQL> print message status
Hit ENter To Continue...
MESSAGE

SHIVER ME TIMBERS Hit ENter To Continue...

STATUS

SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   N SHIVER ME TIMBERS
BART2                          007000570000                   N

SQL> select 'AT THIS POINT I HAVE AN UNCOMMITED ALERT FROM WAITING FROM ANOTHER SESSION' from dual; Hit ENter To Continue...
'ATTHISPOINTIHAVEANUNCOMMITEDALERTFROMWAITINGFROMANOTHERSESSION'

AT THIS POINT I HAVE AN UNCOMMITED ALERT FROM WAITING FROM ANOTHER SESSION SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   N SHIVER ME TIMBERS
BART2                          007000570000                   N

SQL> select 'IT WAS JUST COMMITED' from dual; Hit ENter To Continue...
'ITWASJUSTCOMMITED'

IT WAS JUST COMMITED SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   Y THIS IS FROM ANOTHER SESSION
BART2                          007000570000                   N

SQL> execute dbms_alert.waitone('BART1', :message, :status, 0);

PL/SQL procedure successfully completed.

SQL> print message status
Hit ENter To Continue...
MESSAGE

THIS IS FROM ANOTHER SESSION Hit ENter To Continue...

STATUS

SQL> execute dbms_alert.waitone('BART1', :message, :status, 0);

PL/SQL procedure successfully completed.

SQL> print message status
Hit ENter To Continue...
MESSAGE

Hit ENter To Continue...

STATUS

SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   N THIS IS FROM ANOTHER SESSION
BART2                          007000570000                   N

SQL> variable name varchar2(128)
SQL> select 'I AM NOW GOING TO ENTER A BART2 ALERT AND COMMIT IT IN ANOTHER SESSION USING execute dbms_alert.signal('BA
RT2','AH DAH!')' from dual;
select 'I AM NOW GOING TO ENTER A BART2 ALERT AND COMMIT IT IN ANOTHER SESSION USING execute dbms_alert.signal('BART2','AH DAH!')' f
rom dual

ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> execute dbms_alert.waitany(:name, :message, :status, 0);

PL/SQL procedure successfully completed.

SQL> print name message status
Hit ENter To Continue...
NAME

Hit ENter To Continue...
MESSAGE

Hit ENter To Continue...

STATUS

SQL> execute dbms_alert.waitany(:name, :message, :status, 0);

PL/SQL procedure successfully completed.

SQL> print name message status
Hit ENter To Continue...
NAME

BART2 Hit ENter To Continue...
MESSAGE

AH DAH! Hit ENter To Continue...

STATUS

SQL> execute dbms_alert.remove('bart1');

PL/SQL procedure successfully completed.

SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART2                          007000570000                   N AH DAH!

SQL> execute dbms_alert.remove('bart2');

PL/SQL procedure successfully completed.

SQL> select 'I JUST ADDED alerts to bart1 and bart2 in another session' from dual; Hit ENter To Continue...
'IJUSTADDEDALERTSTOBART1ANDBART2INANOTHERSESSION'

I JUST ADDED alerts to bart1 and bart2 in another session

SQL> select * from dbms_alert_info;

no rows selected

SQL> execute dbms_alert.register('BART1');

PL/SQL procedure successfully completed.

SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   N

SQL> execute dbms_alert.register('BART2');

PL/SQL procedure successfully completed.

SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   N
BART2                          007000570000                   N

SQL> select 'ADDING bart1 in another session again' from dual; Hit ENter To Continue...
'ADDINGBART1INANOTHERSESSIONAGAIN'

ADDING bart1 in another session again

SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   Y AH DAH!
BART2                          007000570000                   N

SQL> execute dbms_alert.waitany(:name, :message, :status, 0);

PL/SQL procedure successfully completed.

SQL> print name message status
Hit ENter To Continue...
NAME

BART1 Hit ENter To Continue...
MESSAGE

AH DAH! Hit ENter To Continue...

STATUS

SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   N AH DAH!
BART2                          007000570000                   N

SQL> execute dbms_alert.remove('bart2');

PL/SQL procedure successfully completed.

SQL> select * from dbms_alert_info;
Hit ENter To Continue...

NAME                           SID                            C MESSAGE


------------------------------ ------------------------------ - ------------------------------


BART1                          007000570000                   N AH DAH!

SQL> execute dbms_alert.remove('bart1');

PL/SQL procedure successfully completed.

SQL> select * from dbms_alert_info;

no rows selected

SQL>

                       \\|//
                       (0-0)
           +-----oOO----(_)-----------+
           | Brian P. Mac Lean        |
           | Database Analyst         |
           | brian.maclean_at_teldta.com |
           | http://www.teldta.com    |
           +-------------------oOO----+
                      |__|__|
                       || ||
                      ooO Ooo

Received on Tue Sep 03 1996 - 00:00:00 CEST

This message: [ Message body ]
Next message: Brian Martin: "Re: Using DBMS_ALERT"
Previous message: Adrian Mulligan: "Using DBMS_ALERT"
In reply to: Adrian Mulligan: "Using DBMS_ALERT"
Next in thread: Brian Martin: "Re: Using DBMS_ALERT"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message