Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2006
    Posts
    12

    Unanswered: Implementing oracle Ref Cursors in DB2 UDB V8.2

    Hi,

    Kindly provide me with a solution on implementing oracle ref cursors in DB2 UDB.The sample code in oracle is given below:-

    CREATE OR REPLACE PACKAGE pack_AcBudget AS
    TYPE budget_Cur IS REF CURSOR;
    PROCEDURE proc_GetFiscYear(p_Cursor IN OUT budget_Cur);
    PROCEDURE proc_GetBudgetGroup(p_Cursor IN OUT budget_Cur);
    PROCEDURE proc_GetBudgetItem(p_BudgetCode IN NUMBER,
    p_Cursor IN OUT budget_Cur);
    PROCEDURE proc_GetBudgetDetail(p_Code IN NUMBER,
    p_Cursor IN OUT budget_Cur);
    END pack_AcBUdget;
    /

    CREATE OR REPLACE PACKAGE BODY pack_AcBudget AS

    PROCEDURE proc_GetFiscYear(p_Cursor IN OUT budget_Cur) IS
    BEGIN
    OPEN p_Cursor FOR
    SELECT
    TO_CHAR(startDate,'YYYY') fiscYear,
    TO_CHAR(startDate,'MON-YYYY') || '-' || TO_CHAR(endDate,'MON-YYYY') range
    FROM
    acFiscYear
    ORDER BY
    startDate
    DESC;
    END proc_GetFiscYear;

    PROCEDURE proc_GetBudgetGroup(p_Cursor IN OUT budget_Cur) IS
    BEGIN
    OPEN p_Cursor FOR
    SELECT
    code,
    budgetCode,
    budgetName,
    groupCode
    FROM
    acBudgetGroup
    CONNECT BY PRIOR code = groupCode
    START WITH groupCode IS NULL;
    END proc_GetBudgetGroup;
    PROCEDURE proc_GetBudgetItem(p_BudgetCode IN NUMBER,
    p_Cursor IN OUT budget_Cur) IS
    BEGIN
    OPEN p_Cursor FOR
    SELECT
    acBudgetItem.code,
    acBudgetItem.itemCode,
    stPharmacopia.itemName
    FROM
    acBudgetItem,stPharmaCopia
    WHERE
    acBudgetItem.budgetCode = p_BudgetCode
    AND
    acBudgetItem.itemCode = stPharmacopia.code
    AND
    acBudgetItem.type = 1;

    END proc_GetBudgetItem;

    PROCEDURE proc_GetBudgetDetail(p_Code IN NUMBER,
    p_Cursor IN OUT budget_Cur) IS
    BEGIN
    OPEN p_Cursor FOR
    SELECT
    a.Code as Code,
    a.BudgetCode,
    a.BudgetName,
    a.GroupCode,
    b.BudgetCode as GroupBudgetCode,
    b.BudgetName as GroupBudgetName,
    a.Category,
    a.Description,
    a.Remarks
    FROM
    ACBudgetGroup a,
    ACBudgetGroup b
    WHERE
    a.GroupCode = b.Code(+)
    AND
    a.Code = p_Code;
    END proc_GetBudgetDetail;

    END pack_AcBUdget;
    /

    Regards,
    Mahesh
    Mahy

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Do you have any ideas on how to do this ? If you have tried something, please post ...

    Please also take a few minutes to read
    http://www.dbforums.com/showthread.php?t=854783

    You should be able to get most answers from db2 migration portal
    www.ibm.com/db2/migration
    if you can spare some time to read the maual ..

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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