HiPlease 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 ValidIf the above query doesn’t work indicate that JAVA is not present in OraclePlease 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 scriptSELECT 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 executionI.e.create or replaceFUNCTION pMeProcFunction (message VARCHAR2)RETURN STRING AS LANGUAGE JAVANAME 'TestReturnString.getMessage(java.lang.String) return java.lang.String';==Java Classpublic class TestReturnString {public static String getMessage(String strHello){return strHello;}}=========Executionselect 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 loadjavaBefore 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 linuxi.e. vi ~/.bash_profileexport PATH=$PATH:$HOME/bin:/app/jdk1.6.0_06/binexport CLASSPATH=.:/app/jdk1.6.0_06export ORACLE_HOME=/app/oracle/base/#export PATHunset USERNAME2) 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 path3) 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.classAnd 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 modelDb 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.jarUsing 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.jar2) 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.classOr use Oracle GUI loadjava tool for the same.Finally write the following Trigger/Procedure and execute the see all working properly:===Triggercreate or replaceTRIGGER TESTTRAFTER INSERT OR DELETE OR UPDATE ON TEST_RABBITMQ--REFERENCING OLD AS OLD NEW AS NEWFOR EACH ROWBEGINdbms_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;===Procedurecreate or replacePROCEDURE pMeProcAS LANGUAGE JAVANAME 'RMQNOPARAM.publishMessage()';==JAVA Classimport 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("");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')