梁**,居民身份证（户口薄）,210204****101****0,女,1966-10-14,医保个账,OB*************17,升级版,198.00 刘**,居民身份证（户口薄）,21020************0,女,1954-11-21,医保个账,OB*************23,升级版,398.00 于**,居民身份证（户口薄）,21020************1,女,1982-01-10,医保个账,OB*************24,升级版,198.00 于**,居民身份证（户口薄）,21020************2,男,1954-11-02,医保个账,OB*************25,升级版,398.00 王**,居民身份证（户口薄）,21020************3,女,2022-09-20,医保个账,OB*************26,基础版,59.00
-- Create table create table PHB ( name VARCHAR2(100), idtype VARCHAR2(100), idno VARCHAR2(100), sex VARCHAR2(100), birthday VARCHAR2(100), info1 VARCHAR2(100), info2 VARCHAR2(100), info3 VARCHAR2(100), info4 VARCHAR2(100) )
3.创建控制文件（phb.ctl），指定如何加载数据：
[oracle@test app]$ cat phb.ctl options(skip=1,BINDSIZE=20971520, ROWS=10000, READSIZE=20971520, ERRORS=999999999) load data infile '/app/phb.csv' insert into table "PHB" fields terminated by ',' Optionally enclosed by '\'' (NAME,IDTYPE,IDNO,SEX,BIRTHDAY,INFO1,INFO2,INFO3,INFO4)
4.运行SQL*Loader命令：sqlldr userid=user/pwd@127.0.0.1:1521/test control=phb.ctl
5.查看日志
[oracle@test app]$ cat phb.log
SQL*Loader: Release 11.2.0.4.0 - Production on Fri Dec 6 15:50:47 2024
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Control File: phb.ctl
Data File: /app/phb.csv
Bad File: phb.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 999999999
Bind array: 10000 rows, maximum of 20971520 bytes
Continuation: none specified
Path used: Conventional
Table "PHB", loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
NAME FIRST * , O(') CHARACTER
IDTYPE NEXT * , O(') CHARACTER
IDNO NEXT * , O(') CHARACTER
SEX NEXT * , O(') CHARACTER
BIRTHDAY NEXT * , O(') CHARACTER
INFO1 NEXT * , O(') CHARACTER
INFO2 NEXT * , O(') CHARACTER
INFO3 NEXT * , O(') CHARACTER
INFO4 NEXT * , O(') CHARACTER
value used for ROWS parameter changed from 10000 to 9031
Table "PHB":
536987 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 20969982 bytes(9031 rows)
Read buffer bytes:20971520
Total logical records skipped: 1
Total logical records read: 536987
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Fri Dec 06 15:50:47 2024
Run ended on Fri Dec 06 15:50:48 2024
Elapsed time was: 00:00:01.67
CPU time was: 00:00:01.31
[oracle@test app]$
6.检查数据
SQL> select count(*) from phb; COUNT(*) ---------- 536987
