How to ignore an ERROR while executing a block of code in ORACLE

Question

I am trying to run a block of code in oracle and it exits the block if it throws some error. How do I overcome it? I tried adding some exceptions and it didn't work. Below is the code and its error.

> begin for i in  (
>     select constraint_name , table_name 
>     from user_constraints 
>     where constraint_type ='C'
>     and status = 'ENABLED' ) LOOP dbms_utility.exec_ddl_statement('alter table "'|| i.table_name || '"
> disable constraint ' || i.constraint_name); end loop; end; /

and it throws the following error which should be ignored and the block should continue executing.

begin
*
ERROR at line 1:
ORA-30671: cannot modify NOT NULL constraint on an identity column
ORA-06512: at "SYS.DBMS_UTILITY", line 574
ORA-06512: at line 9

I tried adding Exceptions which didn't work well.

DO you still face issue?

Maheswaran Ravisankar
– Maheswaran Ravisankar

2015-09-21 13:37:28 +00:00
Commented Sep 21, 2015 at 13:37 — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Sep 21, 2015 at 13:37
Hi Mahesh, Sorry, Its clear now. Thanks. :)

sabarish jackson
– sabarish jackson

2015-09-22 04:16:51 +00:00
Commented Sep 22, 2015 at 4:16 — sabarish jackson
– sabarish jackson, Commented Sep 22, 2015 at 4:16

Maheswaran Ravisankar · Accepted Answer · 2015-09-21 12:16:32Z

3

You should use, nested begin-end block here, the exception handling been inside the INNER block.

begin for i in  (
     select constraint_name , table_name 
     from user_constraints 
     where constraint_type ='C'
     and status = 'ENABLED' )
  LOOP
     BEGIN
      dbms_utility.exec_ddl_statement('alter table "'|| i.table_name || '"disable constraint ' || i.constraint_name);
     EXCEPTION
     WHEN OTHERS THEN
        /* Your exception handing here. */
        NULL;
     END;
  end loop;
  end;
  /

answered Sep 21, 2015 at 12:16

Maheswaran Ravisankar

17.9k6 gold badges50 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to ignore an ERROR while executing a block of code in ORACLE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related