This discussion is archived
5 Replies Latest reply: Nov 30, 2012 5:13 AM by Hoek RSS

Oracle package DBMS_CHANGE_NOTIFICATION

918847 Newbie
Currently Being Moderated
Hello All,

We are making a POC to enable database notifications on one of our DB Schema, As per the Oracle documentation to enable notification
as a first step i have logged into SYSDBA account to grant permissions to DBMS_CHANGE_NOTIFICATION & NOTIFICATION like below.

GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO GAE9;
GRANT CHANGE NOTIFICATION TO GAE9;

here "GAE9" is Schema Name, when we tried to Execute grant permissions on DBMS_CHANGE_NOTIFICATION we get table or view does not exist, however the second "GRANT CHANGE NOTIFICATION TO GAE9" works absolutely fine.

Could you please help me to find the missing settings here, Thank You.

Regards,
Venu
  • 1. Re: Oracle package DBMS_CHANGE_NOTIFICATION
    Hoek Guru
    Currently Being Moderated
    Does following the steps from this article: http://www.oracle-base.com/articles/10g/dbms_change_notification_10gR2.php work for you?
  • 2. Re: Oracle package DBMS_CHANGE_NOTIFICATION
    918847 Newbie
    Currently Being Moderated
    Hi,

    Does this notification feature available in 11g?

    Thanks
  • 3. Re: Oracle package DBMS_CHANGE_NOTIFICATION
    Hoek Guru
    Currently Being Moderated
    Yes, it is documented under 10G, so it is at least 'backwards compatible'.
    If I run the script from the previous link I posted on an 11.2 database:
    SQL> desc dbms_change_notification
    ERROR:
    ORA-04043: object "SYS"."DBMS_CHANGE_NOTIFICATION" does not exist
    
    
    SQL> conn sys/*****@orcl as sysdba
    Verbonden.
    
    Session altered.
    
    SQL> CREATE USER test IDENTIFIED BY test
      2    DEFAULT TABLESPACE users
      3    TEMPORARY TABLESPACE temp
      4    QUOTA UNLIMITED ON users;
    
    User created.
    
    SQL> 
    SQL> GRANT CONNECT, CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE TO test;
    
    Grant succeeded.
    
    SQL> GRANT CHANGE NOTIFICATION TO test;
    
    Grant succeeded.
    
    SQL> GRANT EXECUTE ON DBMS_CHANGE_NOTIFICATION TO test;
    
    Grant succeeded.
    
    SQL> conn test/test@orcl
    Verbonden.
    
    Session altered.
    
    SQL> select banner from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE    11.2.0.1.0      Production
    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    
    SQL> desc DBMS_CHANGE_NOTIFICATION
    FUNCTION CQ_NOTIFICATION_QUERYID RETURNS NUMBER
    PROCEDURE DEREGISTER
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     REGID                          NUMBER                  IN
    PROCEDURE ENABLE_REG
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     REGID                          NUMBER                  IN
    FUNCTION NEW_REG_START RETURNS NUMBER
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     REGDS                          CHNF$_REG_INFO          IN
    FUNCTION NEW_REG_START_OC4J RETURNS NUMBER
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     REGDS                          CHNF$_REG_INFO_OC4J     IN
    PROCEDURE REG_END
    PROCEDURE SET_ROWID_THRESHOLD
     Argument Name                  Type                    In/Out Default?
     ------------------------------ ----------------------- ------ --------
     TBNAME                         VARCHAR2                IN
     THRESHOLD                      NUMBER                  IN
    But it appears to be renamed when searching the 11.2 documentation on "DBMS_CHANGE_NOTIFICATION".
    http://www.oracle.com/pls/db112/search?word=dbms_change_notification
  • 4. Re: Oracle package DBMS_CHANGE_NOTIFICATION
    918847 Newbie
    Currently Being Moderated
    So if i understand it correctly 'Database Change Notification' changed to 'Continuous Query Notification', and do we have to get the this package installed in my database to see the notification package and access it?

    Thank you
  • 5. Re: Oracle package DBMS_CHANGE_NOTIFICATION
    Hoek Guru
    Currently Being Moderated
    Not necessary, you can use DBMS_CHANGE_NOTIFICATION as demonstrated (on 11.2).
    It is still available. However:
    "In 11gR1 the synonym DBMS_CQ_NOTIFICATIONS was added for this package"
    See:
    http://www.morganslibrary.org/reference/pkgs/dbms_change_notification.html
    and:
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_cqnotif.htm

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points