This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

returning a cursor from a stored funct

 
ben may
Greenhorn
Posts: 1
  • 0
  • 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!!
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic