Re: Dynamic SQL EXECUTE IMMEDIATE ERROR HANDLING

From: Paula Stankus <paulastankus_at_yahoo.com>
Date: Thu, 17 Aug 2006 15:19:54 -0700 (PDT)
Message-ID: <20060817221954.26782.qmail@web33206.mail.mud.yahoo.com>

Thank you!

Mindaugas Navickas <mnavickas_at_yahoo.com> wrote: 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




 				
---------------------------------
Want to be your own boss? Learn how on  Yahoo! Small Business. 
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 17 2006 - 17:19:54 CDT