These days most of the companies are using Oracle as their database to maintain their data. SQL*Plus is the major tool to access the Oracle DB because it is more secure than other tools. Handling flat files in the Oracle DB by using SQL*Plus is difficult one.

SQL Loader is the one which is very use full to load the data into the database from flat files (.txt, .csv, etc..,) and UTL_FILE is the one which is very use full to write the data from database to flat file (.txt, .csv, etc…,)

SQL LOADER:

SQL Loader is used to load the data from flat files to database. Steps to process the SQL Loader are below…, it is a bulk loader utility. The main use of this SQL Loader is to load the data from flat files.

Step by Step process:

Step 1: Create a table to load the data in the database. The screenshot for the same is below,

Here test1 is the table where the data to be loaded from flat file.

Step 2: Create a file with data like below to load the data into the table (test1) which is created in step 1

And Save the data file into any location of our local where the SQL*plus is installed.

Step 3: Create a control file with data file path. Control file is the one where we need to define the data file path and how the data has written in the data file and in which table, data should get loaded

Data file Path – ‘D:\File\Data.csv’

Table to be populated – test1

Field Terminator – Comma (,)

Fields – Name, Technology, Designation

And save the control file with ‘.ctl’ extension.

Note: If you want to insert data into a new table, then the above code will suit. But in case if you want to add or append data into a table which already has data, then the code should be changed like below.

(We can use Append or Truncate. Append is to append data with already existing one, truncate is to truncate the data in that table and insert new record)

Load data infile ‘D:\File\Data.csv’

Append/Truncate

Into table test 1

Fields terminated by “,”

(Name, Technology, Designation)

The below image shows the data file and control file location. (D:\File\)

The below image shows there is no data in the table before performing the SQL Loader operation

Step 4: Once the data file and the control file is placed in our local, exit from the SQL*Plus tool and again open the command prompt

‘SQLLDR’ is the command to process the sql loader operation.

• SQLLDR User_Name/Password@DBname:Portnumber Control=ControlFilePath

We can use Skip to delete any particular line. If you have written data with header we can use SKIP=1 to avoid header while loading. Thw command will look like below,

• SQLLDR hr/admin@xe:1521 Control=D:\File\ControlFile.ctl Skip=1

Once the command executed successfully, check the table whether the table is loaded properly or not

UTL_FILE:

UTL_FILE is a package in oracle used to read and write text files. By using UTL _FILE, we can write data from database to flat file and we can read data from flat files.

Rules:

• The Oracle user must have privilege to the directory where we need to write or read.
• The Oracle user must have privilege to execute UTL_FILE package.

Step by step process:

To Write:

Step 1: Grant execute permission for UTL_FILE to the user.

• Grant execute on UTL_FILE to <User>;

Step 2: Create a directory name for the directory where we need to write data from database.

• Create directory <dir_name> as ‘d:\’;

Step 3: Grant read and write permission for the created directory to the user.

• Grant read, write on directory <dir_name> to <User>;

Step 4: Create a program like below by using created directory.

Here,

test1 – table name

UTL_FILE.FOPEN – To open the directory

‘MYDIR’ – Created directory (D:\File\)

Utl_test.txt – File which we are going to create

W – To write the data into that particular directory with given name

32767 – The maximum character in a single line (we can give limit for single line)

UTL_FILE.PUT_LINE – To write into that file.

UTL_FILE.NEW_LINE – To create new line after inserting one line

UTL_FILE.FCLOSE – To close the opened UTL_FILE

When we execute the above program, it will create the file in the given directory with the given name like below.

To Read:

Step 1: Create a program like below

Here,

UTL_FILE.FOPEN – To open the directory

‘MYDIR’ – Created directory (D:\File\)

Utl_test.txt – File which we are going to read the data

R – To read from that file

UTL_FILE.GET_LINE – To read from the file and load that into variable

Get – Variable to store the data (we can print it later)

UTL_FILE.FCLOSE – To close the opened UTL_FILE

When we execute the below program, it will display the data from the flat file like below.

I hope, this blog would help you to handle flat files in Oracle database and I will come soon with another interesting blog…!