Oracle sql loader使用案例

Listing 1:
----------------------
dir *.csv

type abc.csv

sqlplus scott/tiger@orcl


create table emp1 (name varchar2(80), salary number);


host


notepad abc.ctl


load data
infile abc.csv
into table emp 
fields terminated by ','
TRAILING NULLCOLS
(name, salary)


sqlldr scott/tiger@orcl abc.ctl


exit


show user


select * from emp1;


----------------------
Listing 2:
---------------------
set heading off
set pagesize 0


spool c:\whatever.txt
select object_name ||','||object_type from all_objects
spool off


create table loaded as
select object_name, object_type from all_objects
where 1=0;


load data
infile 'c:\whatever.txt'
into table loaded truncate
when object_type <> 'INDEX'
fields termindated by ','
(
    OBJECT_NAME,
    OBJECT_TYPE "TRIM (:object_type)"
)


sqlldr control=c:\whatever.ctl log=c:\whatever.log

-----------------------------------
present  by  dylan.