File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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
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!!
 
jQuery in Action, 2nd edition
 
subject: returning a cursor from a stored funct
 
Similar Threads
sqlException : "Invalid scale size. Cannot be less than zero." when executing script with UNION
sql syntax for system date
Calling stored procedures
JPA Native Query
SQL Show Previous Month Date Question