If you are on any supported version of Oracle, then search this site for

dbms_metadata.get_ddl

to see some examples and then check out the docs for the same.

If you are on 8i or earlier!!!!!! then read on:

Well, frankly, I would simply use:

exp userid=user/password rows=n grants=n indexes=n constraints=n owner=user
imp userid=user/password full=y indexfile=user.sql

then, user.sql will have everything you want.

short of that, you could turn your anonymous block into a function and then use it in a query as follows:

scott@ORA817.US.ORACLE.COM> create or replace function create_table( r_owner in varchar2, r_table_name in varchar2 )
2 return varchar2
3 as
4 starting boolean :=true;
5 begin
6 dbms_output.put_line('create table '||r_owner||'.'||r_table_name||'(');
7 for r in (select column_name, data_type, data_length, data_precision,
8 data_scale, data_default, nullable
9 from all_tab_columns
10 where table_name = upper(r_table_name)
11 and owner=upper(r_owner)
12 order by column_id)
13 loop
14 if starting then
15 starting:=false;
16 else
17 dbms_output.put_line(',');
18 end if;
19
20 if r.data_type='NUMBER' then
21 if r.data_scale is null then
22 dbms_output.put(r.column_name||' NUMBER('||r.data_precision||')');
23 else
24 dbms_output.put(r.column_name||'
25 NUMBER('||r.data_precision||','||r.data_scale||')');
26 end if;
27 else if r.data_type = 'DATE' then
28 dbms_output.put_line(r.column_name||' DATE');
29 else if instr(r.data_type, 'CHAR') >0 then
30 dbms_output.put(r.column_name||' '||r.data_type||'('||r.data_length||')');
31 else
32 dbms_output.put(r.column_name||' '||r.data_type);
33 end if;
34 end if;
35 end if;
36 if r.data_default is not null then
37 dbms_output.put(' DEFAULT '||r.data_default);
38 end if;
39 if r.nullable = 'N' then
40 dbms_output.put(' NOT NULL ');
41 end if;
42 end loop;
43 dbms_output.put_line(' ); ');
44 return null;
45 end;
46 /

Function created.

scott@ORA817.US.ORACLE.COM>
scott@ORA817.US.ORACLE.COM>
scott@ORA817.US.ORACLE.COM> select create_table( user, table_name )
2 from user_tables
3 where rownum < 5
4 /

CREATE_TABLE(USER,TABLE_NAME)
-----------------------------------------------------------------------------------------------------------------------------------





scott@ORA817.US.ORACLE.COM> spool generate_table.lst
scott@ORA817.US.ORACLE.COM> exec null;
create table SCOTT.A(
Y
NUMBER(,0) );
create table SCOTT.B(
X
NUMBER(,0) NOT NULL );
create table SCOTT.BONUS(
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(),
COMM NUMBER() );
create table SCOTT.CONTACT(
ACCOUNTNUMBER
NUMBER(10,0) NOT NULL ,
COMPANYNAME VARCHAR2(80),
CONTACTNAME VARCHAR2(80),
ADDRESS1 VARCHAR2(80),
ADDRESS2 VARCHAR2(80),
CITY VARCHAR2(40),
STATE VARCHAR2(20),
ZIP VARCHAR2(20),
COUNTRY VARCHAR2(20),
TELEPHONE VARCHAR2(20),
FAXNUMBER VARCHAR2(20),
EMAIL VARCHAR2(80) );

PL/SQL procedure successfully completed.

scott@ORA817.US.ORACLE.COM> spool off