ben may

Greenhorn
+ Follow
since Jul 21, 2003
Merit badge: grant badges
For More
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by ben may

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!!
20 years ago