Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

No data found - return cursor of variables

505977May 30 2006 — edited May 31 2006

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

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 28 2006
Added on May 30 2006
19 comments
1,454 views