Re: PL/SQL Question

Jessica Dancy <dancyj_at_pobox.upenn.edu> wrote:

>I am trying to get the following procedure to simply read a row from a
>table via a select statement, using an implicit cursor. If the table is
>empty, I want to add a new tuple, if the tuple already exists, I just
>want to modify the tuple.
 

>The procedure works if data already exists, yet I get an error message
>if no matches are found.
 

>What is wrong with the code?
 

>***************************

...

Hi,
if select does not find a row at all, it raises an exception! Therefore you have to put the INSERT-statement into an exception handler:

CREATE OR REPLACE PROCEDURE x
...
SELECT ... UPDATE ... -- if found

...
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        INSERT ...
END; Or try to use SELECT COUNT(*) INTO number FROM..., which does not raise an exception if no rows found.

Bye,
Werner Received on Thu Feb 13 1997 - 00:00:00 CET