just put it in quotes and add execute immedite. you can also use concantenation and variables ot make it more dynamic.
declare begin execute immediate 'CREATE INDEX ART_HIERARKI_LOCKEDIX1 ON ART_HIERARKI_LOCKED(SELSKAP, SETT_ID, VGRP, ART_GRP, ART_UGRP) TABLESPACE PBKIX PCTFREE 10 STORAGE(INITIAL 65536 NEXT PCTINCREASE )'; end; you could for instance do this. proc makeIndex(p_indexName IN VARCHAR2) IS begin execute immediate 'CREATE INDEX '||p_indexName||' ON ART_HIERARKI_LOCKED(SELSKAP, SETT_ID, VGRP, ART_GRP, ART_UGRP) TABLESPACE PBKIX PCTFREE 10 STORAGE(INITIAL 65536 NEXT PCTINCREASE )' you will need to put close and opening quotes on each line and use || to concatenate together. you can do any type of SQL with execute immediate including Alter System and alter session commands. Its pretty robust. > > From: [EMAIL PROTECTED] > Date: 2003/06/05 Thu AM 06:54:42 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Create index script in pl/sql > > Hallo all, > > anyone whom could help me with this: > > I would like to have an example of pl/sql procedur which does the following: > > CREATE INDEX ART_HIERARKI_LOCKEDIX1 ON > ART_HIERARKI_LOCKED(SELSKAP, SETT_ID, VGRP, ART_GRP, ART_UGRP) > TABLESPACE PBKIX PCTFREE 10 STORAGE(INITIAL 65536 NEXT PCTINCREASE ) > ; > > CREATE UNIQUE INDEX PK_ART_HIERARKI_LOCKED ON > ART_HIERARKI_LOCKED(SELSKAP, SETT_ID, ARTNR) > TABLESPACE PBKIX PCTFREE 10 STORAGE(INITIAL 65536 NEXT PCTINCREASE ) > > So the pl/sql procedur should create those 2 indexesd, how can I write the pl/sql > code then? > > > Thanks in advance > > > Roland > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
