database - 向空表 oracle 注册更改通知

Question

我正在尝试将更改通知监听器注册到一个表，该表目前是空的。我见过的创建表监听器的唯一方法是将数据选择到特定变量中:

SELECT employee_id 
    INTO   v_employee_id 
  FROM   hr.employees       
    WHERE  ROWNUM < 2;

此处的表格必须至少一行。

完整声明:

DECLARE
  v_cn_recip       SYS.CHNF$_REG_INFO;
  v_regid          NUMBER;
  v_employee_id    hr.employees.manager_id%TYPE;
BEGIN
  v_cn_recip := SYS.CHNF$_REG_INFO('hr.dcn_callback­', DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS, 0, 0, 0);
  v_regid := DBMS_CHANGE_NOTIFICATION.NEW_REG_START(v_cn_recip);
  SELECT employee_id 
    INTO   v_employee_id 
  FROM   hr.employees       
    WHERE  ROWNUM < 2;        
  DBMS_CHANGE_NOTIFICATION.REG_END;
  DBMS_OUTPUT.PUT_LINE('the registration id for this query is '||v_regid);
END;
/

来自 Oracle 文档:https://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_dcn.htm

我的问题如下:有什么办法可以给一个空表注册一个变化通知监听器吗？如果不是，这是出于任何特定原因而设计的吗？

Answer 1

只是一个猜测，但根据您提供的链接中的文档，它说:

The DBMS_CHANGE_NOTIFICATION package registers the object itself, which means that any change to the table—regardless of whether the change is to the row returned by the registered query—generates a notification

这很奇怪，如果它只是注册整个表，您甚至需要一个查询，但也许您可以尝试从表中进行计数，保证返回 1 且仅返回 1 行:

DECLARE
...
v_num number;
...
BEGIN
...
SELECT count(1)
    INTO   v_num 
  FROM   hr.employees       
    WHERE  ROWNUM < 2;
...
END;