Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2007
    Posts
    2

    Question Unanswered: Oracle newbie seeks help returning resultset to crystal reports

    Hi,

    I'm having some trouble getting a result set back to a crystal reports enterprise 10 datafoundation from a package/stored procedure on an oracle 9.2 database.

    I'm getting an error on compiling the package body
    subprogram or cursor 'GETOVERSTOCK' is declared in a package specification and must be defined in the package body

    From what I've read it is because the procedure definition in the package header does not match the one in the body, but to an oracle newbie like me they look the same. I've tried to follow the examples of creating cursors in various docs but they usually just create a %rowtype of an existing table but thats no use to me as the structure doesn't exist except in a query.

    I know that I might be mental trying to create a record type to return from my cursor but I'm getting a little lost here and I can't see the forrest for all the trees anymore.

    I need some advice I think....

    here's the package

    create or replace
    PACKAGE OVERSTOCK AS

    TYPE R_overstock IS RECORD (
    "Branch" STOCK.STK_LOC%TYPE,
    "Group" CMGROUP.CMGRP_GROUP%TYPE,
    "Product" CMPROD.CMP_PRODUCT%TYPE,
    "Description" CMPROD.CMP_DESC%TYPE,
    "Works" number,
    "Sold" number,
    "Transfer" number,
    "Physical" number,
    "Allocated" number ,
    "Free" number,
    "Overstock" number,
    "Value" number,
    "MaxTransactionDate" date
    ) ;

    TYPE results_cursor IS REF CURSOR RETURN R_overstock;

    procedure GetOverStock (
    branch in stock.stk_loc%TYPE,
    frommonth in stmove.STMOV_PERIOD%TYPE,
    fromyear in stmove.STMOV_YEAR%TYPE,
    groupstart in cmgroup.CMGRP_GROUP%TYPE,
    groupend in cmgroup.CMGRP_GROUP%TYPE,
    tomonth in stmove.STMOV_PERIOD%TYPE,
    toyear in stmove.STMOV_YEAR%TYPE,
    cur_Results IN OUT results_cursor
    );
    END OVERSTOCK;



    create or replace
    PACKAGE BODY OVERSTOCK
    AS

    procedure GETOVERSTOCK
    ( branch in varchar2
    , frommonth in number
    , fromyear in number
    , groupstart in varchar2
    , groupend in varchar2
    , tomonth in number
    , toyear in number
    , cur_results IN OUT results_cursor
    ) is
    begin

    DECLARE

    fromFinancialMonth NUMBER;
    fromFinancialYear NUMBER;
    v_Return NUMBER;
    toFinancialMonth NUMBER;
    toFinancialYear NUMBER;

    BEGIN

    OPEN cur_results for
    SELECT
    cmgrp_group as "Group",
    stk_product as "Product",
    cmp_desc as "Description",
    -(
    SELECT
    COALESCE(sum(stmov_qty),0)
    FROM stmove
    WHERE stmov_product = stk_product
    AND stmov_loc = stk_loc
    AND stmov_source = 'W'
    AND stmov_trantype = 'F'
    AND ((stmov_year = fromYear and stmov_period >= fromMonth) OR (stmov_year = toYear and stmov_period <= toMonth) OR (stmov_year > fromYear AND stmov_year < toYear))
    )
    as "Works",
    (
    select
    COALESCE(sum(stuse_salqty),0)
    from stusage
    where stuse_loc = stk_loc
    and stuse_product = stk_product
    AND ((stuse_year = fromYear and stuse_period >= fromMonth) OR (stuse_year = toYear and stuse_period <= toMonth) OR (stuse_year > fromYear AND stuse_year < toYear))
    )
    as "Sold",
    (
    select
    COALESCE(sum(stuse_tranoqty),0)
    from stusage
    where stuse_loc = stk_loc
    and stuse_product = stk_product
    AND ((stuse_year = fromYear and stuse_period >= fromMonth) OR (stuse_year = toYear and stuse_period <= toMonth) OR (stuse_year > fromYear AND stuse_year < toYear))
    )
    as "Transfer",
    stk_stkqty as "Physical",
    stk_allstk as "Allocated",
    stk_stkqty - stk_allstk as "Free",
    stk_value as "Value"
    from cmgroup, cmprod, stock, stmove
    where cmgrp_group between groupstart and groupend
    and cmgrp_group = cmp_group
    and cmp_product = stk_product
    and cmp_special = 'N'
    and TRIM(UPPER(stk_loc)) = branch
    and stk_stkqty > 0
    and stmov_loc = stk_loc
    and stmov_product = stk_product
    and not (stmov_trantype = 'O' and stmov_source = 'R' and stmov_ledacct = 'S')
    and stmov_trandate > ADD_MONTHS(CURRENT_DATE, -12)
    group by stk_loc, cmgrp_group, stk_product, cmp_desc, stk_stkqty, stk_allstk, stk_value
    order by "Group", "Product";

    END;

    end getoverstock;

    end overstock;


    thanks in advance
    Mac.

  2. #2
    Join Date
    Jun 2007
    Posts
    2

    solved

    Ok so I was being a wally using %type for the package procedure definition and not in the procedure itself.

    Amazing what you can't see when you stare at the same thing for hours.

Posting Permissions

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