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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Aug 17 2006 - 16:15:58 CDT
