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!

How to read any file using external tables.

Graham BanksNov 3 2008 — edited Nov 9 2008
Hi folks,
I have written an application that reads a series of csv files using external tables which works fine as long as I specify each file name in the directory i.e.......

CREATE TABLE gb_test
(file_name varchar2(10),
rec_date date
rec_name VARCHAR2(20),
rec_age number,
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY GB_TEST
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
)
LOCATION ('data1.csv','data2.csv','data3.csv','data4.csv')
)
PARALLEL 5
REJECT LIMIT 20000;

However I have discovered that I may not know the name of the files to be processed prior to the program being run so just want to read any file regardless of it's name (although it will always be a .csv file).

Is there a way to ensure that you don't need to specify the files to be read in the LOCATION part of the syntax.

Thanks in advance.
Graham.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 7 2008
Added on Nov 3 2008
8 comments
598 views