Calling an Oracle Stored Procedure in Java

Question

Here's how I'm able to execute my stored procedure through SqlDeveloper

var p refcursor;
exec DMG.Getstudentids(12342343,:p);
print p;

Output

P
-----------
STUDENT_ID
-----------
23432425
54353455

Now I'm trying execute the stored procedure the same way but in Java. Here's my code and I'm missing something about the input/output parameters or their datatypes.

Connection connection = DriverManager.getConnection(url, user, pass);
CallableStatement cs = connection.prepareCall("{call DMG.Getstudentids(?,?)}");
cs.setFloat(1, 12342343);
cs.registerOutParameter(2, Types.OTHER);
cs.execute();
List<Integer> result = (List<Integer>) cs.getArray(2);

I get the following error

java.sql.SQLException: Invalid column type: 1111

I think I'm missing something fundamental here. Anyone see where I'm failing? Thanks.

Answer 1

Try following:

Connection connection = DriverManager.getConnection(url, user, pass);
CallableStatement cs = connection.prepareCall("{call DMG.Getstudentids(?,?)}");
cs.setFloat(1, 12342343);
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.executeQuery();
ResultSet resultSet=cs.getObject(1);
List<Integer> result = new ArrayList<Integer>();
while(resultSet.next()){
 result.add(resultSet.getInt(STUDENT_ID));
}

Note : Since the procedure is returning refcursor, you need to register OracleTypes.CURSOR as output parameter.

Nother thing to note is you need to catch the whole dataset(refcursor) into Result Set, iterate it and put the extracted value into List.