Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Dynamic SQL EXECUTE IMMEDIATE ERROR HANDLING
I have been hunting around. I have a procedure that executes a series of EXECUTE IMMEDIATE statements. However, I have noticed that when one fails - the procedure says it was run successfully but none of the subsequent statements are executed.
What is the best way to loop or perform error-handling between different EXECUTE IMMEDIATE statements.
SAMPLE: CREATE OR REPLACE PROCEDURE TEST
BEGIN DBMS_OUTPUT.put_line('Adding Indexes - ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS')); EXECUTE IMMEDIATE 'alter table MV_AGE_GENDER add constraint PK_AGE_GENDER primary key (AGE_GENDER_ID) using index tablespace SERC_IDX_TS'; EXECUTE IMMEDIATE 'alter table MV_COUNTY_FACTOR add constraint PK_COUNTY_FACTOR primary key (COUNTY_FACTOR_ID) using index tablespace SERC_IDX_TS'; EXECUTE IMMEDIATE 'alter table MV_PLAN add constraint PK_PLAN primary key (PLAN_ID) using index tablespace SERC_IDX_TS'; EXECUTE IMMEDIATE 'create index IDX_PLAN_AUTHORITY_ID on MV_PLAN (AUTHORITY_ID) tablespace SERC_IDX_TS'; EXECUTE IMMEDIATE 'create index IDX_PLAN_COVERAGE_TYPE_NAME on MV_PLAN (COVERAGE_TYPE_NAME) tablespace SERC_IDX_TS'; EXECUTE IMMEDIATE 'create index IDX_PLAN_PLAN_TYPE_NAME on MV_PLAN (PLAN_TYPE_NAME) tablespace SERC_IDX_TS';DBMS_OUTPUT.put_line('Finished - ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY HH:MI:SS')); EXCEPTION
IF TO_CHAR(SQLCODE) = '-2260' THEN NULL; -- ignore this error. END IF;
![]() |
![]() |