This discussion is archived
8 Replies Latest reply: Jul 8, 2013 9:18 AM by user9358016 RSS

to run mutipal SQLs in one procedure / function

987213 Newbie
Currently Being Moderated

Hi,

 

First of all, your help and efforts will be highly appreciated!

 

I am new to the Oracle world and learning to write PL/SQL with real world problems.

 

This is what I am facing now:

 

I have a bunch of SQL scripts need to run several times a year, they are simple and just to create/rebuild indexes on different tables. samples as below

CREATE INDEX ST_CODE_INDX_JAN2013 ON TREE_JAN2013
(CODE)
NOLOGGING
TABLESPACE ST_LRG_INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
CREATE UNIQUE INDEX ST_ID_INDX_JAN2013 ON RECORDS_JAN2013
(M_ID)
NOLOGGING
TABLESPACE ST_LRG_INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

 

And in total we probably will need to run around 100 scripts like this every time. Basically what we are doing is to import new tables every time and recreate/rebuild indexes. As you may see in the sample scripts above, all tables keep the same suffix but different table names. So, for now, what we are doing is to manually modify the script to reflex changes then kick it off.

 

Now, how can I create a procedure/function stored in database and waiting for a EXEC command to pass the suffix and then run automatically?

 

Once again, appreciated your great help!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points