result set without using cursor in Oracle
843854Nov 12 2002 — edited Nov 12 2002Hi,
I am very much new to oracle. we have a JDBC code and SQL Server procedure and which works fine. Now that we want to implement the same in Oracle also.we deceided not to change the JDBC but to change Stored Procedure and schema.
Assume for Eg there is a stored procedure in SQL Server that return a simple query from table
say
creat procedure Test
as
begin
select * from Accounts
return
end
This procedure returns all the rows in the Accounts table and corresponding java code the fetch all the rows from ResultSet.
CallableStatement cStmt = con.prepareCall("{ call test ? }" );
cStmt.setInt( 1 , id_ );
ResultSet rs = cStmt.executeQuery();
while( rs.next() )
{
// get all the rows
}
will give all the rows from the table account.
Now that we want to implement the same in Oracle.
I just want to know, does Oracle provides any way to write a pl/sql or Procedure that returns resultset not as a cursor, but i should work with existing code.
I don't want to declare like the below code
CallableStatement cStmt = con.prepareCall("{? = call BrowseAccount.getRefCursor }");
cStmt.registerOutParameter(1, OracleTypes.CURSOR);
cStmt.execute();
ResultSet rs = null;
rs = (ResultSet)cStmt.getObject(1);
while (rs.next() )
{
// get the resultset
}
If anybody have any suggestion or sample code will be really appreciated
thanks
vijay