Sports

https://www.espncricinfo.com/ci/engine/match/scores/desktop.html
Showing posts with label sql*loader control file load into multiple tables. Show all posts
Showing posts with label sql*loader control file load into multiple tables. Show all posts
Wednesday, October 15, 2014

Export Data from Oracle using SQL*Loader


sql loader xlsx file

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:-
===================

  1. Create a sample table (I use Scott.dept2 table created from dept with the addition of  ENTDATE column)
  2. One control file (control.txt)
  3. One data file (data.csv)
  4. One batch file (optional)
Important Note:-

Join the newsletter

Subscribe to get our latest content by email.
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:-
===================
  1. Create a sample table (same as above dept2 with ENTDATE column addition)
  2. Single control file (control.txt)
  3. multiple data file (data1.csv , data2.csv)
  4. 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

Popular