Question about reraising an error

[gandolf989]

I have a PL/SQL procedure where I need to update a record if it is there, or insert a new record if it isn't. I decided to try the update and if that fails because of no_data_found then do the insert. But if I get a different error I just want to reraise that error.

So my options are:

         DECLARE
         BEGIN
            UPDATE my_table_name SET ...;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               INSERT INTO my_table_name ...;
            WHEN OTHERS THEN
               RAISE;
         END;

         DECLARE
         BEGIN
            UPDATE my_table_name SET ...;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               INSERT INTO my_table_name ...;
            WHEN OTHERS THEN
               RAISE_APPLICATION_ERROR ( SQLCODE, SQLERRM );
         END;

Is there a reason to do one rather than the other? Which is better?

Thanks.

[marist89]

Typically, I use the RAISE_APPLICATION_ERROR for application specific errors. By just using RAISE I know that any procedures that call my procedure will have the opportunity to handle the error themselves. Not sure if that would work with RAISE_APPLICATION_ERROR, but why complicate matters when you don't have to? Alternatively, since you're a developer, you could shoot yourself.

[oracle_faq]

Update never raises NO_DATA_FOUND

U have to do something like this.

begin
update;

if sql%notfound then
insert;
end if;
end;

[gandolf989]

Originally posted by marist89
Typically, I use the RAISE_APPLICATION_ERROR for application specific errors. By just using RAISE I know that any procedures that call my procedure will have the opportunity to handle the error themselves. Not sure if that would work with RAISE_APPLICATION_ERROR, but why complicate matters when you don't have to? Alternatively, since you're a developer, you could shoot yourself.

You are starting to sound like a certain x-mas poo who shall remain nameless. It seems like raise; adds a meaningless error to the error stack. Hence using raise_application_error to reraise the last error.

[gandolf989]

Originally posted by oracle_faq
Update never raises NO_DATA_FOUND

U have to do something like this.

begin
update;

if sql%notfound then
insert;
end if;
end;

I found the example somewhere started working with it. I just now did a test and came up with the same results.

LIDXPNN - t31a=> begin
  2  update a_table set pkey=666 where 1=2;
  3  end;
  4  /
PL/SQL procedure successfully completed.

However, the question really revolves around how to reraise an error. In any case I need to change my code.

[pando]

Cant you use MERGE? It does what you want, update if exists otherwise insert

[gandolf989]

Originally posted by pando
Cant you use MERGE? It does what you want, update if exists otherwise insert

I will take a look at merge.