How resultset is returned by stored procedure?

[Hemant Agarwal]

How resultset is returned by stored procedure?
I know that by using Callable Statement we can call stored procedures. But if that stored procedure is returning a resultSet, then how will you handle that resultset?

[Harshil Mehta]

Hi Hemant,

Say you stored procedure is sp_emp for famous Oracle emp table:

 create procedure sp_emp as select * from emp 

Now when you run the procedure from SQL prompt, it will return records for all the employees which is essentially a resultset. That's how the stored procedure can return resultset.

Now in yor java code:

String procCall = "{call sp_emp}";
CallableStatement cstmt =  connection.prepareCall(procCall);
ResultSet rst = cstmt.executeQuery();

The above code will give you all the employee records in resultset rst. So you can not use the resultset same way as you use for adhoc queries.

hth,
Harshil

[Hemant Agarwal]

So you can not use the resultset same way as you use for adhoc queries.

What do you mean by that?

[Harshil Mehta]

Sorry Hemant,

It was a typo , i meant to say "Now you can use". not "Now you can not use".

--Harshil