准备数据:
[oracle@sgpc146 sqlldr]$ more ext.txt
1|tc
2|tcyang
3|tcy
DROP TABLE SYS.EXT_TABLE_NAME CASCADE CONSTRAINTS;
create table EXT_TABLE_NAME
(
COL_1 NUMBER,
COL_2 VARCHAR2(512)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DIR_SQLLDR
ACCESS Parameters
(
RECORDS DELIMITED BY NEWLINE
badfile 'bad_dev.txt'
LOGFILE 'log_dev.txt'
FIELDS TERMINATED BY '|'
)
LOCATION('ext.txt')
) reject limit unlimited;
--查看数据
SQL> select * from EXT_TABLE_NAME;
COL_1
----------
COL_2
--------------------------------------------------------------------------------
1
tc
2
tcyang
3
tcy
Elapsed: 00:00:00.03
SQL>
如果报错:
ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错
ORA-29400: 数据插件错误KUP-04040: 未找到文件 ext.txt, 在DIR_SQLLDR中.
解决:
[oracle@sgpc146 ~]$ cd ..
[oracle@sgpc146 home]$ ll
total 12
drwx------ 4 grid oinstall 4096 Sep 6 2012 grid
drwx------ 8 oracle oinstall 4096 Sep 22 23:33 oracle
[oracle@sgpc146 home]$ chmod 755 /home/oracle/
[oracle@sgpc146 home]$ ll
total 12
drwx------ 4 grid oinstall 4096 Sep 6 2012 grid
drwxr-xr-x 8 oracle oinstall 4096 Sep 22 23:33 oracle
[oracle@sgpc146 home]$