Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: removing an alert in ORacle8i

    Hi,

    How does one remove an alert or all alerts in Oracle8i? More precisely, what is the command?

    begin
    removeall('emp_alert');
    end;
    /

    I tried that and it does not work. I may be doing something wrong.

    Thanks in advance for your help.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Alex,

    I haven't tried this, ie I don't even know if what you're trying to do is possible, but I'm guessing that your block was meant to be...

    begin
    dbms_alert.removeall;
    end;

    I'm also guessing your db version:-)

    Also, does it not work because of an error, or does the code execute okay but not do what you expect? Maybe you need to be a bit more specific than just saying "it does not work".

    HTH
    Bill

    From the comments in the dbms_alert package spec....
    procedure removeall;
    -- Remove all alerts for this session from registration list. Do this
    -- when the session is no longer interested in any alerts. Removing
    -- alerts is important since it will reduce the amount of work done
    -- by signalers of the alert. If a session dies without removing all
    -- of its alerts, the alerts will eventually (but not immediately)
    -- be cleaned up. This call always performs a commit.
    --
    -- This procedure is called automatically upon first reference to this
    -- package during a session. Therefore no alerts from prior sessions
    -- which may have terminated abnormally can affect this session.

  3. #3
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Error

    Hi,

    Thank you for your assistance. I should have included the dbms_alert package to invoke the procedure. That was stupid of me. Anyway, of course that worked just fine.

    Oracle8i 8.1.7.

    I have another error, though.

    create or replace trigger security
    before insert or update or delete on employees
    for each row
    begin
    dbms_alert.signal('emp_change','NOTICE: OLD id: ' || :OLD.emp_id
    || ' NEW id: ' || :NEW.emp_id || ' OLD Pay Rate: '
    || :OLD.pay_rate || ' NEW pay rate: ' || :NEW.pay_rate);

    insert into security
    (OLD_EMP_ID,OLD_EMP_NAME,OLD_SUPERVISED_BY,OLD_PAY _RATE,OLD_PAY_TYPE,OLD_EMP_DEPT_ID,
    NEW_EMP_ID,NEW_EMP_NAME,NEW_SUPERVISED_BY,NEW_PAY_ RATE,NEW_PAY_TYPE,NEW_EMP_DEPT_ID,
    VERIFIED,CHANGED_BY,TIME_CHANGED)
    values
    (:OLD.EMP_ID,:OLD.EMP_NAME,:OLD.SUPERVISED_BY,:OLD .PAY_RATE,:OLD.PAY_TYPE,OLD_EMP_DEPT_ID,
    :NEW.EMP_ID,:NEW.EMP_NAME,:NEW.SUPERVISED_BY,:NEW. PAY_RATE,:NEW.PAY_TYPE,:NEW.EMP_DEPT_ID,
    'N',user,sysdate);
    end security;
    /

    Warning: Trigger created with compilation errors.

    LOCAL>show errors;
    Errors for TRIGGER SECURITY:

    LINE/COL ERROR
    -------- ---------------------------------------------------
    12/76 PLS-00049: bad bind variable 'NEW.EMP_DEPT_ID'
    LOCAL>

    The table is properly defined:

    LOCAL>desc security;
    Name
    --------------------
    OLD_EMP_ID
    OLD_EMP_NAME
    OLD_SUPERVISED_BY
    OLD_PAY_RATE
    OLD_PAY_TYPE
    OLD_EMP_DEPT_ID
    NEW_EMP_ID
    NEW_EMP_NAME
    NEW_SUPERVISED_BY
    NEW_PAY_RATE
    NEW_PAY_TYPE
    NEW_EMP_DEPT_ID
    VERIFIED
    CHANGED_BY
    TIME_CHANGED


    I don't get this error at all.

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Alex,

    You need to describe the employees table.

    You're not getting an error on :OLD.EMP_DEPT_ID because in your insert you are inserting OLD_EMP_DEPT_ID as a value into column OLD_EMP_DEPT_ID.

    Check the column naming for EMP_DEPT_ID in employees and modify the insert into security accordingly for both old and new emp_dept_id.

    HTH
    Bill

Posting Permissions

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