(1)建立directory路径:
create directory data_dir as 'E:\Test\Desk3';
create directory log_dir as 'E:\Test\Desk4';
grant read on directory data_dir to scott;
grant write on directory log_dir to scott;
(2)建立外部表
create table external_emp
(empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2))
organization external
(type oracle_loader
default directory data_dir --存数据默认路径
access parameters
(
records delimited by newline characterset us7ascii --记录用换行分隔符,字符集为US7ASCII
badfile 'LOG_DIR':'sales.bad' --坏文件放在log_dir定义的目录下,文件名为sales.bad,用作诊断
logfile 'LOG_DIR':'sales.log' --外表日志文件放在log_dir定义目录下
fileds terminated by " " optionally enclosed by '\t' --字段有空格和制表键分隔
)
location('emp_delta.txt') --数据文件名
)
reject limit unlimited;
(3)制作数据文件 set feedback off
set heading off
alter session set nls_date_language='AMERICAN';
spool E:\Test\Desk3\emp_delta.txt
select * from emp;
spool off
(4)最后查询
select * from external_emp;
报错:
第 1 行出现错误:
ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错
ORA-29400: 数据插件错误KUP-00554: error encountered while parsing access paramet
ers
KUP-01005: syntax error: found "minussign": expecting one of: "badfile, byteorde
r, characterset, column, data,
delimited, discardfile, exit, fields, fixed, load, logfile, language, nodiscardf
ile, nobadfile, nologfile, date_cache,
processing, readsize, string, skip, territory, variable"
KUP-01007: at line 1 column 53
ORA-06512: 在 "SYS.ORACLE_LOADER", line 19根据网络上说的把varchar2改成char还是不行,后来怀疑是comm number(7,2)字段有空,把这个字段去掉后还是不行。
另外想问下:
fileds terminated by " " optionally enclosed by '\t' 前面fileds terminated by和后面的optionally enclosed by 有什么区别,
create directory data_dir as 'E:\Test\Desk3';
create directory log_dir as 'E:\Test\Desk4';
grant read on directory data_dir to scott;
grant write on directory log_dir to scott;
(2)建立外部表
create table external_emp
(empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2))
organization external
(type oracle_loader
default directory data_dir --存数据默认路径
access parameters
(
records delimited by newline characterset us7ascii --记录用换行分隔符,字符集为US7ASCII
badfile 'LOG_DIR':'sales.bad' --坏文件放在log_dir定义的目录下,文件名为sales.bad,用作诊断
logfile 'LOG_DIR':'sales.log' --外表日志文件放在log_dir定义目录下
fileds terminated by " " optionally enclosed by '\t' --字段有空格和制表键分隔
)
location('emp_delta.txt') --数据文件名
)
reject limit unlimited;
(3)制作数据文件 set feedback off
set heading off
alter session set nls_date_language='AMERICAN';
spool E:\Test\Desk3\emp_delta.txt
select * from emp;
spool off
(4)最后查询
select * from external_emp;
报错:
第 1 行出现错误:
ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错
ORA-29400: 数据插件错误KUP-00554: error encountered while parsing access paramet
ers
KUP-01005: syntax error: found "minussign": expecting one of: "badfile, byteorde
r, characterset, column, data,
delimited, discardfile, exit, fields, fixed, load, logfile, language, nodiscardf
ile, nobadfile, nologfile, date_cache,
processing, readsize, string, skip, territory, variable"
KUP-01007: at line 1 column 53
ORA-06512: 在 "SYS.ORACLE_LOADER", line 19根据网络上说的把varchar2改成char还是不行,后来怀疑是comm number(7,2)字段有空,把这个字段去掉后还是不行。
另外想问下:
fileds terminated by " " optionally enclosed by '\t' 前面fileds terminated by和后面的optionally enclosed by 有什么区别,
create table external_emp
(empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2))
organization external
(type oracle_loader
default directory data_dir
access parameters
(
records delimited by newline characterset us7ascii
badfile 'LOG_DIR':'sales.bad'
logfile 'LOG_DIR':'sales.log'
fields terminated by " " optionally enclosed by '\t'
)
location('emp_delta.txt')
)
reject limit unlimited; (3)制作数据文件,下面的脚本存储一个文件(不要直接粘贴到sqlplus中),如e:\a.sql
set trims on
set linesize 200
set pagesize 0
set feedback off
set heading off
alter session set nls_date_language='AMERICAN';
spool E:\Test\Desk3\emp_delta.txt
select * from emp;
spool off然后在sqlplus中执行上面的脚本:
sql>@e:\a.sql