I have a stored Function
CREATE OR REPLACE PACKAGE BODY employeeInfo IS FUNCTION GET_EMPLOYEES return ref_cursor IS user_cursor ref_cursor;
BEGIN
OPEN user_cursor
FOR select emp.name, wsk.skill, wsk.ability, wsk.rate,lc.city
from EMPLOYEE emp, WORKERSKILL wsk, LOCATION lc
where wsk.workerskillID = emp.workerskillID and
emp.locationID = lc.locationID
order by lc.city;
RETURN user_cursor;
END;
END;
/
Which I can run using the Oracle SQL*plus editor.
The problem is when I try to run it from a
java application I get the following error: ORA-00900: invalid SQL statement
My java code is as follows:
String query = "begin ? := employeeInfo.GET_EMPLOYEES(); end;";
//String query = "begin ? := GET_EMPLOYEES(); end;";
CallableStatement cs = conn.prepareCall("query");
cs.registerOutParameter(1,OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet)cs.getObject(1);
while(rs.next())
{
System.out.println(rs.getString(1));
}
Not to sure what to do next.
Thanks for any help Tony