Re: how to ignore "No Data Found" error during procedure.

<jeffchirco_at_gmail.com> wrote in message news:1115832524.915141.237600_at_z14g2000cwz.googlegroups.com...
>I am creating a procedure in Oracle 9i, and during my procedure I loop
> through a set of numbers (ie 1-10) and retrieve a number from tableA
> based on what number we are in the loop (tableA.dbid = 1). Say we get
> to number 10 but dbid value of 10 does not exist in tableA, then Oracle
> finds no data and stops the procedure and gives the error "No Data
> Found". Is there a way in oracle to assign the variable something if
> it come accross a No Data Found Error?
>

i can think of 3 ways to handle it:

1. exception handler (wrap the SELECT in a BEGIN ... EXCEPTION ... END block)
2. explicit cursor (OPEN .... FETCH ... CLOSE)
3. cursor in-line SELECT for loop (shortcut for #1 and #2, disdained by some when used for single-row fetches, but simplifies coding -- make sure to add a comment if using this technique to fetch a single row)
4. ok, may 4 ways... select MAX() or MIN() ... this always returns a row (but is a somewhat artificial technique and can lead to confusion when maintaining code)

++ mcs Received on Wed May 11 2005 - 19:47:03 CEST