使用Database Change Notifier(DCN)检索Blob数据时遇ORA-29977异常求助

Hey there! Let's work through your two questions about Oracle's Database Change Notifier (DCN) and BLOB handling:

DCN is built to alert you to database changes, but it doesn’t natively include full large object (LOB) content in notifications—this is a performance choice, since BLOBs can be massive. Here’s how to get the actual BLOB data when a change occurs:

• Step 1: Configure DCN to capture ROWIDs
  When registering your DCN query, ensure it captures the ROWID of affected rows (this is usually enabled by default, but double-check your settings). Avoid including the BLOB column directly in the DCN registration query—this is a common trigger for the ORA-29977 error you’re seeing.
• Step 2: Extract ROWID from notifications
  When you receive a change alert, parse the RowChangeDescription to pull the ROWID of the modified row(s).
• Step 3: Fetch the BLOB via targeted query
  Use the ROWID to run a focused query for the BLOB data, like:

  SELECT blob_col_nme FROM blobtable WHERE ROWID = :target_rowid
  

  Then use JDBC’s ResultSet.getBlob() method to access the data. For large BLOBs, use streaming with Blob.getBinaryStream() to avoid memory overload.

The ORA-29977 error ("invalid column type in query") almost always happens when you try to include a LOB column directly in a DCN registration query. Oracle’s DCN mechanism doesn’t support tracking LOB columns in initial registrations due to size and performance constraints. Here’s how to fix it:

• Remove BLOB columns from your DCN registration
  If your original DCN query included the BLOB column, rewrite it to only include non-LOB identifiers (like primary keys or ROWID) to track changes. For example:

  SELECT ROWID FROM blobtable
  

• Fetch BLOB data after receiving notifications
  As outlined in the first question, use the ROWID from the DCN alert to run a separate query for the BLOB content once you’re notified of a change.
• Verify driver and permissions (if error occurs outside DCN)
  If the error pops up in a non-DCN context, make sure you’re using a recent Oracle JDBC driver (ojdbc8 or newer) and that your database user has full read permissions on blobtable and its BLOB column.

内容的提问来源于stack exchange，提问作者Ramesh