Create Oracle Tables in a Loop

loops, oracle, plsql

Solution

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.

Problem

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....

Original source