1 Reply Latest reply: Sep 4, 2012 8:35 AM by 628404 RSS

    Procedure Function to dequeue a message and return cursor

    628404
      Hi Guys,

      We are looking for ways to dequeue a message from an oracle queue and pass it to the java code.

      Namely we would like to create a procedure/function to dequeue a message which can be called by the java code for further processing.

      So far we have the following working example, but it uses DBMS_OUTPUT.PUT_LINE to display values. But the target is to pass it to java...

      Our DB - 11gR2

      CODE:

      create or replace
      PROCEDURE PRC_DEQUEUE_EVENT_1

      AS

      dequeue_options DBMS_AQ.dequeue_options_t;
      message_properties DBMS_AQ.message_properties_t;
      message_handle RAW(16);
      message our_message_type;
      BEGIN

      dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE;

      DBMS_AQ.DEQUEUE(
      queue_name => 'ORDERS_Q',
      dequeue_options => dequeue_options,
      message_properties => message_properties,
      payload => message,
      msgid => message_handle);
      DBMS_OUTPUT.PUT_LINE('STATUS: '|| MESSAGE.CORDYS_STATUS);
      DBMS_OUTPUT.PUT_LINE('CUSTOMERNUMBER: '|| MESSAGE.CUSTOMERNUMBER);
      DBMS_OUTPUT.PUT_LINE('EINDDATUM: '|| MESSAGE.EINDDATUM);
      DBMS_OUTPUT.PUT_LINE('ERRORCODE: '|| MESSAGE.ERRORCODE);
      DBMS_OUTPUT.PUT_LINE('EXTRA1: '|| MESSAGE.EXTRA1);
      DBMS_OUTPUT.PUT_LINE('EXTRA2: '|| MESSAGE.EXTRA2);
      DBMS_OUTPUT.PUT_LINE('EXTRA3: '|| MESSAGE.EXTRA3);
      DBMS_OUTPUT.PUT_LINE('EXTRA4: '|| MESSAGE.EXTRA4);
      DBMS_OUTPUT.PUT_LINE('EXTRA5: '|| MESSAGE.EXTRA5);

      END;

      In a result we get the following on the screen:

      STATUS: ORDER_COMPLETE
      CUSTOMERNUMBER:
      EINDDATUM: 20-DEC-11 12:00:00
      ERRORCODE:
      EXTRA1:
      EXTRA2:
      EXTRA3:
      EXTRA4:
      EXTRA5:


      Question is how to sent result into a variable/cursor... whatever which might be read by the java code.

      Any help would be much appreciated.

      Thanks!
      eMarcel

      Edited by: eMarcel on Jan 16, 2012 3:56 PM