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


i trying run block of code in oracle , exits block if throws error. how overcome it? tried adding exceptions , didn't work. below code , error.

> begin in  ( >     select constraint_name , table_name  >     user_constraints  >     constraint_type ='c' >     , status = 'enabled' ) loop dbms_utility.exec_ddl_statement('alter table "'|| i.table_name || '" > disable constraint ' || i.constraint_name); end loop; end; / 

and throws following error should ignored , block should continue executing.

begin * error @ line 1: ora-30671: cannot modify not null constraint on identity column ora-06512: @ "sys.dbms_utility", line 574 ora-06512: @ line 9 

i tried adding exceptions didn't work well.

you should use, nested begin-end block here, exception handling been inside inner block.

begin in  (      select constraint_name , table_name       user_constraints       constraint_type ='c'      , status = 'enabled' )   loop      begin       dbms_utility.exec_ddl_statement('alter table "'|| i.table_name || '"disable constraint ' || i.constraint_name);      exception      when others         /* exception handing here. */         null;      end;   end loop;   end;   / 

Comments

Popular posts from this blog

java - Date formats difference between yyyy-MM-dd'T'HH:mm:ss and yyyy-MM-dd'T'HH:mm:ssXXX -

c# - Get rid of xmlns attribute when adding node to existing xml -