Oracleデータ内の外部テキスト・ファイルの入庫(Oracle外部テーブルで使用)

1.       
sqlplus /nolog
conn sys/ticket as sysdba
    
create directory etl_data_dir as 'D:\app\Administrator\admin\ticket\ETL';
create directory etl_log_dir as 'D:\app\Administrator\admin\ticket\ETL';
  
grant write on etl_data_dir to scott;
grant read on etl_data_dir to scott;
      
select * from dba_directories;
  SCOTT         
select * from dba_tab_priv 
where table_name in ('ETL_DATA_DIR','ETL_LOG_DIR');

2.     
conn scott/tiger

     
create table sales_delta
(
 prod_id number(6),
 cust_ID number,
 time_id date,
 channel_ID char(1),
 promo_ID number(6),
 quantity_sold number(3),
 amount_sold number(10,2))
 organization external
 (
   type oracle_loader
    default directory ETL_DATA_DIR
	access parameters
	(
	  records delimited by newline characterset US7ASCII
	  badfile 'ETL_LOG_DIR' :'sales.bad'
	  logfile 'ETL_LOG_DIR' :'sales.log'
	  fields terminated by " " optionally enclosed by '\t'
	)
	LOCation ('sales_delta.txt')
 )
reject limit unlimited;

      
select table_name ,tablespace_name from user_tables;

        
select table_name,default_directory_owner,default_directory_name from user_external_tables;

3.        
conn jinfeng/ticket
@sales_detail.sql
         sql  sales_detail.sql,    
set line 120;
set pagesize 49990
set heading off
set feedback off
alter session set nls_date_language='AMERICAN';
spool D:\app\Administrator\admin\ticket\dpdump\sales_delta.txt
select * from  sales where rownum<49990;
spool off

 sales_delta.txt             ETL_DATA_DIR 。

4.        
conn scott/tigger 
select count(1) from sales_delta;