SQL*Loader - Load data into table from CSV file

In this article, I am going to show how we can use Oracle SQL *Loader to insert data into the Oracle table. We will create one simple table and create the necessary files required to load the data from the CSV file into the table using Oracle SQL*Loader. The example given in this article is very basic and it will help beginners to understand the SQL*Loader concept. SQL*Loader has various options to load data and support multiple input data formats.

Step-1: Lets first create a table Employee. 

CREATE TABLE EMPLOYEE (
    EMPLOYEE_ID		NUMBER(8,0),
    NAME 		VARCHAR2(40),
    EMAIL		VARCHAR2(60) NOT NULL,
    DOB			DATE,
    PRIMARY KEY (EMPLOYEE_ID)
);
Step-2: Create SQL*Loader control file - "employee.ctl"

The control file is a text file that SQL*Loader uses to understand where to find the data file, how to parse the data and how to insert the data into the oracle table and many other options.

Given below is the sample format of the control file which is used to load the CSV file into the employee table. 


-- Employee.ctl file
LOAD DATA
INSERT
INTO TABLE EMPLOYEE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
    EMPLOYEE_ID,
    NAME,
    EMAIL,
    DOB DATA "DD-MON-YYYY"
)

The INSERT option is used for first-time data load and the table should be empty. Otherwise, SQL*Loader will complain and throw an error:

SQL*Loader-601: For INSERT option, table must be empty

If you want to load a CSV file into the table which is not empty then, you can use the APPEND or TRUNCATE option in the control file as given below:


LOAD DATA
APPEND
INTO TABLE EMPLOYEE  
.....
.....

APPEND option will add new records in the existing table without deleting any existing records

The TRUNCATE option will truncate the existing table before loading data

Step-3: Data file in comma-separated value (CSV) format. - "employee.dat"

Given below a sample CSV file:


1,James, james@gmail.com, 10-OCT-1988
2,Bryan, brayn_rocks@yahoo.com, 12-NOV-1987
3,Scott, scott@rediff.com, 12-MAY-1999
4,Pankaj Bhandari, pankaj@yahoo.com, 12-JUN-1992
5,Suraj Singh, ssing@gmail.com, 13-JUN-2000
Step-4: Run SQL*Loader command

Given below a sample script that we can use to run SQL*Loader utility


#!/bin/sh

sqlldr username/password@database control=employee.ctl \
data=employee.dat bad=employee.bad log=employee_load.log

reurntcode=`echo $?`

case "$returncode" in 
0) echo "Data loaded successfully";;
1) echo "Execution exited with failures, see logfile" ;;
2) echo "Execution exited with warnings, see logfiles" ;;
3) echo "Fatal Error" ;;
*) echo "Unknown Error" ;;
esac

exit;

You can give a try your hands on SQL*Loader utility and let me know, how it goes for you?

Comments

Post a Comment

Popular posts from this blog

Creating simple Maven multi module project in Java

Image
In this post, I will discuss how to create a simple Maven multi-module project. In multi-module projects, we have multiple modules to be built using Maven. We have external dependencies as well as internal dependencies between different modules. Let's figure out how we can create multi-modules projects in Maven and how the internal and external dependencies are handled by Maven during the build process?. Technologies used: Maven 3.3.3 JDK 1.6 Simple Structure for Multi-Module Project There are multiple ways to create the structure for multi-module maven projects. It depends on the project requirements, code repository structure, and project lifecycle (need for parent pom release, packaging, etc.). One of the simplest structures is given below: Sample Project - RoketApp In order to understand in a better way, let us create a sample multi-module project - RocketApp , similar to the above structure. RocketApp consist of two module - rocket-core & rocket-ut...
Read more

Tricky Java Questions

Image
Java is most popular programming language. It is widely used programming languange and has many job opportunities. Interviewers often ask tricky questions to check the candidate knowldege about Java programming language. Sometimes experienced programmers also fail to answer tricky questions. In this post, I have picked few conceptual questions that will helped you to have better understanding of Java language. #### Q-1: what will be output of following code ? ``` public static void main(String[] args) { Integer number1 = 100; Integer number2 = 100; if (number1 == number2) { System.out.println("number1 == number2"); } else { System.out.println("number1 != number2"); } ``` #### Answer: ``` number1 == number2" ``` We have compared the references of two Integers. The result is surprising !!!. This is because of the autoboxing. Java uses IntegerCache to suppor...
Read more

How to update existing CCDT file (AMQCLCHL.TAB) for successful MQueue connection

The client channel definition table ( CCDT) file contains the MQ channels definitions and authentication information used by the client program/application to connect to the queue manager. It is a binary file so we cannot modify using any text editor. Change in the channel properties like SSLCIPH, USERID, etc. in Queue manager also required change in the CCDT file. Otherwise, it will break the application MQ connection. From IBM MQ v8.0 onwards we can create and modify the CCDT file on client machines directly. Below are the steps to modify the existing CCDT file. Set the necessary environment properties First set the location of CCDT on the client machine. You can do it using the following environment variables: MQCHLLIB – Specify the directory path where the CCDT file is located. MQCHLTAB – Specify the file name of CCDT (a default name – AMQCLCHL.TAB) #Only path (exclud...
Read more