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

1. MY_TIME_14
2. MY_TIME_15
3. MY_TIME_16

etc....

Answer 1

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.