是否可以使用JDBC从ANYDATA同步捕获队列中出队? [英] Is it possible to dequeue from ANYDATA Synchronous Capture queue using JDBC?

问题描述

我想做的是使用来自Java进程的同步捕获.

ANYDATA队列是使用

ANYDATA queue is created with

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table  => 'hcb_qtab_any',
    queue_name   => 'hcb_queue_any',
    queue_user   => 'gguser');
END;

然后我正在使用使用Java for Oracle 11g队列出队的代码作为示例.我正在尝试

And then I'm using code from Dequeue using Java for Oracle 11g queue as an example. I'm trying to do

message = queue.dequeue(deq_option, XMLType.getORADataFactory());

但是我得到的只是oracle.AQ.AQOracleSQLException:创建描述符时出错:Invalid arguments.事实证明，我已经对其进行了调试，因为ANYDATA TypeDescriptor.getTypeDescriptor()将返回OpaqueDescriptor，而该OpaqueDescriptor不被视为StructDescriptor.isValidObject().

But all I get is oracle.AQ.AQOracleSQLException: Error creating descriptor: Invalid arguments. I've debugged it a bit, it turns out, for ANYDATA TypeDescriptor.getTypeDescriptor() will return OpaqueDescriptor which isn't considered as StructDescriptor.isValidObject().

我还使用瘦JDBC AQ做了另一件事:

I did another take using thin JDBC AQ:

AQDequeueOptions deqopt = new AQDequeueOptions();
deqopt.setConsumerName("subscriber1");
AQMessage msg = conn.dequeue("hcb_queue_any", deqopt, "SYS.ANYDATA");
OPAQUE opq = (OPAQUE)msg.getANYDATAPayload().accessDatum();

我在这里面临一个新的独特问题.该OPAQUE具有getDescriptor().getTypeName() == "XMLTYPE"，因此我非常想将其转换为XML.但是有一个问题:只有瘦JDBC驱动程序支持AQ，而只有ACI驱动程序支持将OPAQUE转换为XML.尝试执行new XMLType(opq)

And I'm facing a new unique problem here. This OPAQUE has getDescriptor().getTypeName() == "XMLTYPE", so I would very much like to turn it to XML. But there's a problem: Only thin JDBC driver supports AQ, while only ACI driver supports turning OPAQUE to XML. I'm getting Only LOB or String Storage is supported in Thin XMLType error when I'm trying to do new XMLType(opq)

如何获取同步捕获XML 使用JDBC从AQ中获取?

How do I get Synchronous Capture XML from AQ using JDBC?

推荐答案

您可以编写存储过程，并在PL/SQL中使消息出队，然后将其转换为XML文本并作为CLOB返回.然后，您可以使用JDBC从Java调用存储过程.在普通的AQ API缺少功能之前，我曾使用过类似的解决方法.

You could write a stored procedure and dequeue the message in PL/SQL and convert it to XML text and return it as a CLOB. Then you can call the stored procedure from Java with JDBC. I have used a similar workaround before when the plain AQ API lacked a feature.

示例:

create or replace procedure dequeue_lcr(
    p_queue_name   varchar2,
    p_consumer     varchar2,
    p_wait_seconds number,
    p_lcr          out clob) as
  deq_lcr     anydata;
  deq_xml     xmltype;
  msgid       raw(16); 
  deqopt      dbms_aq.dequeue_options_t; 
  mprop       dbms_aq.message_properties_t;
  no_messages exception; 
  pragma exception_init (no_messages, -25228);
begin
  deqopt.consumer_name := p_consumer;
  deqopt.wait := p_wait_seconds;
  deqopt.navigation := dbms_aq.first_message;
  deqopt.dequeue_mode  := dbms_aq.remove;
  begin
    dbms_aq.dequeue( 
      queue_name         =>  p_queue_name,
      dequeue_options    =>  deqopt,
      message_properties =>  mprop,
      payload            =>  deq_lcr,
      msgid              =>  msgid);
     deq_xml := dbms_streams.convert_lcr_to_xml(deq_lcr);
     p_lcr := deq_xml.getclobval();    
     commit;
  exception
    when no_messages then
      p_lcr := null;
  end;
end;

当我使用适当的队列和使用者从PL/SQL调用它时，此方法有效:

This works when I call it from PL/SQL with the proper queue and consumer:

declare
  v_clob clob;
begin
  dequeue_lcr('aqtest.hcb_queue_any', 'LOCAL_AGENT', 5, v_clob);
  if (v_clob is not null) then
    dbms_output.put_line('Data: ' || v_clob);
  else
    dbms_output.put_line('No messages');  
  end if;
end;

只需使用带有Clob作为输出参数的CallableStatement从Java进行调用，您就可以开始使用！

Just make the call from Java with a CallableStatement with a clob as output parameter and you should be good to go!

这篇关于是否可以使用JDBC从ANYDATA同步捕获队列中出队?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！