sqlldr的使用方法为SQLLDR keyword=value [,keyword=value,...]。
其中常用的关键字及使用示例可参考http://blog.knowsky.com/257572.htm
可以预先编写好ctl文件,将控制、格式写于ctl文件中再直接使用sqlldr进行输出,其使用方式如下:
sqlldr userid=$USER/$PASSWORD@$INS log=$Table_Log bad=$Table_Bad control=$Table_Ctl data=$Table_Data 其中 log,bad,control,data分别为日志,坏档,控制和来源数据文件
其中,ctl文件的详情可以参考官方文件
http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_control_file.htm#i1005907
The SQL*Loader control file is a text file that contains data definition language (DDL) instructions. DDL is used to control the following aspects of a SQL*Loader session:
-
Where SQL*Loader will find the data to load
-
How SQL*Loader expects that data to be formatted
-
How SQL*Loader will be configured (memory management, rejecting records, interrupted load handling, and so on) as it loads the data
-
How SQL*Loader will manipulate the data being loaded
目前使用的实例及解释如下:
load data --声明这是新的data load
characterset ZHS16GBK--载入字符集
into table F_CKZHFTP
Append—载入方式,可选的有append和truncate
fields terminated by ','—值域间隔
optionally enclosed by '"'—包含方式(与下面的trainlingnullcols配套可以在row中插入空值)
trailing nullcols –未有值的域写入null
(
SYS_BIZ_DATE "trim(:SYS_BIZ_DATE)", --trim函数去除空格(?)
AC_ID "trim(:AC_ID)",
AC_SEQ "trim(:AC_SEQ)",
SUBJECT_CD "trim(:SUBJECT_CD)",
……)
sqlplus及ctl文件中均可指定 data source, bad file等,视需求决定在何处指定这些文件