Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

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:

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

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
*/
share|improve this answer
    
That is exactly what I condensed from Sathyas proposals myself. –  bernd_k Dec 20 '11 at 10:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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