1. Ready to work, window can directly import files through Sqlload, Linux, need to have sqlload related software.
2. SQL Script (MID_DFDZ.CTL)
LOAD DATA
Into TABLE Mid_t0_trans
Truncate
Fields TERMINATED by ' | '
(
Stldate,
Merno,
Termno,
Pan
Transamt,
REFNO,
TERMSSN,
Batno,
TransDate,
Workdate,
Fee
)
3.shell Script (getdfdz.sh)
#!/bin/bash
If [$#-lt 2]
Then
echo "Miss Arguments"
Exit-1
Fi
#parameters
Batdate=$1
Seq_no=$2
File_dfdz=dfdz_${batdate}
#FTPADDR =172.16.3.8
ftpaddr=144.131.254.186
Ftpuser=dc
ftppswd=dc2012
#home/liushui/
Local_path= "/100600/xqfdzwj/dfdz"
#FTP_PATH = "/weblogic/100600/xqfdzwj/dfdznew/"
Ftp_path= "/dc/jy/dfdz/"
Dfdzpath=/dc/jy/dfdz
dfdzfile= $DFDZPATH/"dfdz_" $BATDATE. txt
Locfile=/dc/jy/ctl
Ftp-in $FTPADDR <<!
User $FTPUSER $FTPPSWD
Bin
LCD $FTP _path
CD $LOCAL _path
echo pwd
Get $FILE _dfdz
Bye
!
Dbuser=dc
dbpwd=dc_2015
Dbname=dcdb
dfdzctlfile= $LOCFILE/mid_dfdz.ctl
dfdzbadfile= $LOCFILE/${batdate}_dfdz.bad
dfdzlogfile= $LOCFILE/${batdate}_dfdz.log
Sqlplus-s ${dbuser}/${dbpwd}@${dbname}<<!
ALTER TABLE tb_maintenance nologging
!
Sqlldr Userid=${dbuser}/${dbpwd}@${dbname} control= $DFDZCTLFILE data= $DFDZFILE log= $DFDZLOGFILE bad= $DFDZBADFILE errors=1000000 rows=10000
Result=$?
If [$RESULT-ne 0]
Then
Setcmdrslt $SEQ _no 3
Exit-1
Fi
Result=$?
If [$RESULT-ne 0]
Then
Exit-1
Fi
Exit 0
4. TXT format files (t0_h_20151009.txt)
20151009|898340150399030|01086952|6214855491055386|1300.00|000789608053|000308|000001|133904|20151009|0.65
20151009|898320559990126|55447203|6225768706558846|4085.00|000789614773|001363|000001|134011|20151009|0.00
20151009|898620950130467|942e0166|6259654270166308|7930.00|000789615305|000218|000001|134011|20151009|3.97
20151009|898654059982123|65412446|6217858300020175452|42097.00|000789614807|000069|000001|134013|20151009| 21.05
20151009|898430153980696|21183401|6222060012065499|97555.00|000789615795|000102|000001|134014|20151009|48.78
20151009|898210245112423|15061518|4062522603299967|59900.00|000789615371|000833|000001|134016|20151009|29.95
20151009|898411459982063|41145254|4512893437225106|16000.00|000789615837|000121|000003|134017|20151009|16.00
20151009|898532550394078| tf407801|4581242413584282|22000.00|000789614459|000361|000001|134020|20151009|11.00
Use the shell to import a certain format TXT file into the database via SQL load