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.