1.目录规划:
oracle@mdsp140:~/convertdata> pwd
/opt/oracle/convertdata //用于存放源数据、目标数据、转换脚本、导库脚本;
oracle@mdsp140:~/convertdata> ll
total 8
-rwxrwxrwx 1 oracle oinstall 397 2011-09-29 10:58 converter.sh //转换脚本,用于扫描源数据,提取符合规则的数据;
drwxrwxrwx 2 oracle oinstall 80 2011-09-29 10:49 source //源数据目录,用以存放待处理的数据;
-rwxrwxrwx 1 oracle oinstall 139 2011-09-29 10:47 sqlldr.ctl //导库脚本,该脚本由oracle自带的sqlldr工具,将ctl文件,导入库中;
drwxrwxrwx 2 oracle oinstall 80 2011-09-29 11:18 target //存放经过转换后的数据,作为导库脚本的数据源;
2.源数据设定:
oracle@mdsp140:~/convertdata/source> pwd
/opt/oracle/convertdata/source
oracle@mdsp140:~/convertdata/source> ls
data_sub.txt
oracle@mdsp140:~/convertdata/source> tail data_sub.txt
2,610010200845081,22365000081,0,-1,-1,NONGPRS,0,0,0,0,0,0,0,NO,NO,0,0,NOBPOS,BOTH //一行源数据
3.转换脚本:
oracle@mdsp140:~/convertdata> cat converter.sh
#!/bin/ksh
awk -F \, '{
if (match($1, "[0-9]+"))
{
field1 = $1;
field2 = $2;
field3 = $3;
printf("%s|%s|%s\n", field1, field2, field3);
}
}' ./source/* >> ./target/result.dat
//如果在windows下编写该文件,上传后,出现"^M"提示时,可用以下指领,进行文件过滤;
oracle@mdsp140:~/convertdata>cat -v converter.sh | tr -d "^M" > converter.sh
4.转换后生成的数据文件
oracle@mdsp140:~/convertdata/target> ls
result.dat
oracle@mdsp140:~/convertdata/target> tail result.dat
2|610010200845081|22365000081
5.导库脚本 sqlldr.ctl
load data
infile "./target/result.dat"
append into table import_temp
fields terminated by "|"
trailing nullcols
(HLR_INDEX,IMSI,MSISDN)
6. 创建数据库用户MDSP
7.创建导入的表:
create table import_temp (
HLR_INDEX varchar2(50),
IMSI varchar2(50),
MSISDN varchar2(50))
8.执行导库指令,导库;
//该步骤,应在与ctl文件同级目录,执行,且当前系统用户为oracle
sqlldr userid=mdsp/mdsp control=./sqlldr.ctl
也可用这种格式:
sqlplus mdsp/mdsp@本地命名 control=./sqlldr.ctl //本地命名服务,需要手动进行配置;