Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    1

    Unanswered: returning cursors from stored functions

    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!!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: returning cursors from stored functions

    You need to define a datatype of REF CURSOR. In 9i there is a system defined type SYS_REFCURSOR, but for 8i you need to define it yourself in a package:

    create or replace package rc is
    type ref_cursor is ref cursor;
    end;

    (Could be part of an existing package rather than have its own package).

    Then in the function:

    CREATE OR REPLACE function GetBOMCOpenCycles
    (P_CLUB_CODE BSPAN_FEATSELS.CLUB_CODE%TYPE,P_PROFILE_ID BSPAN_USR_FEATSEL_RESPONSE.ID%TYPE)
    RETURN rc.ref_cursor
    ...

    BTW, you will need to name the first column of your select ('MAIN') so that the calling program can refer to it.

  3. #3
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44

    Re: returning cursors from stored functions

    Try this:

    CREATE OR REPLACE PACKAGE my_types AS
    TYPE cursor_type IS REF CURSOR;
    END my_types;
    /

    CREATE OR REPLACE FUNCTION GetBOMCOpenCycles
    (P_CLUB_CODE BSPAN_FEATSELS.CLUB_CODE%TYPE,P_PROFILE_ID BSPAN_USR_FEATSEL_RESPONSE.ID%TYPE)
    RETURN my_types.cursor_type
    IS
    my_cursor my_types.cursor_type;
    BEGIN
    OPEN my_cursor FOR
    .
    .
    .
    .
    RETURN my_cursor;
    END GetBOMCOpenCycles;
    /


    Cheers,

    Jacek

  4. #4
    Join Date
    Dec 2003
    Location
    McLean, VA
    Posts
    9

    Re: returning cursors from stored functions

    Take a look at the SQL in my post about calling Oracle stored functions dated 01/08/2004 (today). It's a good example and compiles. Of course, I'm having trouble calling it, but I think it's not because it's bad syntax. It compiles. It returns TYPE REF CURSOR that the previous reply suggested but is a bit more complete example. I hope it helps.

  5. #5
    Join Date
    Dec 2003
    Location
    McLean, VA
    Posts
    9

    Re: returning cursors from stored functions

    That PL/SQL code is very hard to read. You might try indenting the code, like:
    CREATE OR REPLACE PACKAGE pkg_PUBLIC_SPs
    IS
    TYPE typ_ref_cursor IS REF CURSOR;

    Function sp_Get_All_Projects
    RETURN typ_ref_cursor;

    END pkg_PUBLIC_SPs;
    /

    CREATE OR REPLACE PACKAGE BODY pkg_PUBLIC_SPs
    IS

    FUNCTION sp_Get_All_Projects
    RETURN typ_ref_cursor
    IS
    p_refcursor typ_ref_cursor;

    BEGIN
    OPEN p_refcursor FOR
    SELECT DISTINCT
    p.ProjectID ProjectID,
    NVL(p.ProjectName, '') ProjectName,
    NVL(p.ProjectDescription, '') ProjectDescription
    FROM Project p,
    Basis b,
    Task t
    WHERE b.ProjectID = p.ProjectID
    AND b.BasisBaseline = 1
    AND t.ProjectID = p.ProjectID
    AND t.TaskIsControlAccount = 1
    ORDER BY ProjectName ASC;
    RETURN p_refcursor;
    END sp_Get_All_Projects;

    END pkg_PUBLIC_SPs;
    /

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •