8 Replies Latest reply: Jun 29, 2012 7:20 AM by Sudipto Chatterjee RSS

    AQ (PL/SQL) and JMS access to same queue?

    3004
      Hello,

      I have a queue of type JMS-MapMessage for which I want to enqueue messages with the
      AQ PL/SQL API. How would I define it's
      payload since apparently the message content
      cannot be displayed by sqlplus? Alternatively, how can I set my own properties for a JMS-type queue in PL/SQL?
      I'd be glad if I could get to see an example
      of a PL/SQL-ENQUEUE to a JMS-type queue!

      Thanks,
      Karin
        • 1. AQ (PL/SQL) and JMS access to same queue?
          3004
          -- For standard JMS type messages -( ie queues with payload type AQ$_JMS_* ) it is recommended that you use the JMS api. If you need to do it in the server using PL/SQL - you can define PL/SQL wrappers for Java Stored procedures and use the JMS api to do the actual send/publish.
          -- For queues with ADT payloads, you can use the PL/SQL api as well as the Oracle JMS extensions (AdtMessage) to access them transparently from any API. However, for ADT type queue user-defined properties are not supported. But you can have properties in the message payload itself and also define selectors on the message content
          • 2. AQ (PL/SQL) and JMS access to same queue?
            3004
            I am using PL/SQL directly to send JMS text messages. Works fine. Here is example:

            DECLARE
            Enqueue_options DBMS_AQ.enqueue_options_t;
            Message_properties DBMS_AQ.message_properties_t;
            Message_handle RAW(16);
            User_prop_array SYS.AQ$_JMS_USERPROPARRAY;
            Agent SYS.AQ$_AGENT;
            Header SYS.AQ$_JMS_HEADER;
            Message SYS.AQ$_JMS_TEXT_MESSAGE;
            Message_text VARCHAR2(100);
            BEGIN

            Agent := SYS.AQ$_AGENT('',NULL,0);
            User_prop_array := SYS.AQ$_JMS_USERPROPARRAY();
            Header := SYS.AQ$_JMS_HEADER( Agent, '', 'aq1', '', '', '', User_prop_array);
            Message_text := 'Message 1 from PL/SQL';
            Message := SYS.AQ$_JMS_TEXT_MESSAGE(Header, LENGTH(Message_text), Message_text, NULL);



            DBMS_AQ.ENQUEUE(queue_name => 'aq1.webapp_queue',
            Enqueue_options => enqueue_options,
            Message_properties => message_properties,
            Payload => message,
            Msgid => message_handle);

            COMMIT;
            END;
            /
            • 3. AQ (PL/SQL) and JMS access to same queue?
              3004
              Originally posted by bnainani ():
              If you need to do it in the server using PL/SQL - you can define PL/SQL wrappers for Java Stored procedures and use the JMS api to do the actual send/publish.

              Thanks for your reply, we figured out that this is what we need to do. But how do I
              access the ConnectionFactory with a java class inside the database? It seems a bit
              strange to connect from the database via
              jdbc to the same database - should this
              work?

              null
              • 4. AQ (PL/SQL) and JMS access to same queue?
                3004
                Sorry for coming back to this, but we're still having trouble with it: We figured
                out we have to use Java Stored Procedures
                (since we need JMS-Map-type messages with
                user-defined properties), but when we try
                to load the oracle jdbc (and aurora) jars into the database we can't give any user the necessary permissions. Are these systems
                classes which are excluded from changes? If
                so, how do you get the jdbc-using java
                classes to run?

                Thanks again,
                Karin
                • 5. AQ (PL/SQL) and JMS access to same queue?
                  3004
                  If you are using an 8i "java-enabled database", the JDBC classes should already be loaded. In 8.1.7 java-enabled databases, the AQ jars are also preloaded.

                  If they are not, you can use
                  the $ORACLE_HOME/admin/initjms.sql script to load the AQ/JMS classes. You need to load these as SYS. This script will also create synonyms and grant access to PUBLIC

                  Also regarding the JDBC connection, you don't need to create a new connection. Inside the Java stored procedure, you just need to get the default connection using

                  OracleDriver ora_drv = new OracleDriver();
                  OracleConnection conn = (OracleConnection)(ora_drv.defaultConnection());

                  Then use static methods in AQjmsTopicConnectionFactory or AQjmsQueueConnectionFactory as follows:

                  TopicConnection tConn = AQjmsTopicConnectionFactory.createTopicConnection(conn);
                  • 6. AQ (PL/SQL) and JMS access to same queue?
                    3004
                    We have been experimenting with AQ and JMS as well. We are trying to enqueue data via a pl/sql trigger to be consumed by a java application using jms. As Bhagat suggested in the forum, we have a pl/sql wrapped java stored procedure that does the enqueue. Problem is, performance is very slow.
                    There is some overhead converting the sql data to java data in the stored procedure. However, the largest bottleneck is the jms api code in the java stored procedure. We are seeing performance in the range of 5 inserts/sec. Note: insert row into table includes fire trigger to jms enqueue data from the row.

                    I'd be interested in hearing what kind of performance numbers others are seeing with this approach.

                    Vlad,
                    Have you measured the performance of the pl/sql sample you posted?

                    null
                    • 7. AQ (PL/SQL) and JMS access to same queue?
                      3004
                      Thanks for your help, it's working now!
                      We'll use Java stored procedures and keep
                      your comments about performance in mind.

                      Thanks again,
                      Karin
                      • 8. Re: AQ (PL/SQL) and JMS access to same queue?
                        Sudipto Chatterjee
                        Hi,

                        I am going to enque using JMS queue in Oracle apps database.

                        Is that feasible.

                        Regards