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

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
Must Read before posting

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