This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Calling Oracle Function that returns %ROWTYPE Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Calling Oracle Function that returns %ROWTYPE" Watch "Calling Oracle Function that returns %ROWTYPE" New topic
Author

Calling Oracle Function that returns %ROWTYPE

chris snyder
Greenhorn

Joined: Apr 24, 2001
Posts: 7
We are trying to call the following Oracle Function (8.1.6)
/*The package declaration*/
PACKAGE ACCOUNTSPKG IS
FUNCTION FINDRECORD
(P_ID IN accounts.id%TYPE
)
RETURN accounts%ROWTYPE;
END ACCOUNTSPKG;
Notice that the return is NOT a REF CURSOR. I have not been able to get this to work and haven't seen any examples that only return %ROWTYPE. How do you call (prepareCall("xxx")) this Function, and how do you get the resultset? [See code below]
// Begin Code
try
{
conn = DBUtil.getConnection(Constants.IPRO_DS);
// .prepareCall line throws SQL Exception:
// java.sql.SQLException: Invalid column
// type Account Info
call = conn.prepareCall (
"{ ? = call AccountsPkg.FindRecord (?)}");
call.registerOutParameter(1, OracleTypes.CURSOR);
call.setInt(2, 10);
call.execute();
ResultSet rs = (ResultSet) call.getObject(1);

while(rs != null && rs.next())
{
result += "\n AccountID: " + rs.getInt("ID")
+ "Account Type: " + rs.getString("ACCOUNT_TYPE");
}
call.close();
conn.close();

}
catch(Exception e)
{
System.out.println("AccountDAO.getAccountRS() exception: " + e);
}
 
Don't get me started about those stupid light bulbs.
 
subject: Calling Oracle Function that returns %ROWTYPE
 
Similar Threads
How to call function from hibernate??
what's wrong with this simple function ?
getting ResultSet from Java Stored Procedure
retrieve rows
Oracle CallableStatement