Oracle 12c 中的 Oracle DBMS_ALERT

问题描述：

我有一个表 (my_tab)，其中包含针对同一表中特定 ID 的 STATUS 列.

I have a table (my_tab) that contains a STATUS column against a specific ID in this same table.

我需要一种通过 DBMS_ALERT 进程在 STATUS 列更改值时收到警报的方法.

I need a means of being alerted via a DBMS_ALERT process of when the STATUS column changes value.

我正在考虑使用触发器来启动警报，即:

I was looking at using a trigger to kick off the ALERT, i.e.:

create or replace trigger my_tab_upd after update of status on my_tab for each row
begin
   dbms_alert.signal('mystatusalert', 'changed from '||:old.status||' to '||:new.status||'.');
end;
/

有了这个，我现在如何在 PL/SQL 过程中收到此 STATUS 更改已发生的警报/通知，然后根据此 STATUS 更改执行另一个操作?

With this, how do I now get alerted/notified that this STATUS change has occurred within a PL/SQL procedure to now go off and perform another operation based on this STATUS change?

除上述之外，通过我的应用程序设置，将有多个用户.基于此，我如何针对特定用户/会话设置警报，以便正确的用户只能收到他们的警报，而不是其他人的警报.

Further to the above, with my application setup, there will be multiple users. Based on this, how can I target the alert for specific users/sessions so that the correct user gets their alert only and not someone else's.

我正在考虑检查来自基于 Web 的应用程序 (Oracle APEX) 的警报，因此不想锁定前端，因此任何关于此的建议都会很好.

I am looking at checking the alert from a web based application (Oracle APEX), so don't want to lock the front-end up so any recommendations on this would be good.

举个例子就好了.

答

我会给自己发一封电子邮件.例如:

I'd send an e-mail to myself. For example:

create or replace trigger my_tab_upd 
  after update of status on my_tab 
  for each row
begin
  utl_mail.send (sender     => 'me@company.com',
                 recipients => 'me@company.com',
                 subject    => 'MY_TAB status changed',
                 message    => 'old = ' || :old.status ||', new = ' || :new.status
                );
end;

DBMS_ALERT 示例:在 Scott 的架构中，我想通知我的存储过程 EMP 表中的某些内容发生了变化，然后 做某事(我只会显示消息).

DBMS_ALERT example: in Scott's schema, I want to notify my stored procedure that something has changed in the EMP table and then do something (I'll just display the message).

首先，创建一个触发器；警报名称是 alert_emp，稍后将在存储过程中使用:

First, create a triggger; alert name is alert_emp and will be used later in the stored procedure:

SQL> create or replace trigger trg_au_emp
  2    after update on emp
  3    for each row
  4  begin
  5    dbms_alert.signal
  6      ('alert_emp', 'Salary changed for ' || :new.ename ||
  7                    ' from ' || :old.sal ||
  8                    ' to '   || :new.sal);
  9  end;
 10  /

Trigger created.

程序:

SQL> create or replace procedure p_test is
  2    l_msg    varchar2(200);
  3    l_status number;
  4  begin
  5    dbms_alert.register ('alert_emp');
  6    dbms_alert.waitone  ('alert_emp', l_msg, l_status);
  7    dbms_output.put_line(l_msg ||': '|| l_status);
  8  end;
  9  /

Procedure created.

现在，执行程序:

SQL> exec p_test;

在这里，它只是在等待 EMP 表中发生的事情.在另一个会话中我正在更新表格.提交是强制性的；否则，什么都不会发生.p_test 仍将等待.

Here, it is just waiting for something to happen in the EMP table. In another session I'm updating the table. Commit is obligatory; otherwise, nothing happens. p_test will still be waiting.

update emp set sal = 1000 where empno = 7369;
commit;

在第一个会话中，一旦 commit 被执行，屏幕显示:PL/SQL 过程成功完成.

In the first session, once commit is being executed, screen shows this: PL/SQL procedure successfully completed.

Salary changed for SMITH from 800 to 1000: 0

PL/SQL procedure successfully completed.

SQL>

Oracle 12c 中的 Oracle DBMS_ALERT

相关推荐