Cannot register for DBMS Change notification, getting error “dbms change notification must be declared”

Question

I am trying to register for DBMS Change notification, however I get error mentioning that DBMS_CHANGE_NOTIFICATION must be declared.

Below is my SQL code:

DECLARE
  regid     NUMBER;
BEGIN
    --Deregister any existing change notification...
    BEGIN
        SELECT regid INTO regid FROM dba_change_notification_regs WHERE LOWER(table_name) LIKE '%aws_app_change_notif';
        IF (regid IS NOT NULL) THEN
            DBMS_OUTPUT.PUT_LINE('Deregistered aws_app_change_notif');
            DBMS_CHANGE_NOTIFICATION.DEREGISTER(regid);
        END IF;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('No change notification found for table aws_app_change_notif');
    END;

    --Register change notification...
    DECLARE
        regds             SYS.CHNF$_REG_INFO;
        regid             NUMBER;
        dept_id           NUMBER;
        qosflags          NUMBER;
        operations_filter NUMBER;
        max_id            NUMBER;
    BEGIN
        qosflags := DBMS_CHANGE_NOTIFICATION.QOS_RELIABLE + DBMS_CHANGE_NOTIFICATION.QOS_ROWIDS;
        operations_filter := DBMS_CHANGE_NOTIFICATION.INSERTOP;
        regds := SYS.CHNF$_REG_INFO ('aws_app_change_notif_callback', qosflags, 0,operations_filter,0);
        regid := DBMS_CHANGE_NOTIFICATION.NEW_REG_START (regds); 
        SELECT max(id) INTO max_id FROM aws_app_change_notif;
        DBMS_CHANGE_NOTIFICATION.REG_END;
    END;
END;

and after executing I get below error:

DBMS_CHANGE_NOTIFICATION.DEREGISTER(regid); ERROR at line 9:

ORA-06550: line 9, column 13: PLS-00201: identifier 'DBMS_CHANGE_NOTIFICATION' must be declared

ORA-06550: line 9, column 13: PL/SQL: Statement ignored

ORA-06550: line 25, column 21: PLS-00201: identifier 'DBMS_CHANGE_NOTIFICATION' must be declared

ORA-06550: line 25, column 9: PL/SQL: Statement ignored

ORA-06550: line 26, column 30: PLS-00201: identifier 'DBMS_CHANGE_NOTIFICATION' must be declared

ORA-06550: line 26, column 9: PL/SQL: Statement ignored

ORA-06550: line 28, column 18: PLS-00201: identifier 'DBMS_CHANGE_NOTIFICATION' must be declared

ORA-06550: line 28, column 9: PL/SQL: Statement ignored

ORA-06550: line 29, column 41: PL/SQL: ORA-00942: table or view does not exist

ORA-06550: line 29, column 9: PL/SQL: SQL Statement ignored

ORA-06550: line 30, column 9: PLS-00201: identifier 'DBMS_CHANGE_NOTIFICATION' must be declared

ORA-06550: line 30, column 9: PL/SQL: Statement ignored

I am using Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production as my database.

Can anyone advice what's going wrong here.

Does your user/schema have access granted to DBMS_CHANGE_NOTIFICATION? — OldProgrammer
It appears that your account doesn't have execute permission on the DBMS_CHANGE_NOTIFICATION package. — Bob Jarvis - Reinstate Monica
@BobJarvis Yes I dint have grant permission. After granting the execute permission I did not get any errors. Thanks for the help — Chetan

Chetan Chetan · Accepted Answer · 2019-04-10T01:56:59

I dint have right permissions granted which caused the issue.

After granting the permission, I could resolve the error.

A good explanation with example can be found here

Cannot register for DBMS Change notification, getting error “dbms change notification must be declared”

1 Answers