SQL Loader: Import CSV to Oracle table

One of the very common task in oracle environment is export/import of CSV files to Oracle database. Export is pretty straight forward as many of oracle clients provide this as standard functionality for any query. But for import, we can do by writing our own scripts using pl/sql or java or .NET or any other platform.
Since, pl/sql belongs to oracle family and also no need of Middle tier code (application server dependent i.e. Java or .NET etc), we use it in this example. Oracle provides another tool, SQL Loader, using which we can import data files with our custom control files with respect to SQL Loader.

$ sqlldr apps/apps@fz1rw102 control=mycontrolfile.ctl log=test.log

When you install SQL Loader in your system (windows/linux), you can find ’sqlldr’ executable in bin directory.
From the above command,

1. apps/apps@fz1rw102 - username/passwd@SID of oracle database.
2. mycontrolfile.ctl - control file we write will be passed.
3. test.log - log file to know the errors/warnings during the course.

Content of mycontrolfile.ctl is:


LOAD DATA
INFILE ‘data.dat’
APPEND
INTO TABLE UJJWAL_TEST
FIELDS TERMINATED BY “:” OPTIONALLY ENCLOSED BY ‘”‘
(PAYMENT_NUMBER,PAYMENT_METHOD,CUSTOMER_NAME,
CUSTOMER_NUMBER,
APP_GL_DATE DATE(20) “DD-MON-YYYY”,
PAYMENT_GL_DATE DATE(20) “DD-MON-YYYY”,
ENTERED_DEBIT,ENTERED_CREDIT,ACCTD_DEBIT,
ACCTD_CREDIT,
RECEIPT_DATE DATE(20) “DD-MON-YYYY”,
RECEIPT_CURRENCY,CODE_COMBINATION_ID,RECEIPT_SOURCE,
BATCH_NAME,CATEGORY,ACCOUNT,COMPANY,ACCOUNT_DESC)

In the above content, you can find file name ‘data.dat’ which is the CSV file having the data to be imported into some table named PAVAN_TEST with it’s fields mentioned over there.

For more details about sqlloader, please refer to here.