Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Red face Unanswered: DBMS_ALERT usage!!

    I want to use a DBMS_ALERT to fire a message whenever a user tries to perform a DDL operation (e.g. DELETE on a table) ... I used DBMS_ALERT package, I already registered the alert but....nothing!!!
    Any ideas ????

  2. #2
    Join Date
    Oct 2003
    Location
    Bangalore, India
    Posts
    5

    DBMS_ALERT EXAMPLE

    Hi,

    This example may help you in working with DBMS_ALERT.

    connect sys/pwd

    grant execute on dbms_alert to <user>;

    /* login to a session of above user */

    connect <user>/pwd

    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;
    /

    Login to another session with same user name and password.

    DECLARE
    alert_msg VARCHAR2(1800);
    alert_status INTEGER;
    alert_name VARCHAR2(40);
    BEGIN
    DBMS_ALERT.REGISTER('STATUS_CHANGE');
    DBMS_ALERT.WAITANY(alert_name, alert_msg, alert_status, 30);

    IF alert_status = 1
    THEN
    DBMS_OUTPUT.PUT_LINE('timed out');

    ELSE
    DBMS_OUTPUT.PUT_LINE('received alert: '||alert_name);
    END IF;
    DBMS_ALERT.REMOVE('STATUS_CHANGE');
    END;
    /

    /* This procedure will wait for an alert for 30 secs*/

    /* Now revert to the first session of the user and run the following*/


    BEGIN
    execute immediate
    'update smsgw_test set status = 0
    where status = 1';
    commit;
    END;
    /

    This update DML will generate a signal that will be caught by the procedure running in the second session.

    regards,
    San.

  3. #3
    Join Date
    Oct 2003
    Location
    Bangalore, India
    Posts
    5
    Oh!, change smsgw_test to test_table in the example.

    -San.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •