Hey Folks I am writing here a short and crisp blog of how to run a java program from PLSQL and here you go with the code..... buddies
I am loading a Java class by using the LOADJAVA utility.
The command line utility uploads the Java class as a large object in a system-generated table and generates the
shared library.
The loadjava utility can be used as per the following
syntax:
loadjava {-user | -u} username/password[@database] [option...]
filename [filename ]...
Here we go with a Java class which converts lowercase string to uppercase
Vi MainClass.java
class MainClass{
public static final String CheckString(String str){
String s=str.toUpperCase();
str=s;
return str;
}
}
-- From OS load the java program
$ loadjava -user scott/tiger@pdborcl MainClass.java
Since we have loaded java using a Scott user lets login to Scott user
$ sqlplus
username: scott/tiger@pdborcl
Create a function to call a java program
CREATE OR REPLACE FUNCTION MainClass
(x varchar2) return varchar2
is
LANGUAGE JAVA
NAME 'MainClass.CheckString(java.lang.String) return java.lang.String';
/
--Execute Java Program
select mainclass(ename) from emp;
or
SQL> variable x varchar2(20);
SQL> execute :x :='asdfhsdf';
PL/SQL procedure successfully completed.
SQL> execute :x:=mainclass(:x);
PL/SQL procedure successfully completed.
SQL> print x;
X
--------------------------------
We can Call same program from hr or any other user
login to Scott user
conn scott/tiger@pdborcl
-- I am granting the execute privilege to hr
grant Execute on mainclass to hr;
conn hr/hr@pdborcl
execute scott.mainclass(last_name) from employees;
Thank You