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!

Loop the table name from arraylist

Discussion in 'SQL PL/SQL' started by prabhur, Mar 21, 2015.

  1. prabhur

    prabhur Active Member

    Messages:
    24
    Likes Received:
    0
    Trophy Points:
    80
    Hi,
    I have ArrayList with values. I want to append the arraylist value in loop to the table name in a query.

    eg

    al=171,172,180, 185


    select * from PDTABLE_171
    select * from PDTABLE_172
    select * from PDTABLE_180
    select * from PDTABLE_185
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    You want to achieve this through PL/SQL Program ? and what is the next step after this, means reporting the data or what ?
     
  3. rajenb

    rajenb Forum Expert

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

    When you say "ArrayList with values", I guess you're hinting at the Java ArrayList which in Oracle PL/SQL world (as you've posted in this specific forum) and in the case you've provided can be mapped to a "TABLE OF NUMBER".

    Oracle provides a built-in type which can also be used: "sys.ODCINumberList".

    Here's a simple example to illustrate how you could use it to build your table names dynamically:

    Assumptions: Your dynamic table names have the same structure as the table PDTABLE

    Code (Text):
    DECLARE
      lt_ext sys.ODCINumberList := sys.ODCINumberList(171,172,180,185);
      l_sql VARCHAR2(2000);
      TYPE data_t IS TABLE OF PDTABLE%ROWTYPE;
      lt_rows data_t;
    BEGIN
      FOR i IN 1 .. lt_ext.COUNT
      LOOP
        l_sql := 'SELECT * FROM PDTABLE_' || lt_ext(i);
        dbms_output.put_line(l_sql);
        EXECUTE IMMEDIATE l_sql BULK COLLECT INTO lt_rows;
        /*
        Your processing ...
        */
      END LOOP;
    END;
    /
    As Bharat asked, we don't know exactly what's your requirement and what you need to do with the dynamically build SELECT * statements... but I hope that with this example, you can build up your PL/SQL logic :)