Oct 302014
 

In order to import DAT file into Oracle table through TOAD follow following steps:

Step1: Create Table “Employee” as below:

CREATE TABLE EMPLOYEE

(

  EMPNAME  VARCHAR2(100 BYTE),

  SALARY   NUMBER

);

Step 2: Create one DAT file with name “Insert_Employee_Data.dat” containing data of Employee table for insertion and save it in D:\ drive as below:

File Name: Insert_Employee_Data.dat

Path: D:\ Insert_Employee_Data.dat

Data in the file:

John, 500

Bill, 1000

Marry,1000

Clinton, 3000

Sam, 4000

Rita, 5000

Sunny, 5000

Karan, 6000

Danny, 7000

Wendy, 7000

Mark, 9000

Step 3: Create CTL file ‘Employee.ctl” and save it in D:\ Drive as below:

File Name: Employee.ctl

Path: D:\ Employee.ctl

CTL file script:

LOAD DATA

INFILE ‘D:\Insert_Employee_Data.dat’

BADFILE ‘D:\Insert_Employee_Data.bad’

DISCARDFILE ‘D:\Insert_Employee_Data.dsc’

Append

INTO TABLE EMPLOYEE

FIELDS TERMINATED BY ‘,’

TRAILING NULLCOLS

(

EMPNAME,

SALARY)

Step 4: Go to TOAD and follow below path to call.ctl file:

TOAD  >>  DATABASE >>  IMPORT >> SQL* LOADER WIZARD >> Select “Use Existing Control File” Radio button as shown below:

 

Select "Use Existing Control File" Radio Button

Select “Use Existing Control File” Radio Button

 

Now, Click on “Next” button and browse the “Employee.CTL” file from D:\ drive as shown below:

 

Select "Employee.CTL" File

Select “Employee.CTL” File

 

Click “Next” button and execute the script by clicking on the “Finish” button.

 

Execute the Script by Clicking on Finish Button

Execute the Script by Clicking on Finish Button

Data is loaded successfully as shown below:

Data Uploaded Successfully

Data Uploaded Successfully

 

Step 5: Check the “Employee” table for data verification uploaded through SQL* Loader.

select * from employee;

Output:

EMPNAME SALARY
John 500
Bill 1000
Marry 1000
Clinton 3000
Sam 4000
Rita 5000
Sunny 5000
Karan 6000
Danny 7000
Wendy 7000
Mark 9000

 

This is how we can use the feature of TOAD to upload the data into the database tables.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)