Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Regarding reultset in stored procedures

 
Gaurav Chikara
Ranch Hand
Posts: 412
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is it possible for stored provedure to return a resultset
and that too if it do not have any out parameters
if we use callable statment then also we cannot get resultset
and if we use preparedStatement then
prepared statement is not used for stored procedures
I was asked this question in an interview but cud not give a
satisfactory reply
another question which wasd asked from me was
that is it true that it is difficult to debug a stored procedure
pls reply if anyone knows it
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Gaurav Chikara:
Is it possible for stored provedure to return a resultset
and that too if it do not have any out parameters
if we use callable statment then also we cannot get resultset

it is possible to return a resultset from a callable statement in 2 ways:
1. as a return value from the callable statement

2. as an output parameter


another question which wasd asked from me was that is it true that it is difficult to debug a stored procedure

This depends. I have training as a Database Administrator as well as a programmer, so debugging stored procedures is as easy as debugging a java application. If you don't know how to read the stored procedure and you just call it, hoping for the best, it may be harder to debug.
 
Gaurav Chikara
Ranch Hand
Posts: 412
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jamie Robertson:

2. as an output parameter



This depends. I have training as a Database Administrator as well as a programmer, so debugging stored procedures is as easy as debugging a java application. If you don't know how to read the stored procedure and you just call it, hoping for the best, it may be harder to debug.

Hi Jamie Robertson
since we know that execute method donot return anything
then how is it possbile that it will return a resultset as u mentioned
can u or anyone clrify it?
 
Gaurav Chikara
Ranch Hand
Posts: 412
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jamie Robertson:

2. as an output parameter



This depends. I have training as a Database Administrator as well as a programmer, so debugging stored procedures is as easy as debugging a java application. If you don't know how to read the stored procedure and you just call it, hoping for the best, it may be harder to debug.

Hi Jamie Robertson
since we know that execute method donot return anything
then how is it possbile that it will return a resultset as u mentioned
can u or anyone clarify it?
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, this needs to be modified a little(was in a hurry Friday!):
If there are no input values for the StoredProcedure, only a return value of cursor/resultset then there is an example in the sun tutorial
here.
I believe if you use this code (not tested) it should work to:


Jamie
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic