SQL*LOADER的应用和场景

最新推荐文章于 2022-12-31 21:04:43 发布

黄驼君

最新推荐文章于 2022-12-31 21:04:43 发布

阅读量322

点赞数

本文链接：https://blog.youkuaiyun.com/vivasugar/article/details/86674526

版权

本文详细介绍如何使用Oracle SQL*Loader工具将数据从文本文件加载到现有表中，包括创建表、数据文件、控制文件，以及通过SQL*Loader命令进行数据加载的过程。同时，文章还介绍了如何创建外部表并通过SQL*Loader生成控制文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Load data into an exsit table

1. login as scott

2. create table sql_loader_test
SQL> create table sql_loader_test
(first varchar2(10),
last varchar2(10));

Table created.

3. create data file sql_loader_test.txt in folder /home/scott
$ cat sql_loader_test.txt
John,Watson
Roopesh,Ramklass
Sam,Alapati

4. create control file sql_loader_test.ctl in folder /home/scott
$ cat sql_loader_test.ctl
load data
infile ‘sql_loader_test.txt’
badfile ‘sql_loader_test.bad’
truncate
into table sql_loader_test
fields terminated by ‘,’
trailing nullcols
(first,last)

5. using command sqlldr to load data from file
$ sqlldr ‘scott/tiger’ control=‘sql_loader_test.ctl’

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Jan 28 11:42:09 2019

Path used: Conventional
Commit point reached - logical record count 4

Table SQL_LOADER_TEST:
3 Rows successfully loaded.

Check the log file:
sql_loader_test.log
for more information about the load.

6. checking the table sql_loader_test
SQL> select * from sql_loader_test;

FIRST	LAST
John	Watson
Roopesh	Ramklass
Sam	Alapati

creating one external table via sqlldr

7. create the external table file for the exsiting one
$ sqlldr scott/tiger control=‘sql_loader_test.ctl’ external_table=generate_only
SQL*Loader: Release 12.1.0.2.0 - Production on Mon Jan 28 13:52:52 2019

Path used: External Table

8. create specific pysical data file path from oracle side (grant access with DBA first)

SQL> grant create any directory to scott;
SQL> create directory system_dmp as ‘/home/scott’;

9. modify the auto-generation file with the property paramters
$ cat sql_loader_test.log
SQL> CREATE TABLE sql_loader_test_ext
(
“FIRST” VARCHAR2(10),
“LAST” VARCHAR2(10)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYSTEM_DMP
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE ‘SYSTEM_DMP’:'sql_loader_test.bad’
LOGFILE 'sql_loader_test.log’
READSIZE 1048576
FIELDS TERMINATED BY “,” LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
“FIRST” CHAR(255)
TERMINATED BY “,”,
“LAST” CHAR(255)
TERMINATED BY “,”
)
)
location
(
‘sql_loader_test.txt’
)
)REJECT LIMIT UNLIMITED;