Following are some common usages for SQL*Loader, if you found any issues,please correct me, tks.
1 How to use a constant in a CTL file.
2 How to use a sequence for a column in the staging table
3 How to insert several staging tables at one time by the same text file
4 How to skip some columns in the text file when uploading to staging table
5 How to use a power function 'case when then end'
6 How many loading methods to upload data into staging talbe
7 How to use Functions in the CTL file
8 How to use the command 'sqlldr' in the linux
9 How to make a staging table not to create redo log
10 How to use SYSDATE in CTL file
Details:
1 How to use a constant.
1) FILE_NAME constant "FM_001"
2 How to use a sequence for a column in the staging table
1) RECORD_ID sequence(1,1)
3 How to insert several staging tables at one time by the same text file
1) load data
infile ‘data.dat’
into table xx_staging_table1 append
when batch_date=’2001′
(account_nbr position(01:10) character,
batch_date position(*:22)
into table xx_staging_table2 replace
when batch_date=’2001′
(account_nbr position(01:10) character,
batch_date position(*:22)
4 How to skip some columns in the text file when uploading to staging table
1) BRANCH FILLER
5 How to use a power function 'case when then end'
1) batch_date 'case
when length(:batch_date)<=10
then to_date(:batch_date,'yyyy/mm/dd')
when length(:batch_date)=11
then to_date(:batch_date,'DD/MON/YYYY')
end'
6 How many loading methods to upload data into staging talbe
1) insert --default method, before uploading the staging table must be null
2) append --add new records in the staging table
3) replace --before inserting delete the original data
4) truncate
7 How to use Functions in the CTL file
1) BATCH_DATE date 'yyyy/mm/dd'
2) LOCATION_ID "TRIM(:LOCATION_ID)"
8 How to use the command 'sqlldr' in the linux
1) sqlldr userid=apps/apps control=xx.ctl direct=true parralel=true
9 How to make a staging table not to create redo log
1) ALTER TABLE RESULTXT nologging
Once you complete it above, then add a line 'unrecoverable' before 'load data' with using 'direct' together.
10 How to use SYSDATE in CTL file
col3 expression "SYSDATE",
col4 expression "SYSTIMESTAMP"
col4 expression "SYSTIMESTAMP"
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10951282/viewspace-757895/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
