External table reference current file name

From: Sidney Chen <huanshengchen_at_gmail.com>
Date: Sat, 28 Jun 2014 00:30:18 +0800
Message-ID: <CAM_ddu84tybxBOGuvqAYbKKaia6UeZwpaMvwcqC4L_aL8dcJ9Q_at_mail.gmail.com>

hi List,

I'm on a benchmark and need to load 8 thousands files, there is a requirement that need your advice.

I need to assign the file name to the FILE_NO col, such as 0001 for all the records in the 0001.csv, and 0002 for all the rows in 0002.csv, and 8000 for the rows in 8000.csv. I want to refence the current file name for the FILE_NO column when enable parallel execution to load the data. Otherwise, I need to apply DIY parallel data loading. Thanks.

CREATE TABLE DATA_EXT
(
create_dt Varchar2(15) ,
FILE_NO number
)

organization external
(type oracle_loader
default directory EXT_DIR
access parameters (

    records delimited by newline skip=1
    badfile 'ERROR_DIR':'FIRST_LOAD.bad'     logfile 'ERROR_DIR':'FIRST_LOAD.log'     fields terminated by ","
    LRTRIM missing field values are null
)

location (

 '0001.csv'
,'0002.csv'
,'0003.csv'

, '0004.csv'
...
, '7999.csv'
, '8000.csv')
)

parallel
reject limit 1000;

-- 
Regards
Sidney Chen

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 27 2014 - 18:30:18 CEST