Step-1:
Create Java Project in eclipse. I use jdk 1.5 for oracle 11g.
Step-2:
Form oracle we can call static method. for
the supported data type check this link .
This link will be helpful to define parameter and return type. For the User
Defined Data type we have implement
either the SQLData or CustomDatum interface.
package com.abb.functions;
import java.sql.Array;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Dictionary;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleDriver;
import com.abb.functions.out.model.BranchOpenDateOut;
public class BranchWrapper {
public synchronized static String isValidBranch(String branchCode) {
return "CBS";
}
public static Array parseToArray(String str, String delim) throws SQLException {
OracleDriver ora = new oracle.jdbc.OracleDriver();
Connection conn = ora.defaultConnection();
OracleConnection oraConn = (OracleConnection) conn;
Array arr = oraConn.createARRAY("STRINGARRAY", str.split(delim));
return arr;
}
public synchronized static BranchOpenDateOut isBranchOpenDate(String branchCode) throws SQLException {
BranchOpenDateOut e = new BranchOpenDateOut("BRANCHOPENDATEOUT", "KAMAL HOSSAIN");
return e;
}
}
import java.sql.Array;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Dictionary;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleDriver;
import com.abb.functions.out.model.BranchOpenDateOut;
public class BranchWrapper {
public synchronized static String isValidBranch(String branchCode) {
return "CBS";
}
public static Array parseToArray(String str, String delim) throws SQLException {
OracleDriver ora = new oracle.jdbc.OracleDriver();
Connection conn = ora.defaultConnection();
OracleConnection oraConn = (OracleConnection) conn;
Array arr = oraConn.createARRAY("STRINGARRAY", str.split(delim));
return arr;
}
public synchronized static BranchOpenDateOut isBranchOpenDate(String branchCode) throws SQLException {
BranchOpenDateOut e = new BranchOpenDateOut("BRANCHOPENDATEOUT", "KAMAL HOSSAIN");
return e;
}
}
import java.math.BigDecimal;
import java.sql.Date;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleConnection;
import oracle.jpub.runtime.MutableStruct;
import oracle.sql.CustomDatum;
import oracle.sql.CustomDatumFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
public BranchOpenDateOut implements CustomDatum, CustomDatumFactory {import java.sql.Date;
import java.sql.SQLException;
import oracle.jdbc.driver.OracleConnection;
import oracle.jpub.runtime.MutableStruct;
import oracle.sql.CustomDatum;
import oracle.sql.CustomDatumFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
public static final String _SQL_NAME = "BranchOpenDateOut";
public static final int _SQL_TYPECODE = oracle.jdbc.OracleTypes.STRUCT;
MutableStruct _struct;
static int[] _sqlType = {java.sql.Types.VARCHAR };
static CustomDatumFactory[] _factory = new CustomDatumFactory[_sqlType.length];
static final TRANSACTIONOUT _BranchOpenDateOutFactory = new TRANSACTIONOUT();
public static CustomDatumFactory getFactory() {
return _BranchOpenDateOutFactory ;
}
/* constructor */
public TRANSACTIONOUT() {
_struct = new MutableStruct(new Object[_sqlType.length], _sqlType,
_factory);
}
/* CustomDatum interface */
public Datum toDatum(OracleConnection c) throws SQLException {
return _struct.toDatum(c, _SQL_NAME);
}
/* CustomDatumFactory interface */
public CustomDatum create(Datum d, int sqlType) throws SQLException {
if (d == null) {
return null;
}
TRANSACTIONOUT o = new TRANSACTIONOUT();
o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
return o;
}
/* accessor methods */
public String getmResBrDt() throws SQLException {
return (String) _struct.getAttribute(0);
}
public void setmResBrDt(String mResBrDt) throws SQLException {
_struct.setAttribute(0, mResBrDt);
}
}
Step-3: after compiling this project export it as jar.
Step-4 use following loadjava command
Loadjava –user schema xyz jar-location
Step-5: in oracle create a Wrapper based on the static method want
to call from oracle.
CREATE or REPLACE
FUNCTION ISVALIDBRANCH(brCode VARCHAR2) RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'com.abb.functions.BranchWrapper.isValidBranch(java.lang.String) return java.lang.String';
FUNCTION ISVALIDBRANCH(brCode VARCHAR2) RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'com.abb.functions.BranchWrapper.isValidBranch(java.lang.String) return java.lang.String';
CREATE or REPLACE
FUNCTION PARSETOARRAY (str IN VARCHAR2, delim IN VARCHAR2)
RETURN STRINGARRAY AS
LANGUAGE JAVA NAME 'com.abb.functions.BranchWrapper.parseToArray (java.lang.String, java.lang.String) return java.sql.Array';
RETURN STRINGARRAY AS
LANGUAGE JAVA NAME 'com.abb.functions.BranchWrapper.parseToArray (java.lang.String, java.lang.String) return java.sql.Array';
CREATE or REPLACE
FUNCTION ISBRANCHOPENDATE(brCode VARCHAR2) RETURN BranchOpenDateOut AS
LANGUAGE JAVA NAME 'com.abb.functions.BranchWrapper.isBranchOpenDate2(java.lang.String) return com.abb.functions.out.model.BranchOpenDateOut';
LANGUAGE JAVA NAME 'com.abb.functions.BranchWrapper.isBranchOpenDate2(java.lang.String) return com.abb.functions.out.model.BranchOpenDateOut';
We have to map BranchOpenDateOutin and STRINGARRAY oracle as Object
CREATE or REPLACE TYPE BranchOpenDateOut AS OBJECT (
mResBrDt VARCHAR2 (100)
)
CREATE or REPLACE type STRINGARRAY is table of varchar2(4000);
That it we have done now we are going to call JAVA METHODS from oracle.
DECLARE
STR VARCHAR2(200);
DELIM VARCHAR2(200);
v_Return STRINGARRAY;
STR VARCHAR2(200);
DELIM VARCHAR2(200);
v_Return STRINGARRAY;
v_Return2 ABBTEST.BRANCHOPENDATEOUT;
v_Return3 VARCHAR2(200);
BEGIN
-- call to parseToArray method
BEGIN
-- call to parseToArray method
STR := 'A,B';
DELIM := ',';
DELIM := ',';
v_Return := PARSETOARRAY(STR => STR, DELIM => DELIM );
FOR i IN v_Return.first .. v_Return.last LOOP
DBMS_OUTPUT.PUT_LINE(v_Return(i));
END LOOP;
-- call to isBranchOpenDate method
v_Return2 := ISBRANCHOPENDATE(BRCODE => BRCODE);
DBMS_OUTPUT.PUT_LINE(v_Return2.mResBrDt);
DBMS_OUTPUT.PUT_LINE(v_Return2.mResBrDt);
-- call to isValidBranch method
v_Return3 := ISVALIDBRANCH(BRCODE => BRCODE); DBMS_OUTPUT.PUT_LINE(v_Return3);
END;