Create multiple tables with array variable

Question

here is the problem.

I read year numbers from a table and insert them in another one (now static but in the end temp table)

Then I look over those numbers and create tables with them.

BEGIN 
 INSERT INTO temp_year
  ( year_column )
  ( 
    select extract(year from datum) from datetest
  ); 

FOR counter_id IN ( SELECT * FROM temp_year )
LOOP  
      EXECUTE IMMEDIATE 'Create table YEAR_' || counter_id || ' (year int, name char(50))'        
END LOOP; 
END; 
/

Temp_year has a column year_column (int) (filled like 2012) datatest has a date colum with values like 10.02.2012

The result should be a table named YEAR_2012 with columns year int and name char(50)

However this is not working. I quits at the execute immediate part, even when there are years in the temp_year table.

Any ideas??

Thanks in advance.

THeVagabond

Wernfried Domscheit · Accepted Answer · 2014-02-04 11:14:30Z

1

Try this one:

EXECUTE IMMEDIATE 'Create table YEAR_' || counter_id.year_column || ' (year integer, name varchar2(50))';

(Do not miss the semicolon at the end)

edited Feb 4, 2014 at 11:14

answered Feb 4, 2014 at 11:05

Wernfried Domscheit

60.5k10 gold badges93 silver badges132 bronze badges

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

4 Comments

Thevagabond Over a year ago

I get: the amount or type of arguments in the call || is wrong (translated from german so it might not be the 100% english error message) Error report: ` ORA-06550: line 10, column 29:` ` PLS-00306: Falsche Anzahl oder Typen von Argumenten in Aufruf von '||'` ` ORA-06550: line 10, column 11:` PL/SQL: Statement ignored ` 06550. 00000 - "line %s, column %s:\n%s"` ` *Cause: Usually a PL/SQL compilation error.`

Wernfried Domscheit Over a year ago

Sorry, I updated it. Note, you can change error messages to English by ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';

Thevagabond Over a year ago

Thanks it is working now. However I get this: Error report: ORA-00955: name is already used by an existing object ORA-06512: at line 10 00955. 00000 - "name is already used by an existing object" *Cause: Why is that so?

Thevagabond Over a year ago

After doing the whole thing with a temp table it is working great. THANKS!

Collectives™ on Stack Overflow

Create multiple tables with array variable

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related