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

[Guest]

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?

[Mark C. Stock]

<jeffchirco@gmail.com> wrote in message
news:1115832524.915141.237600@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

[Guest]

Thanks for the quick respons. The exception handler worked perfectly.