aspose file tools*
The moose likes JDBC and the fly likes Gettting multiple Rows from stored proc Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Gettting multiple Rows from stored proc" Watch "Gettting multiple Rows from stored proc" New topic
Author

Gettting multiple Rows from stored proc

Nimesh Gala
Greenhorn

Joined: Aug 10, 2001
Posts: 4
i am a total newbie to Oracle and JDBC combination.
i have written a stored procedure that is expected to return multiple rows.
but when i execute it i get only the first row of it.
my procedure is
CREATE OR REPLACE Procedure SPGETOFFERSINGROUP
( parentId IN VARCHAR2)
IS
OFFID tbl_Offer_Level_Master.offer_id%type;
DES tbl_Offer_Group_Details.description%type;
BEGIN
SELECT tbl_Offer_Level_Master.offer_id, tbl_Offer_Group_Details.description
INTO OFFID, DES
FROM tbl_Offer_Level_Master, tbl_Offer_Group_Details
WHERE tbl_Offer_Level_Master.parent_id = tbl_Offer_Group_Details.offer_group_id
AND tbl_Offer_Level_Master.parent_id=parentId;

END;
this returns only the first record matching the crieteria,
how will i get all the rows?, do i have to use cursors, if so how?
what code is expected to be written in java to get the multiple records.

[This message has been edited by Nimesh Gala (edited August 11, 2001).]
Gulab Singh
Greenhorn

Joined: Aug 09, 2001
Posts: 8
Originally posted by Nimesh Gala:
i am a total newbie to Oracle and JDBC combination.
i have written a stored procedure that is expected to return multiple rows.
but when i execute it i get only the first row of it.
my procedure is
CREATE OR REPLACE Procedure SPGETOFFERSINGROUP
( parentId IN VARCHAR2)
IS
OFFID tbl_Offer_Level_Master.offer_id%type;
DES tbl_Offer_Group_Details.description%type;
BEGIN
SELECT tbl_Offer_Level_Master.offer_id, tbl_Offer_Group_Details.description
INTO OFFID, DES
FROM tbl_Offer_Level_Master, tbl_Offer_Group_Details
WHERE tbl_Offer_Level_Master.parent_id = tbl_Offer_Group_Details.offer_group_id
AND tbl_Offer_Level_Master.parent_id=parentId;

END;
this returns only the first record matching the crieteria,
how will i get all the rows?, do i have to use cursors, if so how?
what code is expected to be written in java to get the multiple records.
Define a cursor to fetch the rows. See the syntax for declaring and opening of the cursor then fetch the rows ome by one from the cursor. See a standard text book on oracle for syntax of declaring, opening and fetching of the cusrsor.
Thanks and Regards,
Gulab Singh
[This message has been edited by Nimesh Gala (edited August 11, 2001).]

Nimesh Gala
Greenhorn

Joined: Aug 10, 2001
Posts: 4
Originally posted by Gulab Singh:

thank you sir for ur reply,
i have done as said by u, but now one the procedure is executed it will fetch the records and then close the cursur.
there is nothing coming in the recordset.
what i need to do is find some way to store the fetched records in a temporory table and return that table, so that i will get a result set on the another side.
can u help me in this.
Gulab Singh
Greenhorn

Joined: Aug 09, 2001
Posts: 8
well, if you want to retrieve the rows in java, then declare the cursor as type REFCURSOR and then return that cursor. This can then be taken in a resultset.
Nimesh Gala
Greenhorn

Joined: Aug 10, 2001
Posts: 4
OK,
i think that may do , but can u send me some example code for it, i searched the web, but i didnt get any sample code to help me out.
i am not at all familiar with referece cursors
Thomas Kyte
Greenhorn

Joined: Jul 15, 2001
Posts: 6
see
http://osi.oracle.com/~tkyte/ResultSets/index.html
there I have complete examples showing how to interact with ref cursors using jdbc, proc, oci, perl, vb, etc....


Thomas Kyte (tkyte@us.oracle.com) <A HREF="http://asktom.oracle.com/" TARGET=_blank rel="nofollow">http://asktom.oracle.com/</A> <BR>Expert one on one Oracle, programming techniques and solutions for Oracle.<BR>http://www.amazon.com/exec/obidos/ASIN/1861004826/ <BR>Opinions are mine and do not necessarily reflect those of Oracle Corp
 
wood burning stoves
 
subject: Gettting multiple Rows from stored proc