Oracle读取外部表注意事项

Oracle可以通过创建外部表以只读的方式来查询文件数据的内容，这对于文件数据的分析非常有用。

示例代码：

CREATE OR REPLACE DIRECTORY admin_dat_dir AS 'D:\example\data';  
CREATE OR REPLACE DIRECTORY admin_log_dir AS 'D:\example\log';   
CREATE OR REPLACE DIRECTORY admin_bad_dir AS 'D:\example\bad';  

CREATE TABLE ext_emp
	   (employee_id       NUMBER(4),               
		emp_name          VARCHAR2(20),           
		job_id            VARCHAR2(30),            
		manager_id        NUMBER(4),               
		hire_date         DATE,                 
		salary            NUMBER(8,2),            
		commission_pct    NUMBER(2,2),             
		department_id     NUMBER(4),              
		email             VARCHAR2(25)             
	   ) 
     ORGANIZATION EXTERNAL                         
     ( 
       TYPE ORACLE_LOADER                          
       DEFAULT DIRECTORY admin_dat_dir             
       ACCESS PARAMETERS                          
       ( 
         records delimited by newline                
         characterset ZHS16GBK                       
         badfile admin_bad_dir:'empxt%a_%p.bad'     
         logfile admin_log_dir:'empxt%a_%p.log'      
         fields terminated by ','                    
         missing field values are null            
         ( employee_id, emp_name, job_id, manager_id, 
           hire_date char date_format date mask "yyyy-mm-dd", 
           salary, commission_pct, department_id, email 
         ) 
       ) 
       LOCATION ('empxt1.csv', 'empxt2.csv')        
     ) 
     REJECT LIMIT UNLIMITED; 

注意事项：

1. 导入.sql文件时，不能在行头出现注释（--），否则会发生“ORA-29400:数据插件错误KUP-00554..."错误

2. 路径最好用英文，且不要包括负号（-），否则会发生解析错误（ORA-29913: error in executing ODCIEXTTABLEOPEN callout、KUP-00554: error encountered while parsing access parameters）

3. 建好目录及数据表后，不要忘记将数据文件放到data文件夹中（文件名在LOCATION()中指定）

做好以上工作后，使用select * from ext_emp;命令查看所有外部数据，如下所示：

sys_pd@ORCL> select * from ext_emp order by salary;

EMPLOYEE_ID EMP_NAME   JOB_ID     MANAGER_ID HIRE_DATE          SALARY COMMISSION_PCT DEPARTMENT_ID EMAIL
----------- ---------- ---------- ---------- -------------- ---------- -------------- ------------- ----------
        360 Json       CLERK             121 12-9月 -08           3000              0            50 jjanus
        404 Abby       AC_ACCOUNT        205 12-9月 -08           5000              0           110 jrichard
        362 Marry      AD_REP            200 12-10月-10           5500              0            10 bstarr
        401 Benny      HR_REP            203 12-9月 -08           7000              0            40 jcromwel
        361 Rose       SALES             145 05-2月 -09           8000             .1            80 mjasper
        402 Sunny      IT_PROG           103 12-9月 -08           9000             .2            60 aapplega
        363 Henny      ACCOUNT           145 12-11月-08           9000            .15            80 aalda
        403 Json       AD_VP             100 12-9月 -08          27000             .3            90 ccousins

已选择8行。

sys_pd@ORCL> spool out

也就是说，不用肯吃肯吃把数据导入数据表，再进行数据分析了，方便吧！