Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

returning a cursor from a stored funct

 
ben may
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic