How to use Oracle PL/SQL to create ten similar tables from a given Oracle source table

Question

I have data in one table in a oracle database and I want to “split” this table in SQLplus into ten tables based on the content of one column.

Source table is all_data with columns:

kind, time_period, amount

kind is the row to split on: the value of kind In each row is exactly one of ten different names "peter", "maria", "eric", etc...

Now I want to create ten tables, one for each name. The first table for name "peter" would be created manually in SQL as:

CREATE TABLE peter_data  AS (
  SELECT p.kind, p.time_period, p.amount as amount_peter
    FROM all_data
    WHERE kind = 'peter'
)
;

How can I use PL/SQL to create all ten tables peter_data, maria_data, eric_data, etc.?

---

I tried:

DECLARE
    TYPE array_t IS VARRAY(3) OF VARCHAR2(10);
    ARRAY array_t := array_t('peter', 'maria', 'eric');   
BEGIN
    FOR i IN 1..ARRAY.COUNT LOOP
        CREATE TABLE ARRAY(i) AS (
          SELECT p.kind, p.time_period, p.amount as amount_peter
            FROM all_data
            WHERE kind = ARRAY(i)
        )
        ;
   END LOOP;
END;

but this gives understandably the error “PLS-00103: Encountered the symbol "CREATE"...”

Answer 1

You just need to wrap your DDL statement in a string and call EXECUTE IMMEDIATE:

DECLARE
  TYPE array_t IS VARRAY(3) OF VARCHAR2(10);
  ARRAY array_t := array_t('peter', 'maria', 'eric');   
BEGIN
  FOR i IN 1..ARRAY.COUNT LOOP
    EXECUTE IMMEDIATE
      'CREATE TABLE ' || ARRAY(i) || ' ( kind, time_period, amount_' || ARRAY(i) || ' ) AS'
      || ' SELECT kind, time_period, amount'
      || ' FROM all_data WHERE kind = ''' || ARRAY(i) || '''';
   END LOOP;
END;
/

(and remove p. as you didn't define the alias p anywhere.)

Then, if you have the table:

CREATE TABLE all_data ( kind, time_period, amount ) AS
SELECT 'peter', DATE '2020-01-01', 23 FROM DUAL UNION ALL
SELECT 'maria', DATE '2020-02-01', 42 FROM DUAL UNION ALL
SELECT 'eric',  DATE '2020-03-01', 11 FROM DUAL;

Then you get the tables:

SELECT * FROM peter;

KIND  | TIME_PERIOD         | AMOUNT_PETER
:---- | :------------------ | -----------:
peter | 2020-01-01 00:00:00 |           23

SELECT * FROM maria;

KIND  | TIME_PERIOD         | AMOUNT_MARIA
:---- | :------------------ | -----------:
maria | 2020-02-01 00:00:00 |           42

SELECT * FROM eric;

KIND | TIME_PERIOD         | AMOUNT_ERIC
:--- | :------------------ | ----------:
eric | 2020-03-01 00:00:00 |          11

db<>fiddle here

Answer 2

You need dynamic SQL for it and with Oracle we can use execute immediate to do so.

declare
  type array_t is table of varchar2(10);
  array array_t := array_t();
  lo_stmt varchar2(2000);
begin
  select distinct kind
    bulk collect into array
    from all_data;
  
  for i in 1..array.count loop
    --dbms_output.put_line(array(i));
    lo_stmt :=
    'create table '||array(i)||'_data '||
    'as '||
    'select kind,time_period,amount '||
    '  from all_data '||
    ' where kind = '''||array(i)||'''';
    --dbms_output.put_line(lo_stmt);
    execute immediate lo_stmt;
  end loop;
end;
/

Demo can be seen here

OR with a single statement

declare
  type array_t is table of varchar2(4000);
  array array_t := array_t();
  lo_stmt varchar2(2000);
begin
  select distinct 'create table '||kind||'_data '||
    'as '||
    'select kind,time_period,amount '||
    'from all_data '||
    'where kind = '''||kind||''''
    bulk collect into array
    from all_data;
  
  for i in 1..array.count loop
    --dbms_output.put_line(array(i));
    execute immediate array(i);
  end loop;
end;
/

Also works here