Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

External Table which can handle appending multiple csv files dynamic

634196Aug 6 2008 — edited Aug 6 2008

I need an external table which can handle appending multiple csv files' values.
But the problem I am having is : the number of csv files are not fixed.
I can have between 2 to 6-7 files with the suffix as current_date. Lets say it will be like my_file1_aug_08_1.csv, my_file1_aug_08_2.csv, my_file1_aug_08_3.csv etc. and so on.

I can do it by following as hardcoding if I know the number of files, but unfortunately the number is not fixed and need to something dynamically to inject with a wildcard search of file pattern.
:

CREATE TABLE my_et_tbl
( 
  my_field1 varchar2(4000),
  my_field2 varchar2(4000)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY my_et_dir
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED BY NEWLINE
	   FIELDS TERMINATED BY ','
	   MISSING FIELD VALUES ARE NULL  )
     LOCATION (UTL_DIR:'my_file2_5_aug_08.csv','my_file2_5_aug_08.csv')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

Please advice me with your ideas. thanks.

Joshua..

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 3 2008
Added on Aug 6 2008
4 comments
4,148 views