Re: Dynamic SQL EXECUTE IMMEDIATE ERROR HANDLING

  • From: Paula Stankus <paulastankus@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 17 Aug 2006 13:38:37 -0700 (PDT)

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
    WHEN OTHERS THEN
      IF TO_CHAR(SQLCODE) = '-2260'
      THEN
        NULL; -- ignore this error.
      END IF;
    END TEST;
   
  Thanks,
  Paula

                
---------------------------------
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls.  Great rates 
starting at 1¢/min.

Other related posts: