oracle 自动生成建表文本

/***************************************************************************************************/
--** **
--** 自动生成建表SQL文件存储过程 : SP_CREATE_TABLE_DDL **
--** v_Condition : 一个select查询条件,查出的是一条或者多条单列记录,该记录应为表名称 **
--** (请注意):该select语句内若出现单引号,则须在每个单引号之前再加一个单引号 **
--** v_Prefix : 预设导出文件名称的前缀,不需要则设为空值; **
--** v_Suffix : 预设导出文件名称的后缀(不是文件类型,类型已经默认为.ctl) **
--** **
--** 首先以dba或者是其他具有 create directory 的用户创建 directory,然后授权: **
--** create or replace directory CREATE_TABLE_DDL as 'C:\app\userdatabackup\portal\table_ddl'; **
--** grant read, write on directory CREATE_TABLE_DDL to portal; **
--** **
/***************************************************************************************************/

create or replace procedure SP_CREATE_TABLE_DDL(
v_DBlink varchar2,
v_Condition varchar2,
v_FileName varchar2,
v_Prefix varchar2,
v_Suffix varchar2
)
as
v_TableName varchar2(32);
v_DataLine varchar2(1024);
v_Count int;
v_MaxCount int;
type DynamicCursor is ref cursor;
c_Condition DynamicCursor;
outf utl_file.file_type;
begin

begin
utl_file.fremove('CREATE_TABLE_DDL',v_FileName||'.sql');
exception when others then null;
end;

open c_Condition for v_Condition;
loop

v_Count := 1;

fetch c_Condition into v_TableName;
exit when c_Condition%notfound;

execute immediate
'select max(COLUMN_ID) from USER_TAB_COLUMNS'
||v_DBlink
||' where TABLE_NAME = '''
||v_TableName
||''''
into v_MaxCount;

while v_Count <= v_MaxCount
loop
execute immediate
'select decode(COLUMN_ID,
1,
''create table ''||:v_Prefix||TABLE_NAME||:v_Suffix||''('')
||COLUMN_NAME
||'' ''
||DATA_TYPE
||decode(DATA_TYPE,
''VARCHAR2'',
''(''||to_char(DATA_LENGTH)||'')'',
''CHAR'',
''(''||to_char(DATA_LENGTH)||'')'',
''NUMBER'',
decode(DATA_PRECISION,
null,
'' '',
''(''||to_char(DATA_PRECISION)||'',''||to_char(DATA_SCALE)||'')''))
||decode(COLUMN_ID,
:v_MaxCount,
'')compress for direct_load operations nologging;'',
'','')
from USER_TAB_COLUMNS'||v_DBlink
||' where TABLE_NAME = :v_TableName
and COLUMN_ID = :v_Count'
into v_DataLine
using v_Prefix, v_Suffix, v_MaxCount, v_TableName, v_Count;

v_Count := v_Count + 1;

outf := utl_file.fopen('CREATE_TABLE_DDL',v_FileName||'.sql','a');
utl_file.put_line(outf,v_DataLine);
utl_file.fclose(outf);

end loop;

end loop;

end;

/*
call SP_CREATE_TABLE_DDL(
'',
'select OBJECT_NAME from USER_OBJECTS where substr(OBJECT_NAME,1,3) =

''DP_''',
'CREATE_TABLE_DDL',
'ODS_',
'_'||to_char(sysdate,'yyyymmdd')
);
*/