Oracle sqlldr和外部表

已于 2024-03-08 10:23:07 修改
阅读量218 收藏 1
点赞数
分类专栏： Oracle 文章标签： oracle 数据库
于 2023-03-30 15:54:24 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41450814/article/details/129858736
版权

详情查阅:SQL*Loader (oracle.com)

写了三个例子，看看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
INFILE '/home/oracle/SQL/t1.csv'
TRUNCATE

INTO TABLE TARGET_T1
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(id,name)

解释:

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, 或者别的, 反正效果实现了就行。

牧童童童
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle sqlldr 大批量数据导入工具
07-25
Oracle sqlldr 大批量数据导入工具使用实例，通过Oracle 自带的sqlldr 工具可快速导入大批量的数据至Oracle中。 工具使用前提条件： 1.安装的Oracle Client 不能为精简版，要不然就找不到sqlldr.exe 2.将Oracle的安装目录下的/BIN/sqlldr.exe加入系统环境变量 3.执行的实例，请参考压缩包中的remark.txt 说明文件
sqlldr用法
reiskie的专栏
05-16 3618
sqlldr用法SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据仓库选择使用的加载方法，因为它提供了最快速的途径（DIRECT，PARALLEL）。现在，我们抛开其理论不谈，用实例来使您快速掌握SQL*LOADER的使用方法。　　首先，我们认识一下SQL*LOADER。　　在NT下，SQL*LOADER的命令
Oracle sqlldr
松门一枝花
02-24 3303
一、sqlldr的概述 sqlldr是oracle自带的一个工具，用于导入文本文件的内容到oracle数据库中。 该工具在：\oracle\app\oracle\product\11.2.0\server\bin\sqlldr.exe  二、使用 D:\oracle\app\oracle\product\11.2.0\server\bin\sqlldr.exe  userid=use
oracle-sqlldr
weixin_42682872的博客
12-30 1366
转载：原文链接：https://blog.csdn.net/a545812327/article/details/102666185。skip_index_maintenance FALSE 没有维护索引, 将受到影响的索引标记为无用。skip_unusable_indexes FALSE 不允许/允许使用无用的索引或索引分区。rows 常规：64 默认路径：全部 常规路径绑定数组中或直接路径保存数据间的行数。load 全部 要加载的逻辑记录的数目。
oracle sqlldr（oracle自带的导入数据的小工具）
脑子进水养啥鱼？的博客
12-24 685
[oracle@dj ~]$ vi shishi.ctl LOAD DATA INFILE * INTO TABLE SHISHI FIELDS TERMINATED BY "," (ID,NAME) BEGINDATA 1,XIAOCHU 2,XIAOMA 3,XIAOJIAO 4,XIAOSONG ...
Oracle中的sqlldr用法.doc
10-22
Oracle中的sqlldr用法详解 Oracle中的sqlldr工具是用来将数据从外部文件加载到Oracle数据库中...参数external_table指定了是否使用外部表来加载数据，包括不使用外部表、生成创建外部表的sql和处理数据的sql两种模式。
Oracle数据库外部表.doc
06-03
### Oracle数据库外部表详解 #### 一、外部表概述 **外部表（External Tables）**是Oracle数据库中一种特殊的数据存储方式，它允许用户通过普通的SQL查询操作来访问存储在数据库之外的数据文件中的数据。这种设计...
Oracle外部表特性深入浅出
最新发布
03-05
### Oracle外部表特性深入浅出 #### 一、外部表概述 外部表是Oracle自9i版本后引入的一个新特性，它不同于传统意义上的数据库表，实际上...理解并掌握外部表的特性和创建方法对于充分利用Oracle的强大功能至关重要。
oracle_Sqlldr的使用
05-15
Oracle SQL*Loader是Oracle数据库提供的一个用于将外部数据加载到Oracle表中的工具。它是通过读取一个控制文件，然后加载指定的数据文件到数据库中。SQL*Loader支持多种数据加载方式，可以处理不同格式的数据文件，...
C# 通过oracle sqlldr 将TXT批量导入oracle
10-06
C# 通过oracle sqlldr 将TXT批量导入oracle 一个文件夹里有大量的TXT文件,以"~"区分各字段. 这个程序能批量将此文件夹里的所有的文件导入oralce 并按日期把已导入的TXT文件复制另一个文件夹
Oracle中的sqlldr
learning_oracle的博客
06-25 500
Oracle--sqlldr用法 sqlldr VGOPDW/omzleSYC#565@TMDA CONTROL=63227_sqlldr.CTL log=63227_sqlldr.log SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据仓库选择使用的加载方法， 因为它提供了最快速的途径（DIRECT，PARA
Oracle sqlldr命令
ooooooooooooooxiaosu的博客
07-22 1415
sqlldr 简介 　SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件（数据）迁移到ORACLE数据库中。SQL*LOADER是大型数据仓库选择使用的加载方法，因为它提供了最快速的途径（DIRECT，PARALLEL）。 语法和参数 语法： SQLLDR keyword=value [,keyword=value,...]; oracle sqlldr导入文件只需要两步 1. 写CTL 控制文件 2. 在cmd 命令窗口中写入导入语句 第一步：写...
oracle sqlldr详解,sqlldr详解
weixin_39977547的博客
04-13 3536
Oracle 的SQL*LOADER可以将外部数据加载到数据库表中。下面是SQL*LOADER的基本特点：1)能装入不同数据类型文件及多个数据文件的数据2)可装入固定格式，自由定界以及可度长格式的数据3)可以装入二进制，压缩十进制数据4)一次可对多个表装入数据5)连接多个物理记录装到一个记录中6)对一单记录分解再装入到表中7)可以用 数对制定列生成唯一的KEY8)可对磁盘或 磁带数据文件装入制表中...
Oracle-工具之SQLLDR
UntifA的博客
08-25 4017
Oracle工具之SQLLDR
oracle sqlldr命令6,oracle sqlldr命令
weixin_31226805的博客
04-14 558
sqlldr是cmd下的命令，用来将文本格式数据导入到数据库中。 命令格式： sqlldr username/password@sid control=*.ctl *.ctl格式为： load infile "d://test.txt" 外部数据文件 infile "d://test1.txt" 可指定多个数据文件 append into table test 向表中追加数据 f...
Oracle sqlldr命令和ctl控制文件
热门推荐
D2cOneluo的博客
10-21 1万+
oracle sqldrl命令与以及ctl文件 具体操作如下： 第一步：先编辑好数据控制文件 xx.ctl，如lee.ctl options(skip=1) --跳过第一行（看实际情况） load data CHARACTERSET ZHS16GBK --转码 truncate --将表数据清空插入数据，此外还有insert、replace、append方式 into table lee ...
oracle sqlldr命令安装
weixin_46764753的博客
01-19 780
19版本可向下兼容所有oracle版本数据库输入y回车输入y回车执行：sqlldr（在sqlldr命令文件路径下。
Oracle--sqlldr用法
萧逸闲侃
01-10 2083
SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据仓库选择使用的加载方法，因为它提供了最快速的途径（DIRECT，PARALLEL）。现在，我们抛开其理论不谈，用实例来使您快速掌握SQL*LOADER的使用方法。<br />　　首先，我们认识一下SQL*LOADER。<br />在NT下，SQL*LOADER的命令为SQLLDR，在UNIX下一般为sqlldr/sqlload。<br />　　如执行：d:/oracle>sqll
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值