I have been given PL SQL stored Function with the following signature and body description.
Create or Replace function Get_Employee(user_id in Number, res_out out sys_refcursor)
return sys_refcursor is
v_result sys_refcursor;
begin
//here goes function body
return v_result
end;
Basically, I need to call this function from Java. I have tried all possible ways to register out parameter as well as return value which both are refcursor type. Here is the way which I believe is the most correct way.
String employeeSQL = "{ ? = call Get_Employee(?,?)}";
CallableStatement statement = null;
Employee employee = null;
ResultSet result = null;
try{
statement = conn.prepareCall(employeeSQL);
statement.registerOutParameter(1, OracleTypes.Cursor); //registering return value
statement.registerOutParamter(2, OracleTypes.Cursor); // registering out parameter
statement .setLong(3, userId); // setting input parameter
statement.execute();
resultSet = (ResultSet) statement.getObject(1); // getting return value
}finally{
statement.close();
trsultSet.close();
}
The problem is whenever the statement is executed, it throws NullPointer Exception. So I suspect I do not register parameters in a correct way. Any help appreciated!
