写了三个例子,看看sqlldr基本的用法。
例一
写了一个t1.csv文件, 内容:
有五条数据: 101,TOM 102,JERRY 103,BOB 104,MICKEY 105,哪吒 |
开始导入:
先在数据库里创建一张表TARGET_T1: CREATE TABLE TARGET_T1 (ID NUMBER(4),NAME VARCHAR2(20)); |
写了一个load_t1.ctl文件: load data INTO TABLE TARGET_T1 |
解释: badfile和discardfile是可以省略的 INFILE 参数指定要导入的数据在哪个文件里. TRUNCATE 顾名思义, 清空再导入(对于非空表)。 FIELDS TERMINATED BY ',' 表明这个文件是逗号分隔的 TRAILING NULLCOLS 处理空值 |
导入: sqlldr scott/pwd control=load_t1.ctl成功导入。 看看数据是不是正确的: |
例二 , 日期格式的导入:
t2.csv文件, 内容:
101,TOM,2001-01-01,2003-09-11 00:46:12 102,JERRY,2001-02-02,2003-10-12 02:20:32 103,BOB,2001-03-04,2003-11-14 02:46:15 104,MICKEY,2001-05-06,2004-01-16 23:15:56 105,哪吒,2001-07-07,2004-03-17 13:32:00 在上一个例子的基础上加了两个日期列,而且格式还不一样, 一个是'YYYY-MM-DD', 另一个是'YYYY-MM-DD HH24:MI:SS' |
开始导入
先在数据库里创建一张表TARGET_T2: CREATE TABLE TARGET_T2 (ID NUMBER(4),NAME VARCHAR2(20),DATE1 DATE,DATE2 DATE); |
写了一个load_t2.ctl文件: load data INFILE '/home/oracle/SQL/t2.csv' BADFILE '/home/oracle/SQL/t2.bad' DISCARDFILE '/home/oracle/SQL/t2.dsc' TRUNCATE INTO TABLE TARGET_T2 FIELDS TERMINATED BY ',' TRAILING NULLCOLS (id,name "initcap(:name)", date1 DATE"YYYY-MM-DD", date2 DATE"YYYY-MM-DD HH24:MI:SS") |
解释: 在导入日期格式时要告诉sqlldr你这个日期是什么格式的. 当每个日期列的格式都不同时应当"列级"声明格式。 当只有一个日期列或所有日期列的格式都相同时,适合"表级"声明, 比如例三。 !这里我对name列做了一个initcap的转换, 有点狗拿耗子的感觉。 |
导入: sqlldr scott/pwd control=load_t2.ctl |
看看数据是不是正确的: 两个日期列准确无误地导进来了。 而且, name列是首字母大写了。 |
例三 , preprocessor参数
sqlldr不认PREPROCESSOR参数, 不理解为啥。
看看数据:
第一个列是数字类型, 第二个列是字符串 第三个列是日期类型共5000条数据 |
压缩一下:
zip t3.zip t3.csv 小了很多 |
如何导入这个压缩包里的内容呢?
先在数据库创建一张表:
CREATE TABLE TARGET_T3 (ID NUMBER(6),NAME VARCHAR2(7),BIRTHDATE DATE); |
写一个shell脚本:
#!/bin/bash /usr/bin/unzip -c $1记得授权: chmod 700 uncompress.sh |
写个load_t3.ctl:
load data INFILE '/home/oracle/SQL/t3.zip' BADFILE '/home/oracle/SQL/t3.bad' DISCARDFILE '/home/oracle/SQL/t3.dsc' -- PREPROCESSOR 'uncompress.sh' TRUNCATE INTO TABLE TARGET_T3 FIELDS TERMINATED BY ',' DATE FORMAT "YYYY-MM-DD" TRAILING NULLCOLS (id, name, birthdate DATE) |
!!!先注释PREPROCESSOR一行。 |
external_table=generate_only
执行一条命令: sqlldr scott/pwd control=load_t3.ctl external_table=generate_only |
cat load_t3.log
查看生成的log文件, 忽略浅灰色部分: SQL*Loader: Release 19.0.0.0.0 - Production on Thu Mar 30 13:51:19 2023 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Control File: load_t3.ctl Data File: /home/oracle/SQL/t3.csv Bad File: /home/oracle/SQL/t3.bad Discard File: /home/oracle/SQL/t3.dsc (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Continuation: none specified Path used: External Table Table TARGET_T3, loaded from every logical record. Insert option in effect for this table: TRUNCATE TRAILING NULLCOLS option in effect Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- ID FIRST * , CHARACTER NAME NEXT * , CHARACTER BIRTHDATE NEXT * , DATE ""YYYY-MM-DD"" CREATE DIRECTORY statements needed for files ------------------------------------------------------------------------ CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00002 AS '/home/oracle/SQL' CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/SQL/' CREATE TABLE statement for external table: ------------------------------------------------------------------------ CREATE TABLE "SYS_SQLLDR_X_EXT_TARGET_T3" ( "ID" NUMBER(6), "NAME" VARCHAR2(7), "BIRTHDATE" DATE ) ORGANIZATION external ( TYPE oracle_loader DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8 BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'t3.bad' DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'t3.dsc' LOGFILE 'SYS_SQLLDR_XT_TMPDIR_00002':'load_t3.log_xt' READSIZE 1048576 FIELDS TERMINATED BY "," LDRTRIM DATE_FORMAT DATE MASK "YYYY-MM-DD" MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS ( "ID" CHAR(255) TERMINATED BY ",", "NAME" CHAR(255) TERMINATED BY ",", "BIRTHDATE" CHAR(255) TERMINATED BY "," ) ) location ( 't3.zip' ) )REJECT LIMIT UNLIMITED INSERT statements used to load internal tables: ------------------------------------------------------------------------ INSERT /*+ append */ INTO TARGET_T3 ( "ID", "NAME", "BIRTHDATE" ) SELECT "ID", "NAME", "BIRTHDATE" FROM "SYS_SQLLDR_X_EXT_TARGET_T3 statements to cleanup objects created by previous statements: ------------------------------------------------------------------------ DROP TABLE "SYS_SQLLDR_X_EXT_TARGET_T3" DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00002 DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 Run began on Thu Mar 30 13:51:19 2023 Run ended on Thu Mar 30 13:51:19 2023 Elapsed time was: 00:00:00.06 CPU time was: 00:00:00.01 |
编辑create table语句:
-- 复制过来, 高亮部分需要修改一下: CREATE TABLE TARGET_T3 ( "ID" NUMBER(6), "NAME" VARCHAR2(7), "BIRTHDATE" DATE ) ORGANIZATION external ( TYPE oracle_loader DEFAULT DIRECTORY DIR_SQLLDR ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8 PREPROCESSOR 'DIR_SQLLDR':'uncompress.sh' BADFILE 'DIR_SQLLDR':'t3.bad' DISCARDFILE 'DIR_SQLLDR':'t3.dsc' LOGFILE 'DIR_SQLLDR':'load_t3.log_xt' READSIZE 1048576 FIELDS TERMINATED BY "," LDRTRIM DATE_FORMAT DATE MASK "YYYY-MM-DD" MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS ( "ID" CHAR(255) TERMINATED BY ",", "NAME" CHAR(255) TERMINATED BY ",", "BIRTHDATE" CHAR(255) TERMINATED BY "," ) ) location ( 't3.zip' ) )REJECT LIMIT UNLIMITED; |
把表名改成了:TARGET_T3 加了一行:PREPROCESSOR 把目录名改成了:DIR_SQLLDR 别忘了最后加一个分号。 先不要执行 |
在数据库里创建一个目录
sys用户创建一个目录DIR_SQLLDR, 并授权:
create directory DIR_SQLLDR as '/home/oracle/SQL'; grant read,write,execute on directory DIR_SQLLDR to public; |
修改ctl文件里的内容:
把PREPROCESSOR的注释去掉:
load data INFILE '/home/oracle/SQL/t3.csv' BADFILE '/home/oracle/SQL/t3.bad' DISCARDFILE '/home/oracle/SQL/t3.dsc' PREPROCESSOR execdir:'uncompress.sh' TRUNCATE INTO TABLE TARGET_T3 FIELDS TERMINATED BY ',' DATE FORMAT "YYYY-MM-DD" TRAILING NULLCOLS (id, name , birthdate DATE) |
删除表:
到scott用户, 删除之前创建的TARGET_T3表:
drop table target_t3 purge; |
建表:
scott用户执行上面那段很长的建表语句: CREATE TABLE TARGET_T3 ( "ID" NUMBER(6), "NAME" VARCHAR2(7), "BIRTHDATE" DATE ) ORGANIZATION external (TYPE oracle_loader DEFAULT DIRECTORY DIR_SQLLDR ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8 PREPROCESSOR 'DIR_SQLLDR':'uncompress.sh' BADFILE 'DIR_SQLLDR':'t3.bad' DISCARDFILE 'DIR_SQLLDR':'t3.dsc' LOGFILE 'DIR_SQLLDR':'load_t3.log_xt' READSIZE 1048576 FIELDS TERMINATED BY "," LDRTRIM DATE_FORMAT DATE MASK "YYYY-MM-DD" MISSING FIELD VALUES ARE NULL REJECT ROWS WITH ALL NULL FIELDS ( "ID" CHAR(255) TERMINATED BY ",", "NAME" CHAR(255) TERMINATED BY ",", "BIRTHDATE" CHAR(255) TERMINATED BY "," ) ) location ('t3.zip') ) REJECT LIMIT UNLIMITED; |
验证:
SELECT COUNT(1) FROM TARGET_T3; SELECT * FROM TARGET_T3 WHERE ROWNUM<=10; |
查出来啦!!!
嗯↗嗯~ 赛高尼high铁鸭子哒~
注意事项:
一定要仔细检查目录名、参数名、参数值、文件、权限等等等等。
尤其权限, 大部分错误都是权限不足引起的。
外部表这一块儿的报错, 报错信息不太完善, 有可能是A错误但报的是B报错。
shell脚本里的gunzip -c可以换成zcat, 或者别的, 反正效果实现了就行。