Re: Dynamic SQL EXECUTE IMMEDIATE ERROR HANDLING

From: Paula Stankus <paulastankus_at_yahoo.com>
Date: Thu, 17 Aug 2006 13:38:37 -0700 (PDT)
Message-ID: <20060817203837.82128.qmail@web33207.mail.mud.yahoo.com>

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.
--

http://www.freelists.org/webpage/oracle-l Received on Thu Aug 17 2006 - 15:38:37 CDT