set verify off
set feedback off
set serveroutput on
set trimspool on

spool c:\projects\temp\gen_crea_tab.txt

clear buffer
define bv_incld_tab_name = '%'
define bv_excld_tab_name = 'STG%'

declare
--
cursor c_tabs is
select table_name
from user_tables
where table_name like '&bv_incld_tab_name'
and table_name not like '&bv_excld_tab_name'
order by table_name;
--
cursor c_columns (p_table_name varchar2) is
select column_name, data_type, data_length,
data_precision, data_scale, nullable,
data_default
from user_tab_columns
where table_name = p_table_name
order by column_name;
-- order by column_id;
--
mb_first boolean := true;
mv_column varchar2(4000);
mv_datatype varchar2(4000);
mv_nn varchar2(4000);
mv_default varchar2(4000);
--
function nvlv2
(
p_val1 varchar2,
p_val2 varchar2,
p_val3 varchar2
)
return varchar2
is
--
--
begin
--
if p_val1 is null then
return p_val3;
else
return p_val2;
end if;
--
end nvlv2;
--
begin
--
dbms_output.enable(buffer_size => 1000000);
--
for i in c_tabs
loop
--
mb_first := true;
--
dbms_output.put_line('create table ' || lower(i.table_name));
dbms_output.put_line('(');
--
for j in c_columns(i.table_name)
loop
--
if not mb_first then
dbms_output.put_line(',');
else
mb_first := false;
end if;
--
if j.data_type = 'DATE' then
mv_datatype := 'date';
elsif j.data_type = 'NUMBER' then
mv_datatype := 'number';
if j.data_precision is not null then
mv_datatype := mv_datatype || '(' || j.data_precision;
if j.data_scale is not null then
mv_datatype := mv_datatype || ', ' || j.data_scale || ')';
else
mv_datatype := mv_datatype || ')';
end if;
end if;
elsif j.data_type = 'VARCHAR2' then
mv_datatype := 'varchar2' || '(' || j.data_length || ')';
elsif j.data_type = 'CLOB' then
mv_datatype := 'clob';
end if;
--
if j.nullable = 'N' then
mv_nn := 'not null';
else
mv_nn := null;
end if;
--
if j.data_default is not null then
mv_nn := ltrim(mv_nn || ' default ') ||
rtrim(ltrim(j.data_default));
end if;
--
mv_column := '| ' || rpad(lower(j.column_name), 30, ' ');
if mv_nn is not null then
mv_column := mv_column || ' ' || rpad(mv_datatype, 15, ' ');
mv_column := mv_column || mv_nn;
else
mv_column := mv_column || ' ' || mv_datatype;
end if;
--
dbms_output.put(mv_column);
--
end loop;
--
dbms_output.put_line('');
dbms_output.put_line(');');
dbms_output.put_line('----');
dbms_output.put_line('---');
dbms_output.put_line('--');
--
end loop;
--
end;
/

spool off
set serveroutput off
set feedback on

----- Original Message -----
From: "akki " <oracledba-ezmlmshield-x95547572.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Friday, October 29, 2004 6:11 PM
Subject: database creation script


> hi all...
>
> i want a script which can create a "database creation script"
> of a particular oracle 9i database.
> i searched for this script but could not locate
> thanks in advance
> akki
> bhel
>
>
>
>
>
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these
terms:http://www.lazydba.com/legal.html
>
>


Oracle LazyDBA home page