1) Create a table
create table employee
2) Create a control file
into table employee
fields terminated by "||"
( id, name, dept, salary )
3) Create a data file with piping separated fields as employee.txt
4) Run sqlldr
sqlldr hr/hr@orcl control=D:\new.ctl
There is some advice on what looks like the same problem here,
Message was edited by: JohnWatson
corrected the link
How can I specify the filename as a pattern?
As because I will have a file arriving daily and needs to be loaded based on the pattern.
Pattern will be like : FilenameMMDDYYYY.dat
You don't specify the filename as a pattern. You create a SIMPLE procedure that uses ALTER TABLE to modify the filename.
ALTER TABLE myExternalTable (LOCATION ('myNewFileName10292013.dat');
INSERT INTO myRealTable SELECT COL1, COL2 FROM myExternalTable;
See the DBA Guide for the details.
Allows data sources to be changed without dropping and re-creating the external table metadata
ALTER TABLE admin_ext_employees LOCATION ('empxt3.txt', 'empxt4.txt');