Step 1: DEVELOP JAVA CLASS, WITH MEMBER METHOD(S)
public class Hello
{
public static String world ()
{
return "Hello world";
}
}
Then compile the source using:
javac Hello.java
NOTE:
1. It is not necessary to compile the java source file in this step since the utility loadjava (refer STEP 2) does this for us.
2. Methods with VOID return type get mapped to Procedures, where as methods with NON – VOID return type get mapped to functions when these are published using the call specifications (STEP 3)
Step 2: LOAD THE JAVA SOURCE/CLASS INTO THE ORACLE DATABASE
loadjava –u user_name/password@oracleSID -r –v hello_word.java
C:\oracle9i\bin> loadjava -u scott/tiger@dbsmgi Hello.java
Step 3: WRITE THE CALL SPECS TO PUBLISH THE JAVA CLASSES
SQL> CREATE OR REPLACE FUNCTION proc_get_msg RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'Hello.world() return java.lang.String';
/
Function created.
Step 4: CALLING THE JAVA STORED PROCEDURES
SQL> set serveroutput on;
SQL> call dbms_java.set_output(50);
Define the variable to get the message returned by function
SQL> VARIABLE var_MSG VARCHAR2(20);
Call the Function (get the return value into the variable)
SQL> CALL proc_get_msg() INTO :var_MSG;
Print the result
SQL> print var_MSG;
VAR_MSG
---------------------
Hello world
Where are the class files stored ?
SQL> desc all_objects;
Name Type Nullable Default Comments
-------------- ------------ -------- ------- ----------------------------------------------------------------------------
OWNER VARCHAR2(30) Username of the owner of the object
OBJECT_NAME VARCHAR2(30) Name of the object
SUBOBJECT_NAME VARCHAR2(30) Y Name of the sub-object (for example, partition
OBJECT_ID NUMBER Object number of the object
DATA_OBJECT_ID NUMBER Y Object number of the segment which contains the object
OBJECT_TYPE VARCHAR2(19) Y Type of the object
CREATED DATE Timestamp for the creation of the object
LAST_DDL_TIME DATE Timestamp for the last DDL change (including GRANT and
REVOKE) to the object
TIMESTAMP VARCHAR2(19) Y Timestamp for the specification of the object
STATUS VARCHAR2(7) Y Status of the object
TEMPORARY VARCHAR2(1) Y Can the current session only see data that it placed in this
object itself?
GENERATED VARCHAR2(1) Y Was the name of this object system generated?
SECONDARY VARCHAR2(1) Y Is this a secondary object created as part of icreate for domain
indexes?
SQL>select * from all_objects where object_name like 'Hello%';
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY
------------------------------ ------------------------------ ------------------------------ --------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------
APPS Hello 736206 JAVA CLASS 6/6/2007 5: 6/14/2007 12: 2007-06-06:05:40:45 VALID N N N
SQL> desc user_java_classes
Name Type Nullable Default Comments
------------- -------------- -------- ------- --------------------------------------------------------------------------
NAME VARCHAR2(4000) Y name of the java class
MAJOR NUMBER Y the major version number of the java class as defined in JVM specification
MINOR NUMBER Y the minor version number of the java class as defined in JVM specification
KIND VARCHAR2(9) Y is the stored object a java class or java interface?
ACCESSIBILITY VARCHAR2(6) Y the accessiblity of the java class
IS_INNER VARCHAR2(3) Y is this java class an inner class
IS_ABSTRACT VARCHAR2(3) Y is this an abstract class?
IS_FINAL VARCHAR2(3) Y is this an final class?
IS_DEBUG VARCHAR2(3) Y does this class contain debug information?
SOURCE VARCHAR2(4000) Y source designation of the java class
SUPER VARCHAR2(4000) Y super class of this java class
OUTER VARCHAR2(4000) Y outer class of this java class if this java class is an inner class
SQL> Select * From user_java_classes where NAME like 'Hello%';
Hello 45 3 INTERFACE PUBLIC NO NO NO NO Hello.java java/lang/Object
-
1 row selected.
Using the main Method
The main method can take a variable number of arguments which are typically passed on the command line to the Java VM.
Example:
public class DemoMain {
public static void main(String args[]) {
for(int i=0; i< args.length; i++)
System.out.println("args[" + i + "]:" + args[i]);
}
}
Now we have to load it in the database using :
loadjava -user scott/tiger@dbsmgi -verbose -resolve DemoMain.java
Create a wrapper for the method. Assming that it takes either one or two arguments
CREATE OR REPLACE PACKAGE DemoMain AS
Procedure Main(p1 IN VARCHAR2) IS Language Java
Name 'DemoMain.main(java.lang.String[])';
Procedure Main(p1 IN VARCHAR2, p2 IN VARCHAR2) IS Language Java
Name 'DemoMain.main(java.lang.String[])';
END DemoMain;
SQL> CALL DemoMain.Main(‘One’, ‘Two’);
Using the IN OUT or OUT parameters
If a parameter is IN OUT or OUT, then it needs to be passed by reference rather than by value. To achieve this, the Java parameter should be passed as an array.
Example:
import oracle.sql.*;
import java.sql.*;
class JavaModes
{
public static void numP(int p1, int[] p2, int[] p3) {
p3[0] = p2[0] * 2; -- Assign p3= p2 * 2
p2[0] = p2[0] + p1; -- Assign p2=p2 + p1
}
public static void charP(String p1, String[] p2, String[] p3) {
p2[0] += p1; -- Concatenate p1 on end of p2
p3[0] = "Hello, I am a Java String";
}
public static void dateP(DATE p1, DATE[] p2, DATE[] p3) throws SQLException{
p2[0] = p2[0].addMonths(6); -- Add 6 months to p2
p3[0] = p1.addMonths(-6); -- Set p3 to p1 -6 months
}
}
The corresponding PL/SQL wrapper is
CREATE OR REPLACE Package JavaModes AS
Procedure NumP(p1 IN Number, p2 IN OUT Number, p3 OUT Number) IS
Language Java
Name 'JavaModes.numP(int, int[], int[])';
Procedure CharP(p1 IN VARCHAR2, p2 IN OUT VARCHAR2, p3 OUT VARCHAR2) IS
Language Java
Name 'JavaModes.charP(java.lang.String, java.lang.String[], java.lang.String[])';
Procedure DateP(p1 IN DATE, p2 IN OUT DATE, p3 OUT DATE) IS
Language Java
Name 'JavaModes.dateP(oracle.sql.DATE, oracle.sql.DATE[], oracle.sql.DATE[])';
END JavaModes;
