Resultset with using Oracle Cursor

[vijay321]

Hi,
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

[TimHall]

Hi.

You'll need to use REF CURSORS and do something like this:

http://www.oracle-base.com/Articles/...Recordsets.asp

Remember, the conversion of a ref cursor to a ResultSet, or RecordSet, will only happen if you are using an Oracle driver.

Cheers

Tim...

[vijay321]

Thanks for you reply. if there is any way that you return result set WITH OUT using cursors
any help will be really appreciated

vijay

[chrisrlong]

Originally posted by vijay321
Thanks for you reply. if there is any way that you return result set WITH OUT using cursors

Yeah, you can use the old table-array methodology, which sucks. What is the problem with using the Ref Cursors?

- Chris

[vijay321]

I don't want to change the java code which now works with sybase.
here is the eg for Sybase/SQL Server coding

CallableStatement cStmt = con.prepareCall("{ call }");
cStmt.execute();
ResultSet rs = null;
rs = cStmt.getmoreResults();
while (rs.next() )
{
System.out.println(" No of Rows returned is ===> " + iC++);
}

the above code works fine for Sybase/sql server. if i want to use oracle i want to add
cStmt.registerOutParameter(1, OracleTypes.CURSOR);
for get the ref cursor, which will not work in SQL server/sybase.
i hope you understand the problem
any help in this regard will be really helpful.
thanks
vijay

[chrisrlong]

I must confess to not following that code at all .

However, I suspect that you are confused as well. How Oracle implements a resultset is of no concern to you. Mind you, it used to be, when those nasty table-arrays were used. Now, however, the Oracle driver interprets that Ref Cursor parameter for you and simply returns a resultset from the proc - you never see the parameter.

So, from your perspective, it simply appears as if you are calling a proc with no ref cursor parameter that simply returns a resultset.

Make sense?

- Chris

[vijay321]

thanks for your information.
can you please help find some links or sample program to implement it

vijay