Hey I hope somebody can help me with the following problem,
I'm trying to set up Database Change Notification with Java using the JDBC driver. I would like to use the object change notification mode (using as query SELECT * FROM TABLE) since the notifications shall be used for an application that is interested in all changes happening in predefined interface tables. While doing so a problem with one of the tables arose.
Normally the DatabaseChangeListener reports the changes accurately, but on this one table sometimes "wrong" rowIds are reported. Dropping the table, recreating and refilling it made no difference. The entries in the table are managed and created via our own software with help of Hibernate. The table has some indexes, triggers and views on it.
The simplified table definition looks like this:
create table TABLE
(
/* primary keys */
PK number(19) primary key,
/* default attributes */
CREATED timestamp default SYSTIMESTAMP,
UPDATED timestamp,
/* additional attributes */
SOME_VALUES_1 nvarchar2(12) not null,
SOME_VALUES_2 number(6) not null,
…
SOME_VALUES_2 nvarchar2(8) default 'VALUE' check (…),
)
/
create index IX_ 1 ON TABLE(SOME_VALUES_1)
/
create index IX_ 2 ON TABLE(SOME_VALUES_2, SOME_VALUES_3)
/
…
create trigger TABLE_UPDATE
before update on TABLE for each row
begin
:NEW.UPDATED := SYSTIMESTAMP;
end;
/
create trigger TABLE
after insert or update or delete on TABLEfor each row
begin
if inserting then
start a procedure
elsif updating then
start a procedure
else
start a procedure
end if;
end;
/
CREATE OR REPLACE VIEW V_TABLE AS
WITH …
AS
…
FROM TABLE V
/
The problem also appears when using a simple update query for creating changes on the already present data:
begin
for i in (select rowid, TABLE.* from TABLE)
loop
update TABLE
set SOME_VALUES_1 ='somthing'
where rowid=i.rowid;
COMMIT;
end loop;
end;
While 80% of the changes are reported with correct rowids and the entries can be found via SELECT PK FROM TABLE WHERE ROWID=CHARTOROWID('the_rowid');
20% are reported with rowIds which do not exist . Also if all rowids are queried, before and after the update, these wrong rowIds are not present and never have been present.
They do though obviously belong to the table since the first two blocks for object and file are the same as for the others which are actually existing.
The code which is used as minimal example is:
public class Test {
void run() {
try {
OracleConnection connection = (OracleConnection) new OracleDriver().connect(dbUrl, null);
Properties properties = new Properties();
properties.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
properties.setProperty(OracleConnection.DCN_NOTIFY_CHANGELAG, "0");
DatabaseChangeRegistration dcr = connection.registerDatabaseChangeNotification(properties);
try {
dcr.addListener(new DatabaseChangeListener() {
public void onDatabaseChangeNotification(DatabaseChangeEvent dce) {
for (int i = 0; i < dce.getTableChangeDescription()[0].getRowChangeDescription().length; i++) {
try {
OracleConnection conn = (OracleConnection) new OracleDriver().connect(dbUrl, null);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT PK FROM TABLE WHERE ROWID=CHARTOROWID('" + dce.getTableChangeDescription()[0].getRowChangeDescription()[i].getRowid() + "')");
boolean found = false;
while (rs.next()) {
found = true;
}
if (!found) {
System.out.println("Was not fount " + dce.getTableChangeDescription()[0].getRowChangeDescription()[i].getRowid().stringValue() + " " + dce.getTableChangeDescription()[0].getRowChangeDescription()[i].getRowOperation());
} else {
System.out.println("fount " + dce.getTableChangeDescription()[0].getRowChangeDescription()[i].getRowid().stringValue() + " " + dce.getTableChangeDescription()[0].getRowChangeDescription()[i].getRowOperation());
}
rs.close();
stmt.close();
conn.close();
} catch (SQLException ex) {
Logger.getLogger(Test.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
});
Statement stmt = connection.createStatement();
((OracleStatement) stmt).setDatabaseChangeRegistration(dcr);
ResultSet rs = stmt.executeQuery("select * from TABLE");
while (rs.next()) {
}
rs.close();
stmt.close();
Thread.sleep(100000000);
} finally {
if (connection != null) {
connection.unregisterDatabaseChangeNotification(dcr);
connection.close();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
new Test().run();
}
My question is what can be the causes for us receiving rowids which than cannot be found? Dose somebody know of this problem and knows of a way to resolve it?
Some additional info:
We are using Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production and the ojdbc7 JDBC Driver
The table is updated 3 to 4 times a second and has more than 1500 entries.