SQL Loader: loading data from Fixed Length files into Oracle database

SQL-Loader to load fixed length files:
------------------------------------------------------------------------

Step1:
-----------
create a text file and convert it into .dat file and copy it to the required directory.

[oracle@pavanorcl101 sqlloader]$ ls
customerfix.dat

01 pavan   bhimavaram
02 harish  vijayawada
03 patty   guntur
04 bhanu   vijayawada
05 thrilok pulivendhula

   First Open the file in a text editor and count the length of fields, for example in our fixed length file, id number is from 1st position to 2nd position, name is from 4th position to 10th position, City is from 12th position to 23rd position.


Step2:
-----------
Creating table with name customer with columns id, name and city which is required to load the data from .dat file

[oracle@pavanorcl101 sqlloader]$ sqlplus scott/tiger

SQL> create table customerfix (id number(5), name varchar(50), city varchar(30));
Table created


Step3:
-----------
Creating control file in which actions are defined for SQL-loader to do.

[oracle@pavanorcl101 sqlloader]$ vi customerfix.ctl

LOAD DATA
INFILE '/u01/app/sqlloader/customerfix.dat'
INTO TABLE customerfix
(id POSITION(01:02) INTEGER EXTERNAL,
 name POSITION(04:10) CHAR,
 city POSITION(12:23) CHAR)



Step4:
-----------
After you have wrote the control file save it and then, call SQL Loader utility by typing the following command

[oracle@pavanorcl101 sqlloader]$ sqlldr userid=scott/tiger control=customerfix.ctl log=customerfix.log direct=y

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Dec 4 17:08:18 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 5.

Table CUSTOMERFIX:
  5 Rows successfully loaded.

Check the log file:
  customerfix.log
for more information about the load.


Check table in the database for loaded values:
-----------------------------------------------------------

[oracle@pavanorcl101 sqlloader]$ sqlplus scott/tiger

SQL> select * from customerfix;

        ID NAME                 CITY
---------- -------------------- --------------------
1 pavan                bhimavaram
2 harish               vijayawada
3 patty                guntur
4 bhanu                vijayawada
5 thrilok              pulivendhula




Loading Data into Multiple Tables using WHEN condition:
-----------------------------------------------------------------------------------------------------------

[oracle@pavanorcl101 sqlloader]$ vi customerfix1.ctl

load data
infile '/u01/app/sqlloader/customerfix.dat'
append into table scott.customerfix1
when (id='03')
(id POSITION(01:02) INTEGER EXTERNAL,
 name POSITION(04:10) CHAR,
 city POSITION(12:23) CHAR)
into table scott.customerfix2
when (id<>'03')
(id POSITION(01:02) INTEGER EXTERNAL,
 name POSITION(04:10) CHAR,
 city POSITION(12:23) CHAR)



###After you have wrote the control file save it and then, call SQL Loader utility by typing the following command###

[oracle@pavanorcl101 sqlloader]$ sqlldr userid=scott/tiger control=customerfix1.ctl log=customerfix1.log direct=y


SQL*Loader: Release 12.1.0.2.0 - Production on Mon Dec 4 17:57:53 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 5.

Table SCOTT.CUSTOMERFIX1:
  1 Row successfully loaded.

Table SCOTT.CUSTOMERFIX2:
  4 Rows successfully loaded.

Check the log file:
  customerfix1.log
for more information about the load.



Check table in the database for loaded values:
-----------------------------------------------------------

[oracle@pavanorcl101 sqlloader]$ sqlplus scott/tiger


SQL>  select * from customerfix1;

        ID NAME                 CITY
---------- -------------------- ------------------------------
         3 patty                guntur



SQL> select * from customerfix2;

        ID NAME                 CITY
---------- -------------------- ------------------------------
         1 pavan                bhimavaram
         2 harish               vijayawada
         4 bhanu                vijayawada
         5 thrilok              pulivendhula



###Loading data from .csv file with "," delimiter to oracle database using SQL Loader###

https://oracleinsightspk.blogspot.com/2017/12/sql-loader.html