sqlldr导入Sequence等类型数据

sqlldr导入Sequence等类型数据

目标表SQL：
create table STM_TERMINAL_INFO
(
terminal_id NUMBER(12) not null,
terminal_ip VARCHAR2(200) not null,
storey VARCHAR2(50) not null,
region_id VARCHAR2(50) not null,
ccs_id VARCHAR2(50) not null,
seat_code VARCHAR2(50) not null,
seat_desc VARCHAR2(200),
terminal_type VARCHAR2(50) not null,
manage_code VARCHAR2(1024),
manage_tel VARCHAR2(1024),
fault_cause VARCHAR2(1024),
state NUMBER(1) not null,
create_date DATE not null,
create_code VARCHAR2(20) not null,
op_code VARCHAR2(20) not null,
op_id NUMBER(12) not null,
org_id NUMBER(12) not null,
org_name VARCHAR2(200) not null,
done_code VARCHAR2(30) not null,
done_date DATE not null,
notes VARCHAR2(1024),
is_fault NUMBER(1)
);

create sequence STM_TERMINAL_INFO$SEQ
minvalue 1
maxvalue 999999999999
start with 1181
increment by 1
cache 20;

要导入的数据：
IP_ADDRESS A.CENTER_CODE||A.FLOOR_CODE CITY_CODE CENTER_CODE SEAT_CODE 'TESTLEI' '1' 'M000000' '13939012107' '故障原因' 1 TO_CHAR(SYSDATE,'YYYY-MM-DDHH 'M000000' 'M000000' 0 911 '雷智民' 0 TO_CHAR(SYSDATE,'YYYY-MM-DDHH 'LEIZHIMIN' F
1 10.97.106.11 STM_000003_A_211 STM_000003_A STM_000003_A_2 99511 testlei 1 M000000 13939012107 故障原因 1 2011-01-05 16:24:04 M000000 M000000 0 911 雷智民 0 2011-01-05 16:24:04 leizhimin 0
2 10.97.106.12 STM_000003_A_211 STM_000003_A STM_000003_A_2 99512 testlei 1 M000000 13939012107 故障原因 1 2011-01-05 16:24:04 M000000 M000000 0 911 雷智民 0 2011-01-05 16:24:04 leizhimin 0
3 10.97.106.13 STM_000003_A_211 STM_000003_A STM_000003_A_2 99513 testlei 1 M000000 13939012107 故障原因 1 2011-01-05 16:24:04 M000000 M000000 0 911 雷智民 0 2011-01-05 16:24:04 leizhimin 0
4 10.97.106.14 STM_000003_A_211 STM_000003_A STM_000003_A_2 99514 testlei 1 M000000 13939012107 故障原因 1 2011-01-05 16:24:04 M000000 M000000 0 911 雷智民 0 2011-01-05 16:24:04 leizhimin 0
5 10.97.106.15 STM_000003_A_211 STM_000003_A STM_000003_A_2 99515 testlei 1 M000000 13939012107 故障原因 1 2011-01-05 16:24:04 M000000 M000000 0 911 雷智民 0 2011-01-05 16:24:04 leizhimin 0

导入配置文件：
OPTIONS (skip=1,rows=128)
LOAD DATA
INFILE "STM_TERMINAL_INFO.data"
append
INTO TABLE STM_TERMINAL_INFO -- 要插入记录的表
Fields terminated by " "
trailing nullcols --表的字段没有对应的值时允许为空
(
virtual_column FILLER, --这是一个虚拟字段，用来跳过由 PL/SQL Developer 生成的第一列序号
TERMINAL_IP,
STOREY,
REGION_ID,
CCS_ID,
SEAT_CODE,
SEAT_DESC,
TERMINAL_TYPE,
MANAGE_CODE,
MANAGE_TEL,
FAULT_CAUSE,
STATE,
CREATE_DATE DATE "YYYY-MM-DD HH24:MI:SS",
CREATE_CODE,
OP_CODE,
OP_ID,
ORG_ID,
ORG_NAME,
DONE_CODE,
DONE_DATE DATE "YYYY-MM-DD HH24:MI:SS",
NOTES,
IS_FAULT,
TERMINAL_ID "Stm_Terminal_Info$seq.Nextval"
)

导入操作：
E:\loaddata>dir
驱动器 E 中的卷没有标签。
卷的序列号是 1470-E67B

E:\loaddata 的目录

2011-01-05 16:45 <DIR> .
2011-01-05 16:45 <DIR> ..
2011-01-05 16:32 705 ldrcfg.cfg
2011-01-05 16:45 3,173 ldrcfg.log
2011-01-05 16:27 1,193 STM_TERMINAL_INFO.data
3 个文件 5,071 字节
2 个目录 672,718,848 可用字节


E:\loaddata>sqlldr csm/csm_123@hatest control=ldrcfg.cfg

SQL*Loader: Release 10.2.0.1.0 - Production on 星期三 1月 5 16:45:17 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

达到提交点 - 逻辑记录计数 5

E:\loaddata>

日志文件：

SQL*Loader: Release 10.2.0.1.0 - Production on 星期三 1月 5 16:32:29 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

控制文件: ldrcfg.cfg
数据文件: STM_TERMINAL_INFO.data
错误文件: STM_TERMINAL_INFO.bad
废弃文件: 未作指定

(可废弃所有记录)

要加载的数: ALL
要跳过的数: 1
允许的错误: 50
绑定数组: 128 行, 最大 256000 字节
继续: 未作指定
所用路径: 常规

表 STM_TERMINAL_INFO,已加载从每个逻辑记录
插入选项对此表 APPEND 生效
TRAILING NULLCOLS 选项生效

列名 位置 长度 中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
VIRTUAL_COLUMN FIRST * WHT CHARACTER
(FILLER FIELD)
TERMINAL_IP NEXT * WHT CHARACTER
STOREY NEXT * WHT CHARACTER
REGION_ID NEXT * WHT CHARACTER
CCS_ID NEXT * WHT CHARACTER
SEAT_CODE NEXT * WHT CHARACTER
SEAT_DESC NEXT * WHT CHARACTER
TERMINAL_TYPE NEXT * WHT CHARACTER
MANAGE_CODE NEXT * WHT CHARACTER
MANAGE_TEL NEXT * WHT CHARACTER
FAULT_CAUSE NEXT * WHT CHARACTER
STATE NEXT * WHT CHARACTER
CREATE_DATE NEXT * WHT DATE YYYY-MM-DD HH24:MI:SS
CREATE_CODE NEXT * WHT CHARACTER
OP_CODE NEXT * WHT CHARACTER
OP_ID NEXT * WHT CHARACTER
ORG_ID NEXT * WHT CHARACTER
ORG_NAME NEXT * WHT CHARACTER
DONE_CODE NEXT * WHT CHARACTER
DONE_DATE NEXT * WHT DATE YYYY-MM-DD HH24:MI:SS
NOTES NEXT * WHT CHARACTER
IS_FAULT NEXT * WHT CHARACTER
TERMINAL_ID NEXT * WHT CHARACTER
列的 SQL 串: "Stm_Terminal_Info$seq.Nextval"

ROWS 参数所用的值已从 128 更改为 45

表 STM_TERMINAL_INFO:
5 行 加载成功。
由于数据错误, 0 行 没有加载。
由于所有 WHEN 子句失败, 0 行 没有加载。
由于所有字段都为空的, 0 行 没有加载。


为绑定数组分配的空间: 255420 字节 (45 行)
读取 缓冲区字节数: 1048576

跳过的逻辑记录总数: 1
读取的逻辑记录总数: 5
拒绝的逻辑记录总数: 0
废弃的逻辑记录总数: 0

从 星期三 1月 05 16:32:29 2011 开始运行
在 星期三 1月 05 16:32:29 2011 处运行结束

经过时间为: 00: 00: 00.29
CPU 时间为: 00: 00: 00.04


注意，Sequence要放到最后一个位置。