1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

To loop dynamical sql in procedure

Discussion in 'SQL PL/SQL' started by rohit_shinez, Jan 6, 2016.

  1. rohit_shinez

    rohit_shinez Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Hi Guys,

    I am having a procedure which will take two input parameters start_date and end_Date

    i need to loop in alter statement in procedure something like this

    Code (SQL):

    [code=SQL]ALTER TABLE M1
    EXCHANGE PARTITION FOR (TO_DATE('01-JAN-2015','dd-MON-yyyy'))
    WITH TABLE T2
    INCLUDING INDEXES;

    ALTER TABLE M1
    EXCHANGE PARTITION FOR (TO_DATE('02-JAN-2015','dd-MON-yyyy'))
    WITH TABLE T3
    INCLUDING INDEXES;
    .
    .
    .
     
    [/code]

    For eg i have dates in parameter something like 01-JAN-2015 to 04-JAN-2015
    i would require something like this where T is a constant variable i need to append T1,T2.T3 based on range dates.
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Rohit,

    Here's a sample PL/SQL which you can try and adapt to your needs: I'm not sure if I understand your T1, T2, T3... requirement but am sure you can fine-tune the following piece of code:

    Code (SQL):
    SET serverout ON

    DECLARE
    l_start_date DATE := to_date('01-JAN-2015', 'DD-MON-YYYY');
    l_end_date   DATE := to_date('04-JAN-2015', 'DD-MON-YYYY');
    l_str varchar2(2000);
    BEGIN
      FOR c IN (SELECT level n, l_start_date+level-1 dt FROM dual
                 CONNECT BY level <= (l_end_date-l_start_date)+1)
      loop
        dbms_output.put_line('Date:'||to_char(c.dt, 'dd/mm/yyyy'));
        l_str := 'ALTER TABLE M1 ' ||
                 'EXCHANGE PARTITION FOR (TO_DATE('||
                 ''''||to_char(c.dt, 'DD-MON-YYYY')||
                 ''''||',''DD-MON-YYYY'')) '||
                 'WITH TABLE T' || to_char(c.n+1) ||' ' ||
                 'INCLUDING INDEXES';
        dbms_output.put_line('SQL:'||l_str);
        -- execute immediate l_str;
      END loop;
    END;
    /
     
    Note:
    1) The "execute" portion is in comments ; just uncomment it to test (I don't have your data-model).
    2) Add the proper exception handling to complete the code.

    HTH,
    Rajen