Fetch pl/sql array return values in java
Solution 1
I didn't do that with ARRAY
but it should works. First you must register out
parameter of your function. So it can be like this.
private final String PRODECURE_NAME = "{? = call <ProcedureName>(?,?,?)}";
Connection con = null;
CallableStatement cs = null;
try {
con = DAOFactory.getDatabaseConnection();
cs = con.prepareCall(PRODECURE_NAME);
cs.registerOutParameter(1, java.sql.Types.ARRAY);
cs.setYourType(2, <yourData>);
cs.setYourType(3, <yourData>);
cs.setYourType(4, <yourData>);
cs.execute();
Array arr = cs.getArray(1);
if (arr != null) {
String[] data = (String[]) arr.getArray();
}
}
catch (SQLException ex) {
Logger.getLogger(OracleLiekDAO.class.getName()).log(Level.SEVERE, null, ex);
try {
con.rollback();
}
}
finally {
if (con != null) {
try {
con.close();
}
catch (SQLException ex) {
Logger.getLogger(OracleLiekDAO.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
Try this man and give me then asnwer wether it do or not.
EDIT:
These char ? represents one parameter that you will set(it's named as parametrized). So this:
cs.setYourType(2, <yourData>);
cs.setYourType(3, <yourData>);
cs.setYourType(4, <yourData>);
means, that you set your three parameters (?), first parameter of method is column index and second are you data of your specific type.
EDIT 2:
So sorry i wrote bad solution, already updated so check code now and try it.
Solution 2
myArray returned from PL/SQL is of type java.sql.Array. You can invoke getArray() on it and type cast it to get java array.
String[] array = (String[]) myArray.getArray();
Solution 3
I believe you can use the following method present in the SerialArray class which is a direct descendant of java.sql.Array:
Object getArray(Map<String, Class<?>> map)
Here the Map is a holder of an object and its property which is to be mapped to the fetched resultset in the returned array.
For more details see http://docs.oracle.com/javase/1.5.0/docs/api/javax/sql/rowset/serial/SerialArray.html javadoc
Solution 4
Yes, you need to registerOutParameter
of type Types.ARRAY
see example here
Solution 5
The JDBC Specification contains a whole section (16.5) dedicated to dealing with arrays. You might want to give it a look.
Comments
-
Jacob almost 2 years
In Java how to get values from a
pl/sql
function which returns an array.if my pl/sql function returns array called
myArray
, in java is it possible to fetch values frommyArray
into java objects usingcallablestatement
?Thanks
Update 1
My Java code where I am calling function, but I am getting exception.
PLS-00306: wrong number or types of arguments in call to 'myfunc' connection = con.getConnection(); callablestatement = connection.prepareCall("{call myfunc(?,?,?}"); callablestatement.setInt(1, param1); callablestatement.setInt(2, param2); callablestatement.setString(3, param3); callablestatement.registerOutParameter(4, Types.ARRAY); callablestatement.execute(); resultSet = callablestatement.getArray(4).getResultSet();
Update 2
private final String PRODECURE_NAME = "{? = call myfunc(?,?,?)}";
and
connection = con.getConnection(); callablestatement = connection.prepareCall(PRODECURE_NAME); callablestatement.registerOutParameter(1, Types.ARRAY); callablestatement.setInt(2, param1); callablestatement.setInt(3, param2); callablestatement.setString(4, param3); callablestatement.execute(); create or replace type dates is varray(100) of varchar2(32);
function
CREATE OR REPLACE function myfunc ( p_id IN number, p_c_id IN number, p_co_no IN number ) RETURN dates AS myarray contract_dates; par1 VARCHAR2 (32); par2 VARCHAR2 (32);
Fixed Update 3
connection = con.getConnection(); callablestatement = connection.prepareCall("begin ? :=myfunc(?,?,?); end;"); callablestatement.registerOutParameter(1, OracleTypes.ARRAY, "DATES"); callablestatement.setInt(2, param1); callablestatement.setInt(3, param2); callablestatement.setString(4, param3); callablestatement.execute();