2015年Oracle认证考试辅导：Oracle外部表的创建
发布时间：2010/3/13 11:46:00 来源：城市学习网 编辑：MOON
记录一下自己创建外部表的过程，因为中间出了一些小错误。
SQL create directory upload_dir as ‘/storagepool/upload‘;
Directory created.
SQL create table analog_tmp_entrypage_ext_1(
2 PROFILE_ID NUMBER(22)，
3 REPORT_TIME NUMBER(22)，
4 SESSION_ID NUMBER(22)，
5 URL VARCHAR2(2048)
6 )
7 organization external
8 (type oracle_loader
9 default directory upload_dir
10 access parameters
11 (records delimited by newline
12 fields terminated by X‘05‘
13 missing field values are null
14 (PROFILE_ID， REPORT_TIME， SESSION_ID， URL)
15 )
16 location(‘weblog.analog_tmp_entrypage_0.dat‘)
17 );
Table created.
这里出错的原因是：oracle对路径‘/storagepool/upload‘没有权限，无法在该路径下创建log文件和bad文件。
SQL select * from analog_tmp_entrypage_ext_1 where rownum 2;
select * from analog_tmp_entrypage_ext_1 where rownum 2
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file ANALOG_TMP_ENTRYPAGE_EXT_1_12190.log
OS error Permission denied
ORA-06512: at "SYS.ORACLE_LOADER"， line 19
SQL create directory upload_log_dir as ‘/tmp/upload_log‘;
Directory created.
SQL drop table analog_tmp_entrypage_ext_1 purge;
Table dropped.
SQL create table analog_tmp_entrypage_ext_1(
2 PROFILE_ID NUMBER(22)，
3 REPORT_TIME NUMBER(22)，
4 SESSION_ID NUMBER(22)，
5 URL VARCHAR2(2048)
6 )
7 organization external
8 (type oracle_loader
9 default directory upload_dir
10 access parameters
11 (records delimited by newline
12 badfile upload_log_dir:‘analog_tmp_entrypage_ext_1%A_%P.bad‘
13 logfile upload_log_dir:‘analog_tmp_entrypage_ext_1%A_%P.log‘
14 fields terminated by X‘05‘
15 missing field values are null
16 (PROFILE_ID， REPORT_TIME， SESSION_ID， URL)
17 )
18 location(‘weblog.analog_tmp_entrypage_0.dat‘)
19 );
Table created.
SQL select * from analog_tmp_entrypage_ext_1 where rownum 2;
select * from analog_tmp_entrypage_ext_1 where rownum 2
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
