Please follow below given steps for using concept of calling java class from Oracle 11.1 Db.
Step 1: Before using concept of calling java from Oracle Function/Store Procedure or Trigger check properly that oracle contains inbuilt JVM for the same.
We can use following given query to check the same.
select status, count(status) from all_objects where object_type=’JAVA CLASS’ group by status;
if the output of above query is greater than 0 (zero) i.e. in most case count(status) will be greater than 10,000 and status should be Valid
If the above query doesn’t work indicate that JAVA is not present in Oracle
Please run following command $ORACLE_HOME/javavm/install/initjvm (this will install java in Oracle). Do validate again from above query for proper installation.
Step 2: Inbuild java of Oracle provide loadjava command to perform operation of using JAVA Class from Oracle Function/Store Procedure or Trigger.
There are two ways to use this command.
- By using Oracle SQL Developer loadjava GUI functionality
- By using loadjava command from Command Prompt.
- By using Oracle SQL Developer loadjava GUI functionality:
Once the java in installed in Oracle we can find Java option in hierarchy tree of Oracle SQL Developer.
Right click on the java and select Load Java as shown below and fill the Java Source Name with the name of the Java Class and paste the java code inside text area. Submit the form using Apply button.
After successful addition of the java class we will be able to see TestReturnString class inside the Tree Structrure under JAVA folder of Oracle SQL Developer. Confirm the Status of that loaded class into oracle using following sql script.
select owner, object_name, status from all_objects where object_name like ‘% TestReturnString %’ and object_type like ‘JAVA%’;
This will give Status as Valid. In case of InValid status we can see the error by using following sql script
SELECT name, type, sequence, substr(text,0,100) FROM user_errors where name like ‘% TestReturnString %’;
Remove all the error as stated by the above sql and run the following script to chagne the status of the class from InValid to Valid.
ALTER JAVA class “TestString” resolve;
Finally create a Oracle Function or Procedure to check the loaded java classe execution
I.e.
create or replace
FUNCTION pMeProcFunction (
message VARCHAR2
)
RETURN STRING AS LANGUAGE JAVA
NAME ‘TestReturnString.getMessage(java.lang.String) return java.lang.String’;
==Java Class
public class TestReturnString {
public static String getMessage(String strHello)
{
return strHello;
}
}
=========Execution
select pmeprocfunction(‘test’) from dual;
- By using loadjava command from Command Prompt:
This is the proper way of using loadjava functionality or oracle. As it allows end user to understand the complete flow of the loadjava
Before using loadjava command on prompt concept we need to take care of following below given notes: (In our case as we had our Oracle on Linux M/C)
1) Set PATH,CLASSPATH,ORACLE_HOME variable. Better to us bash_profile of linux
i.e. vi ~/.bash_profile
export PATH=$PATH:$HOME/bin:/app/jdk1.6.0_06/bin
export CLASSPATH=.:/app/jdk1.6.0_06
export ORACLE_HOME=/app/oracle/base/
#export PATH
unset USERNAME
2) Most important point to follow is to set all the class path needed for loadjava command using inbuild helloworld java example by Oracle J.
In our case move to
[root@linux-ent1 /]# cd app/oracle/base/javavm/demo/examples/jsproc/basic/helloWorld/
and run make command to set all the call path
3) Use the same prompt to upload the class in oracle using following given command better to put only class file on linux m/C
/app/oracle/base/bin/loadjava -f -verbose -oracleresolver -resolve -oci8 -u userid/pwd@SIDNAME /Siddhu/RabbitMQTrigger/bin/com/dbMQTrigger/TestReturnString.class
And test the above deployed java class using procedure/function of Oracle.
Now let discuss on real Business ……………… HOW TO PERFORM OPERATION ON RABBITMQ WHEN A TABLE GET UPDATED.
For above concept we had used following business model
Db Table à On update/delete/Insert will fire à Trigger à Trigger will call Stored Procedure à Stored Procedure will call Java Class à Finally JAVA Class will insert message into Rabbit MQ.
Now as stated above we are using Rabbit MQ as messaging bus the class which is used to insert data into Rabbit MQ contain imports some of the java class which need RabbitMQ inbuilt jar to be loaded first into the JVM of the Oracle. Here comes the real picture of using concept step2 where we need to use loadjava on command prompt, as Oracle SQL Developer and TOAD does not support loading of JAVA jar file like class file.
Approach taken was as follows:
1) First load all the jar files which is need by our class that is used to insert message in Rabbit Mq i.e. commons-io-1.2.jar, rabbitmq-client.jar, rabbitmq-client-tests.jar and commons-cli-1.1.jar
Using following command
/app/oracle/base/ bin/loadjava -f -verbose -oracleresolver -resolve -oci8 -u userid/pwd@SIDNAME /Siddhu/RabbitMQTrigger/lib/commons-cli-1.1.jar
/app/oracle/base/ bin/loadjava -f -verbose -oracleresolver -resolve -oci8 -u userid/pwd@SIDNAME /Siddhu/RabbitMQTrigger/lib/commons-io-1.2.jar
/app/oracle/base/bin/loadjava -f -verbose -oracleresolver -resolve -oci8 -u userid/pwd@SIDNAME /Siddhu/RabbitMQTrigger/lib/rabbitmq-client.jar
/app/oracle/base/ bin/ loadjava -f -verbose -oracleresolver -resolve -oci8 -u userid/pwd@SIDNAME /Siddhu/RabbitMQTrigger/lib/rabbitmq-client-tests.jar
2) Finally load our java class which actually insert data into RabbitMQ.
/app/oracle/base/ bin/loadjava -f -verbose -oracleresolver -resolve -oci8 -u userid/pwd@SIDNAME /Siddhu/RabbitMQTrigger/bin/com/dbMQTrigger/RMQNOPARAM.class
Or use Oracle GUI loadjava tool for the same.
Finally write the following Trigger/Procedure and execute the see all working properly:
===Trigger
create or replace
TRIGGER TESTTR
AFTER INSERT OR DELETE OR UPDATE ON TEST_RABBITMQ
–REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
dbms_output.put_line(‘Trigger Start’);
— insert into TESTTRIGGER values (‘first’);
–pMeProc(‘hi’);
pMeProc();
— insert into TESTTRIGGER values (‘second’);
dbms_output.put_line(‘Trigger End’);
–commit;
END;
===Procedure
create or replace
PROCEDURE pMeProc
AS LANGUAGE JAVA
NAME ‘RMQNOPARAM.publishMessage()’;
==JAVA Class
import com.rabbitmq.client.Channel;
import com.rabbitmq.client.Connection;
import com.rabbitmq.client.ConnectionFactory;
public class RMQNOPARAM {
public static void publishMessage()
{
try
{
Connection conn = null;
ConnectionFactory factory = new ConnectionFactory();
factory.setHost(“localhost”);
conn = factory.newConnection();
Channel chan = conn.createChannel();
String exchangeName = “TestDbTriggerRabbitMQExchange”;
String routingKey = “TestDbTriggerRabbitMQRoutingKey”;
//System.out.println(” Value of strHello is:”+strHello);
chan.basicPublish(exchangeName, routingKey, null, “fromjava”.getBytes());
//System.out.println(” Published Message ‘Hello World!'”);
chan.close();
conn.close();
}catch(Exception e)
{
e.printStackTrace();
}
}
}
==sql command
insert into test_rabbitMQ values (‘helloTrigger’)
your good
LikeLike
Thanks 🙂
LikeLike
Great template! Was it free?
LikeLike
yes 🙂
LikeLike
i am just wondering for which use case we really need a messaging system such rabitMQ? How oft is your update operation will be called?
LikeLike
In our case we were having situation where database is updated by more than 4 to 5 system running parallel to each other. We want to be very sure that only that system who will update the Db table need to be allowed to insert message in rabbit mq so that respective operation can be handle.
LikeLike
your tutorial is very good and very simple to understand.I’m reached here while I’m searching how to run ‘loadjava’ in command prompt of linux.Your descroption helped me to resolve that Also got idea what this ‘loadjava’ utility is.Thanks Once again ….:)
LikeLike
Thx for comments.
LikeLike
Hello
I am trying to get Oracle and RabbitMQ to talk. I have followed your blog and set permissions as well
exec dbms_java.grant_permission( ‘DANIELSMITH’, ‘SYS:java.net.SocketPermission’, ‘dc2rabbitmq1.carfax.cfx’, ‘connect,resolve’ );
exec dbms_java.grant_permission( ‘DANIELSMITH’, ‘SYS:java.net.SocketPermission’, ‘172.18.53.63:5672’, ‘connect,resolve’ );
but now I get this error in the trace file
java.net.SocketException: Address family not supported by protocol
Address family not supported by protocol
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java)
at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java)
at java.net.Socket.connect(Socket.java)
at com.rabbitmq.client.ConnectionFactory.createFrameHandler(ConnectionFactory.java:445)
at com.rabbitmq.client.ConnectionFactory.newConnection(ConnectionFactory.java:504)
at com.rabbitmq.client.ConnectionFactory.newConnection(ConnectionFactory.java:533)
at Rabbitmq_helper.publishMessage(Rabbitmq_helper.java:14)
Have you seen this error? I am running 11.2.0.2 64-bit linux OEL5.5
thanks
LikeLike
Usually it means the destination host is not reachable, or that the destination port is not available. This may be any other software like antivirus and firewall is creating issue on your PC better you disable them temporarily.
LikeLike
thanks for the suggestions. It was the Oracle JVM was trying to use IPv6 first and throwing the error.
I put this in my java
static {
System.setProperty(“java.net.preferIPv4Stack”, “true”);
}
now it just tries IPv4 and now works.
Thanks
LikeLike
Hello, this post is very helpful. We have recently started using RabbitMQ and need to talk to/from it with Oracle. Have you done the opposite? i.e. Receiving a message from RabbitMQ and put it in Oracle?
LikeLike
We are experiencing a problem that for high volumes of messages, the RabbitMQ connection gets dropped. If we put a short pause (Thread.sleep(2000)) after every 10,000 messages the problem goes away. Running the same volume outside of Oracle, with the same JVM (1.5) does not present the problem. Any ideas?
LikeLike
Hi Steve
Generally RMQ are made for high performance applications. It is build on OTP and made with terms not fail at any time giving 99.9% performance and hence it is used now by most of the telecom section. In this we need to check how many messages (accurate volume) we are transferring to RMQ and what type of configuration you are running RMQ server i.e. In the cluster mode or Topic/Fan out/PTP etc… Lowering the time by 2Sec is enough for RMQ to drill down its message.
Let me know if want more information.
Take care.
S Dhumale
LikeLike
For Oracle DB also , we need to follow the same process ?
How can i run the loadjava command if I am unable to place my jars in the server path ?
As a developer, I just get the DB details to connect and work.. Please help !
LikeLike
yes u are right..
LikeLike