How to catch and handle only specific Oracle exceptions?

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?

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

StackExchange Database Administrators Q#9441, answer score: 39