经常会用SQL* LOADER来倒数据,因此,编写各种控制文件就是个日常工作了,呵呵,这里提供了一个方法,可以根据表结构自动生成控制文件:
基于指定的表结构格式化SQL* LOADER的控制文件
set feedback off
set verify off
drop table select_text;
create table select_text (
text varchar2(2000)
);
accept 1 prompt "Enter Table Name:"
accept 2 prompt "Enter Table Owner:"
declare
cursor cur IS
select owner
, table_name
, column_name
, decode(data_type,
'NUMBER','decimal external',
'DATE' ,'date (11)'
,'char ('||DATA_LENGTH||')') data_type
, column_id
from dba_tab_columns
where table_name = upper('&&1')
and owner = upper('&&2')
order by column_id;
l_curr_line VARCHAR2(2000);
l_owner sys.dba_tables.owner%TYPE;
l_table_name sys.dba_tables.table_name%TYPE;
begin
l_curr_line := '
LOAD DATA
REPLACE
INTO TABLE ';
select owner, table_name
into l_owner, l_table_name
from dba_tables
where table_name = upper('&&1')
and owner = upper('&&2');
l_curr_line := l_curr_line||l_owner||'.'||l_table_name||'
FIELDS TERMINATED BY '||''''||','||''''||'
OPTIONALLY ENCLOSED BY '||''''||'"'||''''||'
TRAILING NULLCOLS
(';
for rec in cur loop
if rec.column_id = 1 then
l_curr_line := l_curr_line||'
'||rpad(rec.column_name,35)||rec.data_type;
else
l_curr_line := l_curr_line||'
, '||rpad(rec.column_name,35)||rec.data_type;
end if;
end loop;
l_curr_line := l_curr_line||')';
insert into select_text values(l_curr_line);
commit;
end;
/
set pages 900
set lines 80
col text format a80
set head off
set term off
spool select.tmp
select * from select_text;
spool off
set term on
ed select.tmp
生成的文件可以根据需要稍作修改,呵呵