Can't enqueue large text messages onto Oracle AQ via JMS client - ORA-00942: table or view does not exist

I am enqueuing JSON messages onto Oracle AQ on the JVM via the JMS client. This is working fine for small text messages but is failing for larger messages. I believe that this is something to do with Oracle using VARCHAR for smaller messages and switching to CLOB for messages larger than 4000 characters.

The AQ database script is

BEGIN
    DBMS_AQADM.CREATE_QUEUE_TABLE (
        queue_table => 'MY.AQT_MY_INBOX',
        queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',
        comment => 'QueueTable for MY Inbox Messages',
        multiple_consumers => FALSE,
        sort_list => 'priority,enq_time'
    );

    DBMS_AQADM.CREATE_QUEUE (
        queue_name => 'MY.AQ_MY_INBOX',
        comment => 'Queue for MY Inbox Messages',
        queue_table => 'MY.AQT_MY_INBOX',
        queue_type => SYS.DBMS_AQADM.NORMAL_QUEUE,
        max_retries => 2880,
        retry_delay => 30
    );

    DBMS_AQADM.GRANT_QUEUE_PRIVILEGE(
        privilege => 'ENQUEUE',
        queue_name => 'MY.AQ_MY_INBOX',
        grantee => 'MY_USER'
    );

    DBMS_AQADM.START_QUEUE (
        queue_name => 'MY.AQ_MY_INBOX'
    );
END;
/

The oracle dependencies are configured via Maven as

<dependency>
    <groupId>com.oracle.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>18.3.0.0</version>
</dependency>
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>aqapi_g</artifactId>
    <version>11.2.0.4</version>
</dependency>

The exception stack trace is

Caused by: oracle.jms.AQjmsException: ORA-00942: table or view does not exist
    at oracle.jms.AQjmsUtil.writeClob(AQjmsUtil.java:640)
    at oracle.jms.AQjmsTextMessage.writeLob(AQjmsTextMessage.java:294)
    at oracle.jms.AQjmsProducer.jdbcEnqueue(AQjmsProducer.java:1054)
    at oracle.jms.AQjmsProducer.send(AQjmsProducer.java:747)
    at oracle.jms.AQjmsProducer.send(AQjmsProducer.java:517)
    at org.springframework.jms.core.JmsTemplate.doSend(JmsTemplate.java:634)
    at org.springframework.jms.core.JmsTemplate.doSend(JmsTemplate.java:608)
    at org.springframework.jms.core.JmsTemplate.lambda$send$3(JmsTemplate.java:586)
    at org.springframework.jms.core.JmsTemplate.execute(JmsTemplate.java:504)
    ... 20 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1052)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:537)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:255)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:610)
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:249)
    at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:82)
    at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:924)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1136)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3640)
    at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1318)
    at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3730)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3710)
    at oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:4265)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1061)
    at oracle.jms.AQjmsUtil.writeClob(AQjmsUtil.java:605)
    ... 28 common frames omitted
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00942: table or view does not exist
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
    ... 44 common frames omitted

I was able to put a breakpoint in AQjmsUtil.writeClob and I could see that the exception was being thrown for the following

clobStmt = (OracleCallableStatement)db_conn.prepareCall("UPDATE " + queueTable + " tab set tab.user_data.text_lob = ? where tab.msgid = ?");
clobStmt.setString(1, textData);
clobStmt.setBytes(2, msgid);
int count = clobStmt.executeUpdate();

The CLOB logic is doing an UPDATE whereas for smaller strings where VARCHAR is used I believe it's only doing an INSERT. The offending UPDATE statement was

UPDATE MY.AQT_MY_INBOX tab set tab.user_data.text_lob = ? where tab.msgid = ?

The issue was fixed by executing the following GRANT script

GRANT SELECT, UPDATE ON MY.AQT_MY_INBOX TO MY_USER;

The strange thing about this solution is that I don't need an INSERT grant for this table (there's only the ENQUEUE grant for the overlying queue). I feel this is a bit of a bug in the oracle implementation and that both the INSERT and UPDATE grants should be added by the ENQUEUE grant. Either that or the JMS API should do a single INSERT and not an UPDATE for CLOB (similar to how VARCHAR is implemented)

Recommended topics

Hot tags