How to catch and handle only specific Oracle exceptions?

Question

From this and this i guess, that there is no predefined Named System Exceptions for ORA-00955.

How can I rewrite the following to catch only the error ORA-00955?

begin
 EXECUTE IMMEDIATE 'CREATE SEQUENCE S_TEST START WITH 1 INCREMENT BY 1';
exception when OTHERS then
 Null;
end;

BTW Is there any syntax to catch errors by just providing the error-codes?

Answer 1

You have two options:

---

Refer to the exception directly by number:

BEGIN
 EXECUTE IMMEDIATE 'CREATE SEQUENCE S_TEST START WITH 1 INCREMENT BY 1';
EXCEPTION
 WHEN OTHERS THEN
 IF SQLCODE = -955 THEN
 NULL; -- suppresses ORA-00955 exception
 ELSE
 RAISE;
 END IF;
END; 

---

Other option is to use EXCEPTION_INIT Pragma directive to bind a known Oracle error number to user defined exception;

DECLARE
 name_in_use exception; --declare a user defined exception
 pragma exception_init( name_in_use, -955 ); --bind the error code to the above 
BEGIN
 EXECUTE IMMEDIATE 'CREATE SEQUENCE S_TEST START WITH 1 INCREMENT BY 1';
EXCEPTION
 when name_in_use then
 null; --suppress ORA-00955 exception
END; 

---

BTW Is there any syntax to catch errors by just providing the error-codes?

Yes, I've demonstrated it in the first example

Further reading for variations on this:

• Oracle Reference Documentation on Handling PL/SQL Exceptions

• bernd_k: can't I go just go without the when others raise lines ?
• Sathyajith Bhat: @bernd_k yes you do that, it goes as an unhandled exception however
• Vincent Malgrat: Please add a raise in your WHEN OTHERS when the sqlcode is NOT 955 =)
• Vincent Malgrat: The OP may still want to have other errors raised. Your Exception block "as is" behaves exactly as a WHEN OTHERS THEN NULL. I think the OP wants something a bit more precise and subtle.
• Sathyajith Bhat: @VincentMalgrat You're correct.

Answer 2

Similar to what Sathya has already suggested, but I like to avoid when others completely if possible - an unhandled exception is usually the correct outcome for exceptions you aren't specifically handling:

create sequence foo;
/*
sequence FOO created.
*/
declare
 name_is_already_used_955 exception;
 pragma exception_init(name_is_already_used_955,-955);
begin
 execute immediate 'create sequence foo';
exception when name_is_already_used_955 then null;
end;
/
/*
anonymous block completed
*/

• bernd_k: That is exactly what I condensed from Sathyas proposals myself.