This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
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: 573
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: 573
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.
 
Similar Threads
can´t read the Oracle´s XMLTYPE generated in a query in my JSP page
Urgent need help with PL/SQL function
getString() does not retrieve String bigger than 255 characters
Need Expert Advice
mysql cursor in stored procedure