1

Is it possible to use UCP to create an OracleConnection to use enqueue method?

Nowadays, which is the best way to enqueue a message into Oracle AQ queue from a java client using a Connection Pool?

Javadoc from OracleDatasource advice about cache deprecation and recommend UCP instead. But I’ve tried to instantiate a connection with it, but is not possible to cast it to OracleConnection, I got a proxy exception instead.

It seems clear that UCP is designed to return common java interfaces like Connection from sql package and is not familiar with Oracle specific use cases?

Somebody can help me to understand how to use an Oracle AQ through a connection pool?

Thank you.

Java dependencies:

  • Spring Boot: 2.4.13
  • com.oracle.database.jdbc:ojdbc8:21.5.0.0
  • com.oracle.database.jdbc:ucp:21.5.0.0

Spring ucp properties:

spring.datasource.type=oracle.ucp.jdbc.PoolDataSource
spring.datasource.oracleucp.connection-factory-class-name=oracle.jdbc.pool.OracleDataSource

Exception message:

java.lang.ClassCastException: oracle.ucp.jdbc.proxy.oracle$1ucp$1jdbc$1proxy$1oracle$1ConnectionProxy$2oracle$1jdbc$1internal$1OracleConnection$$$Proxy cannot be cast to oracle.jdbc.driver.OracleConnection

Source code:

@Autowired
public InsertController(DataSource ds) {
  this.ds = ds;
}

private OracleConnection getConnection() throws Exception {
  Connection con = this.ds.getConnection();
  return (OracleConnection) con; //Proxy error propagation
}
OscarRP
  • 85
  • 2
  • 10
  • 1
    Connection pools do not connect directly to AQ. AQ can only be accessed using PL/SQL, *after* establishing a normal database connection, not *instead* of establishing a database connection. – pmdba Oct 07 '22 at 13:42
  • Yes, I have to establish a connection through connection pool, but is it possible to use UCP to create a normal connection and use that connection to enqueue a message into AQ queue? – OscarRP Oct 07 '22 at 15:43

1 Answers1

2

You can type cast the UCP connection to oracle.jdbc.OracleConnection and then call below API to enqueue the message.
public void enqueue(String queueName, oracle.jdbc.aq.AQEnqueueOptions opt, oracle.jdbc.aq.AQMessage mesg) throws SQLException;

Saurabh Verma
  • 116
  • 4
  • I've tried this, but I've got a proxy error like this during cast: https://support.oracle.com/knowledge/Middleware/2859797_1.html – OscarRP Oct 07 '22 at 18:50
  • Please confirm the JDBC and UCP release version and jar name. For example - For Release version 21.5, JDBC/UCP jar names can be either ojdbc8.jar/ucp.jar or ojdbc11.jar/ucp11.jar. – Saurabh Verma Oct 08 '22 at 16:25
  • I'm using same dependencie version. I've updated owner post with dependencies, properties and initialization process of connection. Please check it, could give you more context. Thank you for your response. – OscarRP Oct 11 '22 at 07:20
  • 2
    Looks like you are type casting the connection to oracle.jdbc.driver.OracleConnection which is wrong. You should type cast it to oracle.jdbc.OracleConnection. This should work. – Saurabh Verma Oct 12 '22 at 05:28