File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Error trying to call a stored function. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Error trying to call a stored function." Watch "Error trying to call a stored function." New topic
Author

Error trying to call a stored function.

Tony Evans
Ranch Hand

Joined: Jun 29, 2002
Posts: 582
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
Jeremy Wilson
Ranch Hand

Joined: Feb 18, 2003
Posts: 166
I cannot locate the code I used before, but I believe you do not need the begin and end. I think you can use the sql "Call package.functionname(parameterlist)" as the statement.


Jeremy Wilson
Tony Evans
Ranch Hand

Joined: Jun 29, 2002
Posts: 582
Thanks Jeremy
I used the call statement and it worked, here is the code for anyone else who may have the same problem.
CallableStatement cs = null;


try
{
cs = conn.prepareCall("{? = call employeeInfo.GET_EMPLOYEES()}");
cs.registerOutParameter(1,OracleTypes.CURSOR);
cs.execute();
}
catch(SQLException e)
{
System.out.println("3 "+e);
}

ResultSet rs = null;
try
{
rs = (ResultSet)cs.getObject(1);
while(rs.next())
{
System.out.println(rs.getString(1));
}
}
catch(SQLException e)
{
System.out.println("5 "+e);
}
Thanks again Tony
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Error trying to call a stored function.