I guess I'm having an issue with returning a set of variables in a cursor. I basically have the following code. It checks a table for records for a particular user. If no records are found, I want to default the variables to 0. I use the variables later in the procedure (which isn't posted for brevity). How can I returned the variables into a cursor? I am trying the following but i get NO data found and it never returns the 0's in the cursor.
CREATE OR REPLACE PROCEDURE USP_METRICDATA_SEL(
v_apprid IN GP2.PM_MetricDefData.M_ApprId%TYPE,
v_camid IN GP2.PM_MetricDefData_Log.UserCAMId%TYPE,
cv_results OUT SYS_REFCURSOR)
IS
v_bread NUMBER(1);
v_bwrite NUMBER(1);
v_bapprove NUMBER(1);
v_bdelete NUMBER(1);
v_badmin NUMBER(1);
vctr NUMBER;
BEGIN
/* Check permissions table for multiple records per metric per user */
Select Count(SecId) into vctr From GP2.PM_Security
Where UserCAMId = v_camid and ApprId = v_apprid;
if vctr > 1 then
RAISE_APPLICATION_ERROR(-20001,'There is more than 1 permission record for this metric for this user. Please contact technical support for further assistance.');
end if;
/* Select permissions for metric */
if vctr = 1 then
Select bRead, bWrite, bApprove, bDelete, bAdmin into
v_bread, v_bwrite, v_bapprove, v_bdelete, v_badmin
From GP2.PM_Security
Where UserCAMID = v_camid and ApprId = v_apprid;
else
/* No permissions found so default to 0 to be returned */
Select 0,0,0,0,0 into
v_bread, v_bwrite, v_bapprove, v_bdelete, v_badmin from dual;
end if;
OPEN cv_results FOR
Select v_bread, v_bwrite, v_bapprove, v_bdelete, v_badmin from dual;
END;
Thanks,
Greg