Using '-jarsasdbobjects -prependjarnames' parameters and calling the loaded class from a Java Stored Procedure

Posted by Steve Racanovic | Posted in | 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 ~]$