Re: problem returning NOTFOUND when dynamic SQL used

Paul Fell wrote:

>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;
>

Where's the FETCH statement ? Hard to test for NOTFOUND without trying to actually get the data. Received on Mon Apr 22 2002 - 23:33:53 CDT