The Oracle Change Notification or Continuous Query Notification functionality is a useful means of triggering events in remote sessions when a transaction commits. Sometimes though a client may set up a registration and then disappear leaving orphaned registrations. Using the DBMS_CHANGE_NOTIFICATION package (documented by it’s synonym DBMS_CQ_NOTIFICATION) these old entries can be removed easily with the DEREGISTER procedure.
SQL> SELECT regid FROM user_change_notification_regs; REGID ----- 12345 SQL> begin 2 dbms_change_notification.deregister(12345); 3 end; 4 /
You can also deregister entries with Java using the unregisterDatabaseChangeNotification method of the OracleConnection class.
This can be particularly helpful if you still have older (10g/11g) systems in use because the PL/SQL deregister procedure did not recognize registrations created using the OCI interface. I ran into this with appliactions written in C# that could not be deregistered using the pl/sql interface. While it would have been possible to create a cleanup procedure in C#, we wanted to centralize the functionality within the database. To this end I created a small Java class and corresponding package to expose the api as stored procedures. Using this package notifications from PL/SQL or OCI would be deregistered. I also added support for mass-deregistration. So a single call could deregister all notifcations for a given user, a table, or from a particular host machine. All of these work simply by iterating through the USER_CHANGE_NOTIFICATION_REGS view and deregistering each id found there.
Using the view served multiple purposes. First, obviously, it provided the list of notifications based on the given inputs. Second, the view would return the callback string needed for the java deregistration API. Third, and most importantly, it would reinforce the security paradigm that a user could only deregister the notifications created by that user. Since this was intended for application usage there was no need to support SYS-level deregistration. Funneling all requests through the view enforced application level access only.
The Java class we used was:
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "DeReg_CQ_Notif" as
import java.sql.*;
import oracle.jdbc.*;
public class DeReg_CQ_Notif {
public static void dereg_id(OracleConnection conn, long p_regid, String p_callback) throws Exception
{
try {
conn.unregisterDatabaseChangeNotification(p_regid,p_callback);
}
catch (SQLException ex){
if (ex.getErrorCode() != 24950)
throw ex;
}
}
public static void dereg_by_id(String p_url, String p_user, String p_password, long p_regid) throws Exception
{
OracleConnection conn = (OracleConnection)DriverManager.getConnection(p_url,p_user,p_password);
PreparedStatement pstmt = conn.prepareStatement("select callback from USER_CHANGE_NOTIFICATION_REGS where regid=?");
pstmt.setLong(1, p_regid);
ResultSet rs = pstmt.executeQuery();
while(rs.next())
{
String callback = rs.getString(1);
dereg_id(conn,p_regid,callback);
}
rs.close();
pstmt.close();
conn.close();
}
public static void dereg_by_host(String p_url, String p_user, String p_password, String p_host) throws Exception
{
OracleConnection conn = (OracleConnection)DriverManager.getConnection(p_url,p_user,p_password);
PreparedStatement pstmt = conn.prepareStatement("select regid,callback from USER_CHANGE_NOTIFICATION_REGS where regexp_substr(callback,'HOST=([^)]+)',1,1,null,1)=?");
pstmt.setString(1, p_host);
ResultSet rs = pstmt.executeQuery();
while(rs.next())
{
long regid = rs.getLong(1);
String callback = rs.getString(2);
dereg_id(conn,regid,callback);
}
rs.close();
pstmt.close();
conn.close();
}
public static void dereg_by_table(String p_url, String p_user, String p_password, String p_table) throws Exception
{
OracleConnection conn = (OracleConnection)DriverManager.getConnection(p_url,p_user,p_password);
PreparedStatement pstmt = conn.prepareStatement("select regid,callback from USER_CHANGE_NOTIFICATION_REGS where table_name=?");
pstmt.setString(1, p_table);
ResultSet rs = pstmt.executeQuery();
while(rs.next())
{
long regid = rs.getLong(1);
String callback = rs.getString(2);
dereg_id(conn,regid,callback);
}
rs.close();
pstmt.close();
conn.close();
}
public static void dereg_by_user(String p_url, String p_user, String p_password) throws Exception
{
OracleConnection conn = (OracleConnection)DriverManager.getConnection(p_url,p_user,p_password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select regid,callback from USER_CHANGE_NOTIFICATION_REGS");
while(rs.next())
{
long regid = rs.getLong(1);
String callback = rs.getString(2);
dereg_id(conn,regid,callback);
}
rs.close();
stmt.close();
conn.close();
}
}
/
The API was exposed through PL/SQL using the following package:
CREATE OR REPLACE PACKAGE cq_notifications
AS
PROCEDURE dereg_by_id(
p_url IN VARCHAR2,
p_user IN VARCHAR2,
p_password IN VARCHAR2,
p_reg_id IN NUMBER
);
PROCEDURE dereg_by_host(
p_url IN VARCHAR2,
p_user IN VARCHAR2,
p_password IN VARCHAR2,
p_host IN VARCHAR2
);
PROCEDURE dereg_by_table(
p_url IN VARCHAR2,
p_user IN VARCHAR2,
p_password IN VARCHAR2,
p_table IN VARCHAR2
);
PROCEDURE dereg_by_user(p_url IN VARCHAR2, p_user IN VARCHAR2, p_password IN VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY cq_notifications
AS
PROCEDURE dereg_by_id(
p_url IN VARCHAR2,
p_user IN VARCHAR2,
p_password IN VARCHAR2,
p_reg_id IN NUMBER
)
AS
LANGUAGE JAVA
NAME 'DeReg_CQ_Notif.dereg_by_id( java.lang.String, java.lang.String, java.lang.String, long )' ;
PROCEDURE dereg_by_host(
p_url IN VARCHAR2,
p_user IN VARCHAR2,
p_password IN VARCHAR2,
p_host IN VARCHAR2
)
AS
LANGUAGE JAVA
NAME 'DeReg_CQ_Notif.dereg_by_host( java.lang.String, java.lang.String, java.lang.String, java.lang.String )' ;
PROCEDURE dereg_by_table(
p_url IN VARCHAR2,
p_user IN VARCHAR2,
p_password IN VARCHAR2,
p_table IN VARCHAR2
)
AS
LANGUAGE JAVA
NAME 'DeReg_CQ_Notif.dereg_by_table( java.lang.String, java.lang.String, java.lang.String, java.lang.String )' ;
PROCEDURE dereg_by_user(p_url IN VARCHAR2, p_user IN VARCHAR2, p_password IN VARCHAR2)
AS
LANGUAGE JAVA
NAME 'DeReg_CQ_Notif.dereg_by_user( java.lang.String, java.lang.String, java.lang.String )' ;
END;
/
Per MOS Doc ID 971412.1, it is not necessary to use Java in 12c or above as the PL/SQL interface recognizes notifications created by either PL/SQL and OCI.
In the use case we had, external cleanup was supported only by explicit request hence the requirement to require extra login with username and password. Less restrictive functionality could have been achieved using default connections and invoker rights with the package.