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

[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.