Re: problem returning NOTFOUND when dynamic SQL used

You need to FETCH from this cursor at least once. Then you'll have %NOTFOUND initialized to TRUE if fetch didn't retrieve anything.

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Paul Fell" <novaweb_at_iinet.net.au> wrote in message
news:4b270a4e.0204222016.11ca38cb_at_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;