Re: Update NOTFOUND from PL/SQL

On October 6, 1995, satish_at_cais.cais.com (Satish Rajan) wrote:

>A newbie question...
>
>I've written a PL/SQL storedproc to update a single row
>in a table. When I invoke it from a ProC program, it
>never tells me that the record I'm looking for is
>NOTFOUND. So I had to put in an SQL%NOTFOUND in the
>stored procedure and raise_application_error() to
>detect the notfound condition.
>
>Is there a better way of detecting notfound conditions
>in this situation?
>
>--

A better way to deal with the possibility of missing records for updates probably would have the following general structure:

• declare an explicit cursor and recordtype for the table you want to update
• open the cursor explicitly and fetch a record
• now you can check the <cursor>%FOUND or %NOTFOUND function to determine the proper action
• close the explicit cursor

If you include the pseudocolumn ROWID in the list of columns fetched from the table you want to update you can use that directly in the WHERE clause of the corresponding UPDATE statement which ensures efficiency in that part.

In the Oracle forum on CompuServe you can find some PowerPoint presentations by Steve Feuerstein which are very helpful in pointing out the do's and dont's of PL/SQL programming, one especially addressing cursor handling.

     Roland Received on Thu Oct 19 1995 - 00:00:00 CET