5 Replies Latest reply: Nov 30, 2012 7:13 AM by Hoek RSS

    Oracle package DBMS_CHANGE_NOTIFICATION

    918847
      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
          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
            Hi,

            Does this notification feature available in 11g?

            Thanks
            • 3. Re: Oracle package DBMS_CHANGE_NOTIFICATION
              Hoek
              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
                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
                  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