Batch data files import data to oracle with sqlldrr

Many people must have used sqlload to direct text data to oracle, but if there are hundreds or thousands of data text, or text data is generated in real time. How to import oracle in bulk for this scenario? here's how I deal with it.

The 1.shell script LoadData.sh is as follows

#! / bin/bashdata_dir=/home/bea/datasource/warn-log/dayflow # text data directory data_dir_bak=/home/bea/datasource/warn-log/dayflow_bakshell_dir=/home/bea/scriptwhile [1] docd $data_dirif [`pwd` = = $data_dir] then filelist= `ls-rt | grep dayflow.log_. `# data text file name matches fi#echo $filelistcd $shell_dirif [`pwd` = = $shell_dir] then for file in $filelist do date= `date "+% Y%m%d% H:%M:%S" `export LoadFileName=$data_dir "/" $file # is very important To load variables into the environment echo "Time is $date, start importing $LoadFileName data" sqlldr user_1/123456@213_db control=dayflow.log.ctl BAD=dayflow.log.bad echo "Time is $date, $LoadFileName data import" echo "start backing up the file $file" mv $LoadFileName $data_dir_bak echo "$file backup" donefidone

The most important thing to pay attention to in this place is

Export LoadFileName=$data_dir "/" $file

Here, the variable LoadFileName (text imported into oracle, absolute path) is export into the linux environment, so that the control file of sqlload can read the variable and import it.

2.sqlldr controls text dayflow.log.ctl

Load dataCHARACTERSET AL32UTF8infile'$LoadFileName'APPEND into table user_1.BIL_FLUX_HIGH_CURfields terminated by'| 'trailing nullcols (ACCS_NBR "TRIM (: ACCS_NBR)", DATE_ID "TRIM (: DATE_ID)", TOTAL_FLUX "TRIM (: TOTAL_FLUX)", sys_date "sysdate")

Please note that infile'$LoadFileName', here is the variable just export in shell. In this way, you can pass in the file name as a variable, so the batch import is a circular operation.