possible to return modified ref cursor?

From: Bomb Diggy <techguy_chicago_at_yahoo.com>
Date: 18 Jun 2003 14:18:54 -0700
Message-ID: <94599bb3.0306181318.2d17e426@posting.google.com>

Hi,

I know how to do the standard Java-to-PL/SQL-REF CURSOR thing for returning ResultSets from stored procedures, but now I have a very complicated (impossible for me) SQL statement that I wanted to break up into several smaller statements. So, I want to essentially build a list of objects and return them to the caller. In PL/SQL, I would guess the nomenclature would be 'build a Collection of Types and return to caller via REF CURSOR'. (??)

The answer may have something to do with Collections or temporary tables. (??)

Here's what I have working:

---

PROCEDURE SP_GET_RESULT_SET( P_CURSOR_VAR OUT GenericCursorType ) BEGIN
  open P_CURSOR_VAR for select sysdate from dual; END Here's what I want to do:

---

PROCEDURE SP_GET_RESULT_SET( P_CURSOR_VAR OUT GenericCursorType )

• declare some master_record_type that will eventually be returned
• in P_CURSOR_VAR BEGIN
• // I've simplified -> the idea is data aggregation
• FOREACH row in parent table
• hold row in temp variable
• do a select on a child table; hold data in temp variable
• do a select on a child table; hold data in temp variable
• combine all data into one master_record and add it to return list --
• return list of master_records END

Thanks for any help!!! Received on Wed Jun 18 2003 - 16:18:54 CDT