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.
