Using '-jarsasdbobjects -prependjarnames' parameters and calling the loaded class from a Java Stored Procedure
Posted by Steve Racanovic | Posted in Java Stored Procedure | Posted on 12:08 PM
0
An example using loadjava/dropjava with '-jarsasdbobjects -prependjarnames' parameters and trying to access the class in the jar from a java stored procedure.
[oracle@sracanov-au2 ~]$ cat > HelloWorld1.java
public class HelloWorld1 {
public static void main(String[] args){
System.out.println(new HelloWorld1());
}
public String toString(){
return "Hello World 1";
}
public static String getHello(){
return "Hello World 1 !!!";
}
}
Quit
[oracle@sracanov-au2 ~]$ javac HelloWorld1.java
[oracle@sracanov-au2 ~]$ java HelloWorld1
Hello World 1
[oracle@sracanov-au2 ~]$ loadjava -u scott/tiger -jarsasdbobjects -prependjarnames HelloWorld1.java
[oracle@sracanov-au2 ~]$ cat > java_objects.sql
COL object_name format a30
COL object_type format a15
SELECT object_name, object_type, status FROM user_objects WHERE object_type IN ('JAVA SOURCE', 'JAVA CLASS', 'JAVA RESOURCE') ORDER BY object_type, object_name;
Quit
[oracle@sracanov-au2 ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 6 14:38:01 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @java_objects.sql
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ --------------- -------
HelloWorld1 JAVA CLASS INVALID
HelloWorld1 JAVA SOURCE INVALID
SQL> CREATE OR REPLACE FUNCTION HW RETURN VARCHAR2 as LANGUAGE JAVA NAME 'HelloWorld1.getHello() return String';
2 /
Function created.
SQL> select HW() from dual;
HW()
--------------------------------------------------------------------------------
Hello World 1 !!!
SQL> @java_objects.sql
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ --------------- -------
HelloWorld1 JAVA CLASS VALID
HelloWorld1 JAVA SOURCE VALID
SQL> exec dbms_java.dropjava('HelloWorld1');
PL/SQL procedure successfully completed.
SQL> @java_objects.sql
no rows selected
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@sracanov-au2 ~]$ loadjava -u scott/tiger HelloWorld1.java
[oracle@sracanov-au2 ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 7 13:18:44 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @java_objects.sql
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ --------------- -------
HelloWorld1 JAVA CLASS INVALID
HelloWorld1 JAVA SOURCE INVALID
SQL> CREATE OR REPLACE FUNCTION HW RETURN VARCHAR2 as LANGUAGE JAVA NAME 'HelloWorld1.getHello() return String';
2 /
Function created.
SQL> select HW() from dual;
HW()
--------------------------------------------------------------------------------
Hello World 1 !!!
SQL> @java_objects.sql
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ --------------- -------
HelloWorld1 JAVA CLASS VALID
HelloWorld1 JAVA SOURCE VALID
SQL>
[oracle@sracanov-au2 ~]$ mkdir Test
[oracle@sracanov-au2 ~]$ cp HelloWorld1.java HelloWorld2.java
[oracle@sracanov-au2 ~]$ sed -i 's/1/2/g' HelloWorld2.java
[oracle@sracanov-au2 ~]$ sed '1i\package Test;' HelloWorld2.java > Test/HelloWorld2.java
[oracle@sracanov-au2 ~]$ rm HelloWorld2.java
[oracle@sracanov-au2 ~]$ javac -cp .:./Test Test/HelloWorld2.java
[oracle@sracanov-au2 ~]$ java -cp .:./Test Test/HelloWorld2
Hello World 2
[oracle@sracanov-au2 ~]$ loadjava -u scott/tiger -jarsasdbobjects -prependjarnames Test/HelloWorld2.java
[oracle@sracanov-au2 ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 7 14:07:30 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE OR REPLACE FUNCTION HW RETURN VARCHAR2 as LANGUAGE JAVA NAME 'Test.HelloWorld2.getHello() return String';
2 /
Function created.
SQL> select HW() from dual;
HW2()
--------------------------------------------------------------------------------
Hello World 2 !!!
SQL> @java_objects.sql
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ --------------- -------
HelloWorld1 JAVA CLASS VALID
Test/HelloWorld2 JAVA CLASS VALID
HelloWorld1 JAVA SOURCE VALID
Test/HelloWorld2 JAVA SOURCE VALID
SQL> exec dbms_java.dropjava('Test/HelloWorld2');
PL/SQL procedure successfully completed.
SQL> @java_objects.sql
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ --------------- -------
HelloWorld1 JAVA CLASS VALID
HelloWorld1 JAVA SOURCE VALID
SQL> exec dbms_java.dropjava('HelloWorld1');
PL/SQL procedure successfully completed.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@sracanov-au2 ~]$ cat > Manifest.txt
Main-Class: Test.HelloWorld2
Quit
[oracle@sracanov-au2 ~]$ jar cvfm Test.jar Manifest.txt ./Test/*class
adding: META-INF/ (in=0) (out=0) (stored 0%)
adding: META-INF/MANIFEST.MF (in=29) (out=31) (deflated -6%)
adding: Test/HelloWorld2.class (in=594) (out=355) (deflated 40%)
Total:
------
(in = 623) (out = 738) (deflated -18%)
[oracle@sracanov-au2 ~]$ java -jar Test.jar
Hello World 2
[oracle@sracanov-au2 ~]$ loadjava -u scott/tiger Test.jar
[oracle@sracanov-au2 ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 8 10:02:16 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @java_objects
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ --------------- -------
Test/HelloWorld2 JAVA CLASS INVALID
META-INF/MANIFEST.MF JAVA RESOURCE VALID
SQL> CREATE OR REPLACE FUNCTION HW RETURN VARCHAR2 as LANGUAGE JAVA NAME 'Test/HelloWorld2.getHello() return String';
2 /
Function created.
SQL> select HW() from dual;
HW()
--------------------------------------------------------------------------------
Hello World 2 !!!
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@sracanov-au2 ~]$ dropjava -u scott/tiger Test.jar
[oracle@sracanov-au2 ~]$ loadjava -u scott/tiger -jarsasdbobjects -prependjarnames Test.jar
[oracle@sracanov-au2 ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 12 10:33:50 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @java_objects
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ --------------- -------
Test.jar///Test/HelloWorld2 JAVA CLASS INVALID
/233d8437_MANIFESTMF JAVA RESOURCE VALID
SQL> CREATE OR REPLACE FUNCTION HW RETURN VARCHAR2 as LANGUAGE JAVA NAME 'Test.jar///Test/HelloWorld2.getHello() return String';
2 /
Function created.
SQL> select HW() from dual;
HW()
--------------------------------------------------------------------------------
Hello World 2 !!!
SQL>
Note: I also used the parameters to load an individual class here
which is not necessary. So '-jarsasdbobjects -prependjarnames' or
'loadjava' does not do any parsing on the file type.i.e.
[oracle@sracanov-au2 ~]$ cat > somefile
blah blah
Quit
[oracle@sracanov-au2 ~]$ loadjava -u scott/tiger -jarsasdbobjects -prependjarnames somefile
[oracle@sracanov-au2 ~]$