Automatic simple sqlldr Test

Test procedure:
1. Create a table:

Create table spread_detail
(
Id number,
Datecreated DATE,
Partnername VARCHAR2 (200 BYTE ),
Adform VARCHAR2 (100 BYTE ),
Ipaddress VARCHAR2 (20 BYTE ),
Isavail NUMBER (1 ),
Regflag NUMBER (1 ),
Memo1 VARCHAR2 (100 BYTE)
);

Create unique index idx_spread_detail ON spread_detail (ID );

2. Create a sequence

Create sequence seq_spread_detail_id
Start with 0
Max value 999999999
MINVALUE 0
NOCYCLE
NOCACHE
NOORDER;

3. Write the automatically executed shell script file loadlog. sh (SSH requiring public key authentication, refer to the http://blog.csdn.net/wzy0623/archive/2007/06/14/1651722.aspx)

#! /Bin/bash

Cd $ HOME
.. Bash_profile

Scp oracle@10.1.9.1:/var/log/httpd/log 'date-d "yesterday" + % Y % m % D'. log/home/oracle/dbbat/loader/log92.txt

Cd $ HOME/dbbat/loader
Sqlldr userid = user1/passwd1 control = load_log92.ctl log = load_log92.log

Cp/home/oracle/dbbat/loader/log92.txt/home/oracle/dbbat/loader/log 'date-d "yesterday" + % Y % m % D'. log

4. Add the script in the previous step to crontab and perform the import operation at three o'clock every day.
00 3 ***/home/oracle/dbbat/loadlog. sh

5. Prepare the sqlldr control file load_log92.ctl.

LOAD DATA
INFILE 'log92.txt'
BADFILE 'log92. bad'
DISCARDFILE 'log92. dsc'

Into table "SPREAD_DETAIL"
APPEND

Fields terminated by x '9' -- optionally enclosed '"'
TRAILING NULLCOLS
(
IPADDRESS,
DATECREATED "to_date (: DATECREATED, 'yyyy-mm-dd hh24: mi: ss ')",
C1 FILLER, -- skip it
PARTNERNAME,
ADFORM constant '1 ',
ISAVAIL constant 0,
REGFLAG constant 0,
ID "SEQ_SPREAD_DETAIL_ID.nextval"
)

6. Test
The content of the input file is as follows:
60.214.238.147 07:27:24 a 234
222.170.163.205 07:27:24 a 234
125.110.32.32 07:27:24 a 234
61.190.135.210 07:27:26 a 234
60.19.2.20.2007-04-26 07:27:28 a 234
58.52.98.144 07:27:28 a 234
211.92.205.231 07:27:29 a 234
222.133.15.114 07:27:29 a 234
58.24.21.191 07:27:30 A 234
59.58.16.53 07:27:31 A 234
222.69.245.61 07:27:33 A 234

Execution result:
SQL * Loader: Release 10.2.0.1.0-production on Fri Jul 20 17:18:43 2007

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Commit point reached-logical record count 11

Select * From spread_detail;

0 2007-4-26 7:27:24 234 1 60.214.238.147 0 0
1 2007-4-26 7:27:24 234 1 222.170.163.205 0 0
2 2007-4-26 7:27:24 234 1 125.110.32.32 0 0
3 2007-4-26 7:27:26 234 1 61.190.135.210 0 0
4 2007-4-26 7:27:28 234 1 60.19.2.20.0 0
5 2007-4-26 7:27:28 234 1 58.52.98.144 0 0
6 2007-4-26 7:27:29 234 1 211.92.205.231 0 0
7 2007-4-26 7:27:29 234 1 222.133.15.114 0 0
8 2007-4-26 7:27:30 234 1 58.24.21.191 0 0
9 2007-4-26 7:27:31 234 1 59.58.16.53 0 0
10 2007-4-26 7:27:33 234 1 222.69.245.61 0 0