can you creating tables using variables in pl/sql

Q_Ball

hey guys,

i need to dynamically create tables in oracle using plsql. i cant explicitly state the table names because at runtime the script doesnt know them. so i need to use a variable that contains the table name and use the statement

CREATE TABLE tableNameVar etc.

but this throws up a table name error.

in the code i can get the table names from the other schema and store them in a table of varchar(20) but i cant recreate the tables in my schema.

is it possible to create tables like this?

all the best,

daz

declare
  -- Declare the PL/SQL table
  type deptarr is table of VARCHAR2(20)
       index by binary_integer;
  d_arr deptarr;

  -- Declare cursor
  type d_cur is ref cursor;
  c1 d_cur;

  i number := 1;
  
  sql_stmt VARCHAR2(200);
  
begin
  -- Populate the PL/SQL table from the cursor
  open c1 for select table_name from all_tables where owner = 'SCOTT';
  loop
    exit when c1%NOTFOUND;
    fetch c1 into d_arr(i);
    i := i+1;
  end loop;
  close c1;

  -- Display the entire PL/SQL table on screen
    for i in 1..d_arr.last loop
        --This is where i should create the table
        --Cant use EXECUTE IMMEDIATE because the create statement will be create table tablenamevar as select * from SCOTT.tablenamevar
    	dbms_output.put_line('TABLE NAME : '||d_arr(i) );
	dbms_output.put_line('---------------------------');
  end loop;


end;
/

even this doesnt work!

DECLARE
	sql_stmt VARCHAR2(200);
	tablename VARCHAR2(10) := 'TEST';
BEGIN
	sql_stmt := 'CREATE TABLE :x ( name VARCHAR2(20) )';
	EXECUTE IMMEDIATE sql_stmt USING tablename;	
END;
/

Q_Ball

never mind, got it. missing stupid ' marks :mad: