Calling JAVA class from Oracle using loadjava. Inserting message in RabbitMQ on updating Oracle Db.

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’)