This discussion is archived
1 Reply Latest reply: Feb 21, 2011 8:37 AM by Dom Brooks RSS

Action Table with SQL Commands

614500 Newbie
Currently Being Moderated
Dear all,

I have a SQL SELECT command that produces a table where every row is a SQL DDL statement - in this case an ALTER TABLE ADD PARTITION statement. The next step that I'm not sure how it can be accomplished, is to action those SQL commands via a PL-SQL script. Meaning, something (scripted) that would execute the commands and create the partitions. SQL SELECT below:

SELECT 'ALTER TABLE F_SALES_TRENDS2 ADD PARTITION F_SALES_TRENDS2_' || SAR_JNJ_MONTH_CODE || ' VALUES (' || WM_CONCAT('''' || SAR_DATE_OID || '''') || ') TABLESPACE DATAMARTS_D;'
FROM D_SALE_RECOGNITION_CALENDAR
WHERE SAR_DATE_OID = SAR_BE_ID
GROUP BY SAR_JNJ_MONTH_CODE
ORDER BY SAR_JNJ_MONTH_CODE;

Many thanks for your support.

Kind regards,
Pedro Martins
  • 1. Re: Action Table with SQL Commands
    Dom Brooks Guru
    Currently Being Moderated
    Something like this (untested)?
    (No ';' need in dynamically executed statement)
    BEGIN
     FOR r IN 
      (SELECT 'ALTER TABLE F_SALES_TRENDS2 ADD PARTITION F_SALES_TRENDS2_' || SAR_JNJ_MONTH_CODE || ' VALUES (' || WM_CONCAT('''' ||  
      SAR_DATE_OID || '''') || ') TABLESPACE DATAMARTS_D' stmt
      FROM D_SALE_RECOGNITION_CALENDAR
      WHERE SAR_DATE_OID = SAR_BE_ID
      GROUP BY SAR_JNJ_MONTH_CODE
      ORDER BY SAR_JNJ_MONTH_CODE)
     LOOP
      DBMS_OUTPUT.PUT_LINE(r.stmt);
      EXECUTE IMMEDIATE r.stmt;
     END LOOP;
    END;
    /

Legend

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