Create Oracle Tables in a Loop

Question

I am trying to create 1 PL/SQL statement that would allow me to get multiple table outputs after each successive datetime iteration and rename each table with the year of that datetime iteration. Example below with desired results. Thanks

  Create table MY_TIME_XX AS 
   ( Select 
    X.* 
    FROM Metadata X )
    Where X.Datetime between '01/01/2014' and '12/31/2014'

So in the end my schema will have

MY_TIME_14
MY_TIME_15
MY_TIME_16

etc....

Why do you like to do that? Most likely it does not make any sense. — Wernfried Domscheit
– Wernfried Domscheit, Commented Oct 27, 2016 at 17:55
the (Select * FROM Metadata X) is more than just what I showed for a five year span it is quite large as well. My temp file doesn't hold it well So I would like to Make a able for each year then put it into a partition single table. — Tinkinc
– Tinkinc, Commented Oct 27, 2016 at 18:16
Don't use string literals in date comparisons use either date literals or the to_date function: e.g. use to_date('01/01/2014','mm/dd/yyyy') or date '2014-1-1' instead of '01/01/2014' — Sentinel
– Sentinel, Commented Oct 27, 2016 at 20:32
Why not create the partitioned table to begin with. You can still insert a years worth of data at a time instead of all the data at once. — Sentinel
– Sentinel, Commented Oct 27, 2016 at 20:36
Don't understand the logic of wanting to do it. But wouldn't be hard to do. Have a look at docs.oracle.com/cd/B19306_01/appdev.../…. Just build your create table statement as a string then use execute immediate to fire it off. — Shaun Peterson
– Shaun Peterson, Commented Oct 28, 2016 at 5:21

APC · Accepted Answer · 2016-10-28 06:00:28Z

5

Jobs like this require dynamic SQL. Assuming you know the years in scope something like this should do it for you.

begin
    for idx in 2014..2016 loop
        execute immediate 
            'Create table MY_TIME_'|| idx ||' AS  
                Select  X.* 
                FROM Metadata X 
                Where to_char(X.Datetime, ''yyyy'') = '''|| idx||'''';
    end loop;
end;

Note the use of double quotes to escape literals in the string.

answered Oct 28, 2016 at 6:00

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create Oracle Tables in a Loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related