sql*loader control file load into multiple tables Wednesday, October 15, 2014

Export Data from Oracle using SQL*Loader

Using SQL Loader for Single and Multiple Files

Hello, All My Friends,
=====
This is for Windows Scenario, You can add Service using Net Configuration Assistant to connect to your server machine and that is the much safer way in my little opinion.

Let's Start it right now

For Single Load File

=======================

Requirements are as under:-
===================

Create a sample table (I use Scott.dept2 table created from dept with the addition of ENTDATE column)
One control file (control.txt)
One data file (data.csv)
One batch file (optional)

Important Note:-

Before you start working on this article, just remember that file paths must be correct and clearly mentioned with their drive letters for avoiding any inconvenience.

Here is the example

Control File (ControlFile.txt)

========

------------------------------------------------------------

-- SQL-Loader Basic Control File

------------------------------------------------------------

options ( skip=1 )

load data

infile 'F:\oracle\dbHome\BIN\sqlloader\single_file_Insert\dept.csv'

truncate into table scott.dept2

fields terminated by ","

optionally enclosed by '"'

( DEPTNO

, DNAME

, LOC

, ENTDATE

)

Data File (dept.csv)

======================

DEPTNO,DNAME,LOC,entdate

1,F1,X1,15-Oct-14

2,G1,X2,16-Oct-14

3,H1,X3,17-Oct-14

4,I1,X4,18-Oct-14

5,J1,X5,19-Oct-14

6,K1,X6,20-Oct-14

7,L1,X7,21-Oct-14

8,M1,X8,22-Oct-14

9,N1,X9,23-Oct-14

10,O1,X10,24-Oct-14

11,P1,X11,25-Oct-14

12,Q1,X12,26-Oct-14

13,R1,X13,27-Oct-14

14,S1,X14,28-Oct-14

15,T1,X15,29-Oct-14

16,U1,X16,30-Oct-14

17,V1,X17,31-Oct-14

18,W1,X18,1-Nov-14

19,X1,X19,2-Nov-14

20,Y1,X20,3-Nov-14

21,Z1,X21,4-Nov-14

Batch File (Start_SQL_Load.bat)
============

@echo off
F:\oracle\dbHome\BIN\sqlldr 'scott@pmstest' control='F:\oracle\dbHome\BIN\sqlloader\single_file_Insert\ControlFile.txt'
log='F:\oracle\dbHome\BIN\sqlloader\single_file_Insert\Results.log'
pause

Last Step
======
Just double-click this batch file. (Then just verify that records are inserted successfully)

Note: No need to create dept.bad file it will create this automatically

For Mulitple File Insertion

==============================

Requirements are as under:-
===================

Create a sample table (same as above dept2 with ENTDATE column addition)
Single control file (control.txt)
multiple data file (data1.csv , data2.csv)
One batch file (optional)

Control File (control.txt)
------------------------------------------------------------
-- SQL-Loader Basic Control File
------------------------------------------------------------
options ( skip=1 )
load data
infile 'F:\oracle\dbHome\BIN\sqlloader\multi_file_insert\dept1.csv'
infile 'F:\oracle\dbHome\BIN\sqlloader\multi_file_insert\dept2.csv'
truncate into table scott.dept2
fields terminated by ","
optionally enclosed by '"'
( DEPTNO
, DNAME
, LOC
, entdate
)

Ist Data File (dept1.csv)

======================

DEPTNO,DNAME,LOC,entdate
1,F1,X1,15-Oct-14
2,G1,X2,16-Oct-14
3,H1,X3,17-Oct-14
4,I1,X4,18-Oct-14
5,J1,X5,19-Oct-14
6,K1,X6,20-Oct-14
7,L1,X7,21-Oct-14
8,M1,X8,22-Oct-14
9,N1,X9,23-Oct-14
10,O1,X10,24-Oct-14
11,P1,X11,25-Oct-14
12,Q1,X12,26-Oct-14
13,R1,X13,27-Oct-14
14,S1,X14,28-Oct-14
15,T1,X15,29-Oct-14
16,U1,X16,30-Oct-14
17,V1,X17,31-Oct-14
18,W1,X18,1-Nov-14
19,X1,X19,2-Nov-14
20,Y1,X20,3-Nov-14
21,Z1,X21,4-Nov-14

2nd Data File (dept2.csv)

======================

DEPTNO,DNAME,LOC,entdate

22,F2,X1,17-Oct-14

23,G2,X2,18-Oct-14

24,H2,X3,19-Oct-14

25,I2,X4,20-Oct-14

26,J2,X5,21-Oct-14

27,K2,X6,22-Oct-14

28,L2,X7,23-Oct-14

29,M2,X8,24-Oct-14

30,N2,X9,25-Oct-14

31,O2,X10,26-Oct-14

32,P2,X11,27-Oct-14

33,Q2,X12,28-Oct-14

34,R2,X13,29-Oct-14

35,S2,X14,30-Oct-14

36,T2,X15,31-Oct-14

37,U2,X16,01-Nov-14

38,V2,X17,02-Nov-14

39,W2,X18,03-Nov-14

40,X2,X19,04-Nov-14

41,Y2,X20,05-Nov-14

42,Z2,X21,06-Nov-14

Batch File (Start_SQL_Load.bat)
============

@echo off
F:\oracle\dbHome\BIN\sqlldr 'scott@pmstest' control='F:\oracle\dbHome\BIN\sqlloader\multi_file_insert\ControlFile.txt'
log='F:\oracle\dbHome\BIN\sqlloader\multi_file_insert\Results.log'

pause
Last Step
=======

Just double-click this batch file. (Then just verify that records are inserted successfully using sqlplus)

Result
=======
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining, and Real Application Testing options

SQL> select * from dept2;

DEPTNO DNAME LOC ENTDATE
---------- -------------- ------------- ---------
1 F1 X1 15-OCT-14
2 G1 X2 16-OCT-14
3 H1 X3 17-OCT-14
4 I1 X4 18-OCT-14
5 J1 X5 19-OCT-14
6 K1 X6 20-OCT-14
7 L1 X7 21-OCT-14
8 M1 X8 22-OCT-14
9 N1 X9 23-OCT-14
10 O1 X10 24-OCT-14
11 P1 X11 25-OCT-14

DEPTNO DNAME LOC ENTDATE
---------- -------------- ------------- ---------
12 Q1 X12 26-OCT-14
13 R1 X13 27-OCT-14
14 S1 X14 28-OCT-14
15 T1 X15 29-OCT-14
16 U1 X16 30-OCT-14
17 V1 X17 31-OCT-14
18 W1 X18 01-NOV-14
19 X1 X19 02-NOV-14
20 Y1 X20 03-NOV-14
21 Z1 X21 04-NOV-14
22 F2 X1 17-OCT-14

DEPTNO DNAME LOC ENTDATE
---------- -------------- ------------- ---------
23 G2 X2 18-OCT-14
24 H2 X3 19-OCT-14
25 I2 X4 20-OCT-14
26 J2 X5 21-OCT-14
27 K2 X6 22-OCT-14
28 L2 X7 23-OCT-14
29 M2 X8 24-OCT-14
30 N2 X9 25-OCT-14
31 O2 X10 26-OCT-14
32 P2 X11 27-OCT-14
33 Q2 X12 28-OCT-14

DEPTNO DNAME LOC ENTDATE
---------- -------------- ------------- ---------
34 R2 X13 29-OCT-14
35 S2 X14 30-OCT-14
36 T2 X15 31-OCT-14
37 U2 X16 01-NOV-14
38 V2 X17 02-NOV-14
39 W2 X18 03-NOV-14
40 X2 X19 04-NOV-14
41 Y2 X20 05-NOV-14
42 Z2 X21 06-NOV-14

42 rows selected.

SQL>

Note: No need to create dept.bad file it will create this automatically

Did you like this blog please appreciate.

Thanks & Regards,

Muhammad Kamran Saeed

Natural DBA