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.