This week's book giveaway is in the Performance forum.
We're giving away four copies of The Java Performance Companion and have Charlie Hunt, Monica Beckwith, Poonam Parhar, & Bengt Rutisson on-line!
See this thread for details.
Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Error trying to call a stored function.

 
Tony Evans
Ranch Hand
Posts: 598
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 166
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Tony Evans
Ranch Hand
Posts: 598
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic