Gurpreet Singh Gurpreet Singh - 5 months ago 17
Java Question

ORA-29977: Unsupported column type for query registration in guaranteed mode

I am trying to perform

DatabaseChangeListener
from my java class and i am getting the following error:


ORA-29977: Unsupported column type for query registration in
guaranteed mode.


I need an implementation where i should get notified whenever there is a new row inserted in table.

This is the code i tried, for getting the change notified to java:

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.Properties;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleDriver;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.DatabaseChangeEvent;
import oracle.jdbc.dcn.DatabaseChangeListener;
import oracle.jdbc.dcn.DatabaseChangeRegistration;

public class DBChangeNotification
{
static final String USERNAME= "artl";
static final String PASSWORD= "artl";
static String URL="jdbc:oracle:thin:@localhost:1521:orclaq";

public static void main(String[] argv)
{
/*if(argv.length < 1)
{
System.out.println("Error: You need to provide the URL in the first argument.");


System.exit(1);
}*/
// URL = argv[0];
DBChangeNotification demo = new DBChangeNotification();
try
{
demo.run();
}
catch(SQLException mainSQLException )
{
mainSQLException.printStackTrace();
}
}

void run() throws SQLException
{
OracleConnection conn = connect();

// first step: create a registration on the server:
Properties prop = new Properties();

// if connected through the VPN, you need to provide the TCP address of the client.
// For example:
// prop.setProperty(OracleConnection.NTF_LOCAL_HOST,"14.14.13.12");

// Ask the server to send the ROWIDs as part of the DCN events (small performance
// cost):
prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS,"true");
//
//Set the DCN_QUERY_CHANGE_NOTIFICATION option for query registration with finer granularity.
prop.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION,"true");
//prop.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_VSESSION_OSUSER,"artl");
//prop.setProperty(OracleConnection.CONNECTION_PROPERTY_PASSWORD,"artl");

// The following operation does a roundtrip to the database to create a new
// registration for DCN. It sends the client address (ip address and port) that
// the server will use to connect to the client and send the notification
// when necessary. Note that for now the registration is empty (we haven't registered
// any table). This also opens a new thread in the drivers. This thread will be
// dedicated to DCN (accept connection to the server and dispatch the events to
// the listeners).
DatabaseChangeRegistration dcr = conn.registerDatabaseChangeNotification(prop);

try
{
System.out.println("try");
// add the listenerr:
DCNDemoListener list = new DCNDemoListener(this);
dcr.addListener(list);

// second step: add objects in the registration:
Statement stmt = conn.createStatement();
// associate the statement with the registration:
((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);
ResultSet rs = stmt.executeQuery("select * from act_code_metadata where product_id=1159");
System.out.println("rs.next() "+rs.next());
while (rs.next())
{}
String[] tableNames = dcr.getTables();
System.out.println("tableNames "+tableNames);
for(int i=0;i<tableNames.length;i++)
System.out.println(tableNames[i]+" is part of the registration.");
rs.close();
stmt.close();
}
catch(SQLException ex)
{
System.out.println("Exception here 3");
// if an exception occurs, we need to close the registration in order
// to interrupt the thread otherwise it will be hanging around.
if(conn != null)
conn.unregisterDatabaseChangeNotification(dcr);
throw ex;
}
finally
{
try
{
// Note that we close the connection!
conn.close();
}
catch(Exception innerex){ innerex.printStackTrace();
System.out.println("Exception here 4");
}
}

synchronized( this )
{
// The following code modifies the dept table and commits:
try
{
Date date=new Date();
OracleConnection conn2 = connect();
conn2.setAutoCommit(false);
Statement stmt2 = conn2.createStatement();
stmt2.executeUpdate("insert into act_code_metadata (ACT_CODE,PRODUCT_ID,ACT_ACTION,ACT_EXECUTION_ORDER,REQUEST_STRC_ID,CREATION_DATE,LAST_UPDATE_DATE) values ('ACT_CODE_1151',1151,'cool dept',1,1,"+date+","+date+")",
Statement.RETURN_GENERATED_KEYS);
ResultSet autoGeneratedKey = stmt2.getGeneratedKeys();
if(autoGeneratedKey.next())
System.out.println("inserted one row with ROWID="+autoGeneratedKey.getString(1));
stmt2.executeUpdate("insert into act_code_metadata (ACT_CODE,PRODUCT_ID,ACT_ACTION,ACT_EXECUTION_ORDER,REQUEST_STRC_ID,CREATION_DATE,LAST_UPDATE_DATE) values ('ACT_CODE_1151',1151,'cool dept',1,1,"+date+","+date+")",

Statement.RETURN_GENERATED_KEYS);
autoGeneratedKey = stmt2.getGeneratedKeys();
if(autoGeneratedKey.next())
System.out.println("inserted one row with ROWID="+autoGeneratedKey.getString(1));
stmt2.close();
conn2.commit();
conn2.close();
}
catch(SQLException ex) { ex.printStackTrace();
System.out.println("Exception here 1");
}

// wait until we get the event
try{ this.wait();} catch( InterruptedException ie ) {
System.out.println("Exception here 2");
}
}

// At the end: close the registration (comment out these 3 lines in order
// to leave the registration open).
OracleConnection conn3 = connect();
conn3.unregisterDatabaseChangeNotification(dcr);
conn3.close();
}

/**
* Creates a connection the database.
*/
OracleConnection connect() throws SQLException
{
OracleDriver dr = new OracleDriver();
Properties prop = new Properties();
prop.setProperty("user",DBChangeNotification.USERNAME);
prop.setProperty("password",DBChangeNotification.PASSWORD);
return (OracleConnection)dr.connect(DBChangeNotification.URL,prop);
}
}
/**
* DCN listener: it prints out the event details in stdout.
*/
class DCNDemoListener implements DatabaseChangeListener
{
DBChangeNotification demo;
DCNDemoListener(DBChangeNotification dem)
{
demo = dem;
}
public void onDatabaseChangeNotification(DatabaseChangeEvent e)
{
Thread t = Thread.currentThread();
System.out.println("DCNDemoListener: got an event ("+this+" running on thread "+t+")");
System.out.println(e.toString());
synchronized( demo ){ demo.notify();}
}
}


Kindly suggest me how to resolve this issue. Thanks in advance.

Answer

Your problem is here:

  Statement stmt = conn.createStatement();
  // associate the statement with the registration:
  ((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);
 ResultSet rs = stmt.executeQuery("select * from act_code_metadata where      product_id=1159");
  System.out.println("rs.next() "+rs.next());
  while (rs.next())
  {}

The error is telling you that you have an unsupported column type in the DCN registration. Maybe a blob or clob. As I cannot see your table structure, please edit the query and select - build up - one column at a time. You are not doing any work with the result set columns anyway so this should be easy to find.