Update another table in database using RowId's returned from database events

Question

Handling RowID's returned as part of DB events through a listener:

class DCNDemoListener implements DatabaseChangeListener
    {
      DBChangeNotification demo;
      DCNDemoListener(DBChangeNotification dem)
      {
        demo = dem;
      }
      public void  onDatabaseChangeNotification(DatabaseChangeEvent e)
      {
        System.out.println(e.toString());
      }
    }

For Example: Below are the values returned from database on DML operation

ROW: operation=UPDATE, ROWID=AAASjgAABAAAVapAAA

Using the above ROWID's I want to update/insert into another table in database. How do I do this? Do I have first store the RowId's in cache or is there any other way to insert/update using queries

You want to use the ROWIDs gained through the listener to SELECT data from the table that gave you the ROWIDs and to insert that data into another table in the same database? In other words, you're looking to write that statement? Or, do you want to batch the ROWIDs up rather than executing your statement on every event? — Ben, Jan 04 '15 at 19:10
Anyway will do. I just want to update the other table in same database. Which way is better? — , Jan 04 '15 at 19:14
You can't update a row in a different table as a ROWID is unique per table. It won't necessarily exist in another. All you can use it for is accessing the table you got it from. You can then use this to SELECT data from that table... if that's all you need then there's a [huge amount of information in the docs](http://docs.oracle.com/javase/7/docs/api/java/sql/package-summary.html). — Ben, Jan 04 '15 at 19:29
How do I even select the data as the RowId's returned are alphanumberic. So how would the select query look like ? Could you please show me an example. Thanks Ben — , Jan 04 '15 at 20:12

score 0 · Answer 1 · answered Jul 09 '15 at 15:38

Inserting into another table using SQL :

Insert into table2 (column1,column2,....) (select column1,column2,.... from table1 where rowid = retrived_rowid);

If you want to use C# to insert into the second table using the rowids from the first table, you have to somehow persist the data in your application and then read your array/list and insert the data into the other table one tuple at a time. There's a method in ODP.net to insert multiple lines into a table in batch mode.

You can only update your original table with the retrieved rowids, since rowids are the physical row identifier and it's unique for each tuple in the database.

Concept wise what you said is similar to my requirement but I am looking at java approach. Its been 6months I left the project unattended. I will have a look at it again — , Jul 10 '15 at 13:27

Update another table in database using RowId's returned from database events

1 Answers1