23

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?

2 Answers 2

38

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:

5
  • 1
    can't I go just go without the when others raise lines ?
    – bernd_k
    Dec 20, 2011 at 7:57
  • @bernd_k yes you do that, it goes as an unhandled exception however Dec 20, 2011 at 8:01
  • 2
    Please add a raise in your WHEN OTHERS when the sqlcode is NOT 955 =) Dec 20, 2011 at 10:46
  • 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. Dec 20, 2011 at 10:49
  • @VincentMalgrat You're correct. Dec 20, 2011 at 10:56
5

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
*/
1
  • That is exactly what I condensed from Sathyas proposals myself.
    – bernd_k
    Dec 20, 2011 at 10:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.