Easily Load Data from a Text File to Oracle

oracle database

So, this is our sample data in text format which we want to load into Oracle. We’ll use SQL Loader utility of Oracle which is a super fast way of loading data into Oracle database.

[ninja_tables id=”22″]

Just remember that Oracle does not automatically create a table for you from a text file. Therefore, we need to create our table manually in the Oracle database into which we will load our data.

CREATE TABLE accounting_table (
ID varchar2(20),
ACCRUED number,

Secondly, we create a control file, which is simply a text file with .ctl extension. The name of the file is accounting_table.ctl

options (skip=1)
load data

     infile accounting_table.csv
     replace into table accounting_table
     fields terminated by "," optionally enclosed by '"'
     trailing nullcols


ID ,
ACCRUED "TO_NUMBER(:ACCRUED,'9999999999999D99','nls_numeric_characters=.,')",
COLLECTED "TO_NUMBER(:COLLECTED,'9999999999999D99','nls_numeric_characters=.,')",
OUTSTANDING_DEBTS "TO_NUMBER(:OUTSTANDING_DEBTS,'9999999999999D99','nls_numeric_characters=.,')"


Thirdly, we create a batch file with the following contents:

sqlldr userid=your_username/your_password@your_database_name control=accounting_table.ctl rows=100000 bindsize=1000000 direct=true

When you double-click the batch file you’ve just created, the loading should begin. That’s it for loading data from a text file to a table in Oracle!

NOTE: Don’t forget to place all files, including the data file, in the same folder. After, completion a log file will be created by the system in the same folder.