8 Replies Latest reply: Jul 8, 2013 11:18 AM by user9358016 RSS

    to run mutipal SQLs in one procedure / function

    987213

      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!