Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Gettting multiple Rows from stored proc

 
Nimesh Gala
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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....
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic