problem returning NOTFOUND when dynamic SQL used

From: Paul Fell <novaweb_at_iinet.net.au>
Date: 22 Apr 2002 21:16:10 -0700
Message-ID: <4b270a4e.0204222016.11ca38cb@posting.google.com>

Thanks to all those people who have responded to earlier email. I have a SQL Server background and still coming to terms with Oracle syntax.

Oracle 9i.

The following snip of dynamic sql works. I know there's bit here, but it's the test at the end that I can't get working. Whether records are found or not, the value of pCursor%NOTFOUND (or even SQL%NOTFOUND) is always NULL. Can someone tell me how I can get either a rowcount or whether records were found when dynamic sql is used. I can't see anything in the pl/sql manual

Thanks Paul

open pCursor for
'select DISTINCT nvl(a.Title,' || chr(39) || chr(39) ||') "Title",

             nvl(a.ID,' || chr(39) || chr(39) || ') "ID",
             nvl(b.Description,' || chr(39) || chr(39) || ')


"Subject",

             nvl(a.Publish_Date,' || chr(39) || chr(39) || ')


"Publish_Date",

             nvl(a.Employee_ID,' || chr(39) || chr(39) || ')


"Employee_ID",

             nvl(c.Preferred,nvl(c.Forenames,' || chr(39) || chr(39)
|| ')) "Preferred",
             c.Surname "Surname", 
             nvl(substr(a.Article,1,300),' || chr(39) || chr(39) || ')


"Article"

    from     tblNews a 
    inner join tblLUSubject b on 
             a.Subject_Code = b.Code 
    inner join tblNewsDepartmentsLink d on 
             a.ID = d.News_ID 
    left join tblLUDivision f on 
             d.Division_Code = f.Code 

    left join tblStaffDirectory c on
    a.Employee_ID = c.Employee_ID
    where (' || Subject || Divisions || ' ) AND (a.Publish_Date <= SYSDATE ' ||

	 ' AND a.Archived_Date >= trunc(SYSDATE)) 
     order by "Publish_Date" Desc, "Title"';
     

If pCursor%NOTFOUND then

    --no records returned
    pintReturnStatus := intNotFound;
end if; Received on Mon Apr 22 2002 - 23:16:10 CDT