Creating multiple tables with identical columns in Oracle

Question

I'm new to PL/SQL. Is it possible to create a given number of tables with identical columns but specific table names inside a while (or for) loop setting the individual table names with the help of string concatenation e.g. No_1, No_2, etc?

My attempt:

DECLARE
  my_num  integer := 1;
  conc char(1);
BEGIN
  WHILE my_num <= 5 LOOP
    select to_char(my_num) into conc from dual;

    Create table No_||conc (dists float);
    my_num :=my_num+1;
  END LOOP;
END;

It does not work. I would be grateful if someone could make this clear for me.

kfinity · Accepted Answer · 2019-12-02 20:27:25Z

3

You need an execute immediate statement to run DDL inside a PL/SQL block, like this:

DECLARE
  my_num  integer := 1;
  conc char(1);
BEGIN
  WHILE my_num <= 5 LOOP

    execute immediate 'create table No_' || my_num || ' (dists float)';

    my_num :=my_num+1;
  END LOOP;
END;

However, I want to point out that Oracle discourages the use of integer and float (in favor of number), and char in favor of varchar2. And I think your code would be more commonly written like this:

BEGIN
  FOR my_num in 1..5 LOOP
    execute immediate 'create table No_' || my_num || ' (dists number)';
  END LOOP;
END;

answered Dec 2, 2019 at 20:27

kfinity

9,1411 gold badge16 silver badges22 bronze badges

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

1 Comment

curiousMind_85 Over a year ago

Thank you for your clarification! Have a nice day!

Collectives™ on Stack Overflow

Creating multiple tables with identical columns in Oracle

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related