前面的博客實驗做了sqlldr load，那個是導入外部的excel（txt）到oracle數據庫，如果不需要導入到oracle，僅僅是訪問（當做外部表），可以使用oracle_loader。

1，創(chuàng)建目錄directory sys下

SYS@ncbeta>create or replace directory su as 'd:\tbt'; --su是目錄名

目錄已創(chuàng)建。

SYS@ncbeta>select owner,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

?上述命令可以看到所有的directory

SYS@ncbeta>drop directory su; --刪除directory

目錄已刪除。

2，目錄下有這么一個txt，學生成績表，這里最后一行詹國坤math沒有成績

3，寫sql? 發(fā)現了terminated錯誤，可是為什么還給建表了

16：44 更新

確定了，去除后面的--備注就ok

create table ex_stu (name varchar2(10),subject varchar2(10),score number(3)) organization external ( type oracle_loader default directory suaccess parameters ( records delimited by newline logfile su:'ex_sut_log.log' --這里su:可以不用輸入 fields terninated by ',' missing field values are null --詹國坤沒有成績null處理 (name,subject,score)) location ('student.txt'))? --這里兩個)) parallel reject limit unlimited ;

3，表創(chuàng)建了，但是select報錯，并且logfile沒有生成

使用查詢外部表SYS@ncbeta>select owner,table_name,location, DIRECTORY_NAME from dba_external_locations;

?在其他用戶下創(chuàng)建也不行

SYS@ncbeta>grant read,write on directory su to ncv5;

這種用法是在ncv5下建表用的

create table ncv5.ex_stu

授權成功。

16：06 更新

?讓倪瑞同學測試，她ok了，按照她的說法，她給我排版了

create table ex_stu (name varchar2(20),subject varchar2(10),score number(3)) organization external (type oracle_loaderdefault directory suaccess parameters(records delimited by newlinelogfile 'ex_sut.log' fields terminated by ','missing field values are null (name,subject,score)) location ('student.txt')); 不過這次還好，終于產生了log文件
?LOG file opened at 01/07/13 15:53:49

Field Definitions for table EX_STU
? Record format DELIMITED BY NEWLINE
? Data in file has same endianness as the platform
? Rows with all null fields are accepted

? Fields in Data Source:

??? NAME??????????????????????????? CHAR (255)
????? Terminated by ","
????? Trim whitespace same as SQL Loader
??? SUBJECT???????????????????????? CHAR (255)
????? Terminated by ","
????? Trim whitespace same as SQL Loader
??? SCORE?????????????????????????? CHAR (255)
????? Terminated by ","
????? Trim whitespace same as SQL Loader
error processing column NAME in row 5 for datafile d:\xs\student.txt
ORA-12899: value too large for column NAME (actual: 11, maximum: 10) 原來是zhangyuntao占了11個字符，varchar2(10)少了 改成20就行了

?

轉載于:https://www.cnblogs.com/sumsen/archive/2013/01/07/2848898.html

總結

以上是生活随笔為你收集整理的ORACLE_LOADER加载外部数据的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。