1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How DBMS_ALERT works.,?

Discussion in 'SQL PL/SQL' started by Vicky, Feb 24, 2015.

  1. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    Hii..

    I've created trigger for the table test_table as below:

    create table test_table (status number);

    insert into test_table values (1);


    CREATE TRIGGER ex_trig AFTER INSERT OR UPDATE OR DELETE ON test_table
    BEGIN
    DBMS_ALERT.SIGNAL('STATUS_CHANGE', 'message_text');
    END;

    Cud U tel me how/where to chk the alert, once the DML occurs./.!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    In what you have a question?
    Who is the listener and who shall receives messages?

    simple example....

    One session :
    Code (SQL):

    DECLARE

      l_name varchar2(32 CHAR) := 'YOUR_MSG';
      l_msg  varchar2(255 CHAR);
      l_status NUMBER;
       
    BEGIN
        dbms_alert.register(l_name);    
        dbms_alert.waitone(l_name,l_msg,l_status,180);        
        IF l_status = 1 THEN
            dbms_output.put_line('Time-out....');
        ELSE
            dbms_output.put_line('your_msg = '||l_msg);
        END IF;        
        dbms_alert.remove(l_name);    
    END;    
    /
     


    Other session :
    Code (SQL):

    BEGIN
        dbms_alert.signal('YOUR_MSG','END');
        commit;
    END;    
     
     
    Vicky likes this.