我正在通过JMS客户端将JSON消息排入JVM上的Oracle AQ。这对于小型文本消息很有效，但对于较大的消息则失败了。我认为这与Oracle对较小的消息使用VARCHAR，对大于4000个字符的消息切换到CLOB有关。
AQ数据库脚本是
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;
/
oracle依赖项通过Maven配置为
<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>
异常堆栈跟踪是
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
我能够在AQjmsUtil.writeClob中放置一个断点，我可以看到下面的引发了异常
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();
CLOB逻辑执行UPDATE，而对于使用VARCHAR的较小字符串，我相信它只执行INSERT。有问题的UPDATE语句是
UPDATE MY.AQT_MY_INBOX tab set tab.user_data.text_lob = ? where tab.msgid = ?
通过执行以下GRANT脚本修复了该问题
GRANT SELECT, UPDATE ON MY.AQT_MY_INBOX TO MY_USER;
这个解决方案的奇怪之处在于，我不需要为这个表授予INSERT权限(只有为覆盖的队列授予ENQUEUE权限(。我觉得这是oracle实现中的一个小错误，INSERT和UPDATE授权都应该由ENQUEUE授权添加。要么，要么JMS API应该为CLOB执行单个INSERT，而不是UPDATE(类似于VARCHAR的实现方式(