Load the data into Oracle database using SQL Loader

 Load the data into Oracle database using SQL Loader-

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. It supports various load formats, selective loading, and multi-table loads.

OR 

SQL*Loader is a command line utility, provided by Oracle to load data from external files into Oracle database.

Note- We can only insert the data into target tables.

In SQL*Loader, during the process of loading data from external file, It needs two input files and generates three output files.

•  Input files-
• Data File
• Control File
•  Output files-
• Log File
• Bad File
• Discard File

In details-

1. Data File: It is a flat file/external file/source file, which we need to load into database.

2. Control file: Control file defines the structure of a flat file/external file/source file. How the data is         formatted in data file, information about target table to load the data etc.

Process-

SQL Loader reads the information from control file and loads the information into target table.

We should follow below steps to load the data-

1. Create a target table, in which we have to load the data.

e.g. 

CREATE TABLE DEMO_SQLLDR 

   ( EMPID NUMBER, 

ENAME VARCHAR2(50), 

SAL NUMBER

   );

   2. Create Control file-

Syntax for control file creation:

OPTIONS(SKIP=1)

LOAD DATA

INFILE 'Demo_data_file.txt'

DISCARDFILE 'Demo_discard_data_file.txt'

TRUNCATE INTO TABLE DEMO_SQLLDR

WHEN empid="1"    

FIELDS TERMINATED BY ","

(Empid,Ename,Sal)

Note: When we write TRUNCATE : means, previous/existing data will be deleted from table and new data will be loaded from data file.

  When we write APPEND:  means, previous/existing data will remain in table and new data will be added.

Note: If there is no discarded data available in file or if we don't put any condition in WHEN clause of control file to discard the data then discarded file will not get generated.

3. Now, open command prompt in Windows and navigate the location where we have saved both files(Data file and Control file).

-- To check the files, if files are existing in particular location or not, use command dir as below-

4. Now type below command to load the data into target table-

Syntax-

sqlldr user/password@databasename control=control_file name

e.g. 

sqlldr user1/User#12@ORCLDB control=control_file.ctl

-> If you are loading data from sys user, use below command-

sqlldr \"/ as sysdba\" control=control_file.ctl

5. After the process of loading data from data file into database, SQL Loader generates three output files-

1- Log File- It contains the information about process logs. Whether the load is successful or not.                             We can get the information like (No of records loaded, No of failure records with                                     reasons) in this file. 

2- Bad File- It contains the information about the failed data. If the data is not loaded into the                                     target table, it comes in bad file.

3- Discard File- It contains information about the discarded data. When we need to load only                                     selected data, as per the WHEN condition in control file, then whatever the data                                     doesn't meet the condition, comes in Discard file.

***In case of any query, Kindly reach me at yogitatripathi1225@gmail.com with Subject line-Query_For_Blog