create table exp_table(table_name varchar2(50),name_type varchar2(1000));


create or replace procedure pro_exp_table as
declare
c1 is select b.column_name,a.table_name,b.column_id,b.data_type from user_tables a,user_tab_columns b where a.table_name=b.TABLE_NAME;
v_column_name varchar2(50);
v_column_type varchar2(30);

begin
update exp_table set name_type=null;
open c1;
loop
 fetch c1.column_name into v_column_name;
 fetch c1.column_type into v_column_type;
 fetch c1.table_name into v_table_name;
 update exp_table set name_type=name||' '||v_column_name||' '||v_column_type||' ,' where table_name=v_table_name;
end loop;
update exp_table t set name_type=substr(t.name_type,1,length(name_type)-1);
commit;
end pro_exp_table;


spool create.sql;
select 'create table '||t.table_name||' ('||t.name_type||' );' from exp_table t order by t.table_name;
spool off;