permaculture playing cards*
The moose likes Oracle/OAS and the fly likes returning a cursor from a stored funct Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "returning a cursor from a stored funct" Watch "returning a cursor from a stored funct" New topic
Author

returning a cursor from a stored funct

ben may
Greenhorn

Joined: Jul 21, 2003
Posts: 1
Hey all! I am VERY new to (read: first time user of) oracle stored procedures/functions. Been using oracle generally for a while now, tho so I am not a total newbie...
Anyways, I am trying to write a stored function which returns a set of rows. As far as I can tell the only way to do this is by returning a cursor. I ultimately plan to retrieve these rows in a java application, so obviously I need to do this in a way whereby the resulting set of rows can be parsed and read in java. Anyways, here is what I have so far:
Original SQL:
SELECT
'MAIN' AS TYPE, FEAT.SALE_ID, FEAT.CYCLE_ID, FEAT.SKU_ID, HIST.MAILED_DATE, HIST.FEATSEL_RPLY_BY_DT
FROM
BSPAN.BSPAN_FEATSELS FEAT,
BSPAN.BSPAN_USR_FEATSEL_HISTORY HIST
WHERE
HIST.SALE_CYCLE = FEAT.CYCLE_ID AND
HIST.FEAT_REFERENCE = FEAT.SALE_ID AND
HIST.ID = '211892721' and
FEAT.CLUB_CODE = '102' AND
HIST.NEG_ORD_RSLT_CODE = 0 AND
HIST.FEATSEL_RPLY_BY_DT > TRUNC(SYSDATE) +5
AND
(NOT EXISTS
( SELECT RESP.ID FROM
BSPAN.BSPAN_USR_FEATSEL_RESPONSE RESP WHERE RESP.CYCLE = FEAT.CYCLE_ID AND
RESP.sale_id = FEAT.SALE_ID AND RESP.ID = HIST.ID
)
)
UNION
SELECT
'ALT' AS TYPE,ALT.SALE_ID, ALT.CYCLE_ID, ALT.SKU_ID, HIST.MAILED_DATE, HIST.FEATSEL_RPLY_BY_DT
FROM
BSPAN.BSPAN_ALTSELS ALT,
BSPAN.BSPAN_USR_FEATSEL_HISTORY HIST
WHERE
HIST.SALE_CYCLE = ALT.CYCLE_ID AND
HIST.FEAT_REFERENCE = ALT.SALE_ID AND
ALT.CLUB_CODE = '102' AND
HIST.ID = '211892721' and
HIST.NEG_ORD_RSLT_CODE = 0 AND
HIST.FEATSEL_RPLY_BY_DT > TRUNC(SYSDATE) +5

Here is the PL/SQL I have so far for the stored func:
CREATE OR REPLACE function GetBOMCOpenCycles
(P_CLUB_CODE BSPAN_FEATSELS.CLUB_CODE%TYPE,P_PROFILE_ID BSPAN_USR_FEATSEL_RESPONSE.ID%TYPE)
RETURN REF feat_sels
IS
BEGIN
OPEN feat_sels for
SELECT
'MAIN', FEAT.SALE_ID, FEAT.CYCLE_ID, FEAT.SKU_ID, HIST.MAILED_DATE, HIST.FEATSEL_RPLY_BY_DT
FROM
BSPAN.BSPAN_FEATSELS FEAT,
BSPAN.BSPAN_USR_FEATSEL_HISTORY HIST
WHERE
HIST.SALE_CYCLE = FEAT.CYCLE_ID AND
HIST.FEAT_REFERENCE = FEAT.SALE_ID AND
HIST.ID = P_PROFILE_ID and
FEAT.CLUB_CODE = P_CLUB_CODE AND
HIST.NEG_ORD_RSLT_CODE = 0 AND
HIST.FEATSEL_RPLY_BY_DT > TRUNC(SYSDATE) +5
AND
(NOT EXISTS
( SELECT RESP.ID FROM
BSPAN.BSPAN_USR_FEATSEL_RESPONSE RESP WHERE RESP.CYCLE = FEAT.CYCLE_ID AND
RESP.sale_id = FEAT.SALE_ID AND RESP.ID = HIST.ID
)
)
UNION
SELECT
'ALT',ALT.SALE_ID, ALT.CYCLE_ID, ALT.SKU_ID, HIST.MAILED_DATE, HIST.FEATSEL_RPLY_BY_DT
FROM
BSPAN.BSPAN_ALTSELS ALT,
BSPAN.BSPAN_USR_FEATSEL_HISTORY HIST
WHERE
HIST.SALE_CYCLE = ALT.CYCLE_ID AND
HIST.FEAT_REFERENCE = ALT.SALE_ID AND
ALT.CLUB_CODE = P_CLUB_CODE AND
HIST.ID = P_PROFILE_ID and
HIST.NEG_ORD_RSLT_CODE = 0 AND
HIST.FEATSEL_RPLY_BY_DT > TRUNC(SYSDATE) +5;
return feat_sels;
End;
/
It seems that constantly the issue is the damn return type. It just will not accept any variation I've tried yet. I'm using oracle 8.1.7... Any help appreciated!!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: returning a cursor from a stored funct