Oracle: Return multiple records from a stored procedure or function

[DinoVaught]

Does anyone know how to return multiple rows and records from a stored procedure or function in Oracle?

I have procedures like . . .


PROCEDURE p_Get_UOM_RV(pOrg_Code IN VARCHAR2,
pUOM_Out OUT VARCHAR2)
IS BEGIN

IF pOrg_Code IN ('A','B','C','D') THEN
pUOM_Out := 'METRIC';
ELSE
pUOM_Out := 'IMPERIAL';
END IF;

END p_Get_UOM_RV;


That return a single value. This seems straight forward to me but how do you return multiple records / fields from a stored procedure / function?

[DinoVaught]

I figured it out. If anyone is interested. . .

In the Spec. . .


TYPE AutoMoveData_cur IS REF CURSOR;

PROCEDURE p_Get_DJN_Routing_Info(pDJN IN VARCHAR2, pAM_Data OUT AutoMoveData_cur);


Then in the body or procedure. . .


PROCEDURE p_Get_DJN_Routing_Info(pDJN IN VARCHAR2, pAM_Data OUT AutoMoveData_cur)
IS
BEGIN
OPEN pAM_Data FOR
SELECT A.Field1, B.Field2
FROM TableA A, TableB B
WHERE A.id = B.id
AND A.Field1 = pDJN
ORDER BY A.Field1;
END p_Get_DJN_Routing_Info;