Question about reraising an error

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:

Code:

---

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

---

Code:

---

        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. :)

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. ;)

Update never raises NO_DATA_FOUND

U have to do something like this.

begin
update;

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

Quote:

---

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.

Quote:

---

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.

Code:

---

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. :o

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

Quote:

---

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.