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!

Using SYS_REFCURSOR and NOTFOUND

592531Apr 14 2008 — edited Apr 15 2008
Hey world,
I read some documentation about the NOTFOUND property and as far as I concern it should return TRUE if 0 rows are returned, right?

well I have a stored procedure (SP_GET_CHANGE_USER_XREF) that returns a SYS_REFCURSOR, which looks like this:
PROCEDURE SP_GET_CHANGE_USER_XREF
(
	p_UserSeq IN POC_USER_INFO.POC_USER_SEQ%Type,
	p_IsAppLevel IN CHANGE_LOG.IS_APP_LEVEL%Type,
	recordSet OUT SYS_REFCURSOR
)	
AS
	v_UserChangeSeq NUMBER;
BEGIN
	

	if (p_IsAppLevel = 'N') then
		begin
			open recordSet for select distinct CAT_CODE, DESCRIPTION, UPTIME from CHANGE_USER_XREF_VW where POC_USER_SEQ = p_UserSeq and CHANGE_SEQ > v_UserChangeSeq and IS_APP_LEVEL = 'N' ORDER BY CAT_CODE;
		end;
	else
		begin
			open recordSet for select distinct DESCRIPTION, UPTIME from CHANGE_USER_XREF_VW WHERE IS_APP_LEVEL = 'Y' and CHANGE_SEQ > v_UserChangeSeq;
		end;
	end if;	
	
END SP_GET_CHANGE_USER_XREF;	 
And I created a function (SHOULD_USER_SEE_REPORT) that will return a varchar(Y or N) indicating whether rows are returned from the store procedure above.
FUNCTION SHOULD_USER_SEE_REPORT
	(
		p_UserSeq IN POC_USER_INFO.POC_USER_SEQ%Type	  
	)	RETURN VARCHAR2
	AS
	  rc SYS_REFCURSOR;
	BEGIN
	  SP_GET_CHANGE_USER_XREF(p_UserSeq, 'N', rc);	  
	  
	  if(rc%NOTFOUND) then	
	  
	      return 'N';
	    else
	      return 'Y';
	  end if;
    END SHOULD_USER_SEE_REPORT;
With the following lines on SQL Plus I can test different records and see which return rows which don't

var rc refcursor;
exec PKG_VIEWER.SP_GET_CHANGE_USER_XREF(1, 'Y',:rc);
print rc;

However, the return value of this last function is always Y, even when I see that there were not rows returned.

Could somebody please help me figure out what I am doing wrong?

Any help would be appreciated.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 13 2008
Added on Apr 14 2008
12 comments
2,146 views