Re: SELECT INTO when there is no data.

From: moshe <Moshe_t_at_yahoo.com>
Date: 22 Mar 2004 01:31:31 -0800
Message-ID: <616be6f6.0403220131.12cba5a0_at_posting.google.com>


Still, I can't use it in FOR LOOP (cursor). I have FOR LOOP, and I would like that the loop will continue even there's NO_DATA_FOUND or TOO_MANY_ROWS or other exceptions. I don't like to stop the procedure. I want to handle with that exceptions and keep moving to the next record. I read in the doc's many pages. I understood that the EXCEPTION comes after the END. Meaning that the EXCEPTION will stop my procedure/loop. and I can't go back in order to continue the procedure with the next record.

Am I right ? What would you do in that case ?

With lot of thanks,

    Moshe.

> > That's correct no doubt, but when I use exceptions handling - I lose
> > my workflow of the procedure. My procedure doesn't include only one
> > SELECT. The procedure contains other things.
> > In SQL Server there is something like IF EXISTS (select * from table)
> > THEN ...
> > Is there something like that in Oracle ?
> >
> > Thanks for all of you for your assistance,
> > Moshe.
>
> Why do you think it will not work? Why would you need anything else?
>
> Read the manuals. You can nest Blocks so you can bring the exception
> clause as close to the Select as you want or need. So given two
> unrelated queries you might block them as
>
> BEGIN
> SELECT
> EXECPTION
> WHEN...
> END
> BEGIN
> SELECT
> EXECPTION
> WHEN...
> END
>
> while two related, dependent queries might be blocked as:
> BEGIN
> SELECT
> BEGIN
> SELECT
> EXECPTION
> WHEN...
> END
> EXECPTION
> WHEN...
> END
>
> IOW, what Sybrand told you is a very good suggestion. Learn how to use
> your tools properly, you'll be much happier.
>
> HTH,
> ed
> (trying to program ORACLE in the same manner as SYBASE is like trying
> to use a Phillips screwdriver as a wood chisel.)
Received on Mon Mar 22 2004 - 10:31:31 CET

Original text of this message