Oracle allows the database to access a flat file as a table. You can have a flat file with a delimiter, it can be accessed from Oracle. This is one of the best methods to load Data into Oracle database. In this excercise we will create an external table from a flat file and then make into a permanent Oracle table with a simple CTAS.

Name of Flat File with Comma Delimiter = CSTF.TXT

eg:

000010,1111000000000,OA,JOHN DOE
000011,111100000001,OA,MARY ANN

— Create Directory where the flat file is copied —

create or replace directory ext_dir as ‘F:\data’;

grant read, write on directory ext_dir to public;

— Create Table from the Flat File —

create table ext_table_csv (
basic_nbr varchar(10),
account_nbr Varchar2(20),
account_type Varchar2(20),
cust_name Varchar2(20)
)
organization external (
type oracle_loader
default directory ext_dir
access parameters (
records delimited by newline
badfile ext_dir:’as400.bad’
logfile ext_dir:’as400.log’
fields terminated by ‘,’
missing field values are null
)
location (‘CSTF.TXT’)
)
reject limit unlimited;

— Do a Select to Check —

select * from ext_table_csv;

— Create Internal Table from External table —

create table internal_table as select * from ext_table_csv;

………….And your done importing data from the flat file (text file) into an Oracle database without going through the hassles of creating CTL files from SQL*LOADER etc. I use this method for loading millions of records in a table on a daily basis. And trust me it is a breeze with this method. You can use CSV or DAT files also. Only thing is the delimiter should be consistent for the records to be imported.