Re: Dynamic SQL EXECUTE IMMEDIATE ERROR HANDLING

  • From: Mindaugas Navickas <mnavickas@xxxxxxxxx>
  • To: paulastankus@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 17 Aug 2006 17:15:58 -0400 (EDT)

Hi Paula,

I am sure that there are different ways of doing it - here is mine (build on
top of your code:
--------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE TEST IS
  ERRonlyOnePK   EXCEPTION;
  PRAGMA EXCEPTION_INIT (ERRonlyOnePK, -2260);
  SQLarr      DBMS_SQL.VARCHAR2_TABLE;
BEGIN
  DBMS_OUTPUT.put_line ('Adding Indexes - ' || TO_CHAR (SYSDATE, 'MM/DD/YYYY
HH:MI:SS'));
  SQLarr (1) :=
    'alter table MV_AGE_GENDER add constraint PK_AGE_GENDER primary key
(AGE_GENDER_ID) using index tablespace SERC_IDX_TS';
  SQLarr (2) :=
    'alter table MV_AGE_GENDER add constraint PK_AGE_GENDER primary key
(AGE_GENDER_ID) using index tablespace SERC_IDX_TS';
  SQLarr (3) :=
    'alter table MV_COUNTY_FACTOR add constraint PK_COUNTY_FACTOR primary key
(COUNTY_FACTOR_ID) using index tablespace SERC_IDX_TS';
  SQLarr (4) :=
    'alter table MV_PLAN add constraint PK_PLAN primary key (PLAN_ID) using
index tablespace SERC_IDX_TS';
  SQLarr (5) :=
    'create index IDX_PLAN_AUTHORITY_ID on MV_PLAN (AUTHORITY_ID) tablespace
SERC_IDX_TS';
  SQLarr (6) :=
    'create index IDX_PLAN_COVERAGE_TYPE_NAME on MV_PLAN (COVERAGE_TYPE_NAME)
tablespace SERC_IDX_TS';
  SQLarr (7) :=
    'create index IDX_PLAN_PLAN_TYPE_NAME on MV_PLAN (PLAN_TYPE_NAME)
tablespace SERC_IDX_TS';

  FOR i IN SQLarr.FIRST .. SQLarr.LAST LOOP
    BEGIN
      EXECUTE IMMEDIATE SQLarr (i);
    EXCEPTION
      WHEN ERRonlyOnePK THEN
        NULL;  -- Ignore it
      WHEN OTHERS THEN
        RAISE;
    END;
  END LOOP;

  DBMS_OUTPUT.put_line ('Finished  - ' || TO_CHAR (SYSDATE, 'MM/DD/YYYY
HH:MI:SS'));
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END TEST;
/
--------------------------------------------------------------------------

Regards
Mindaugas



__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
--
//www.freelists.org/webpage/oracle-l


Other related posts: