This content has been marked as final. Show 8 replies
-- 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
I am using PL/SQL directly to send JMS text messages. Works fine. Here is example:
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);
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
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?
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);
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.
Have you measured the performance of the pl/sql sample you posted?
Thanks for your help, it's working now!
We'll use Java stored procedures and keep
your comments about performance in mind.
I am going to enque using JMS queue in Oracle apps database.
Is that feasible.