SQL-Loader

SQL Loader:
-----------------
SQL LOADER utility is used to load data from other data source into Oracle. For example, if you have a table in ACCESS or SYBASE or any other third party database, you can use SQL Loader to load the data into Oracle Tables. SQL Loader will only read the data from Flat files. So If you want to load the data from Access or any other database, you have to first convert that data into Delimited Format flat file or Fixed length format flat file, and then use SQL loader to load the data into Oracle.

Following is procedure to load the data from Third Party Database into Oracle using SQL Loader.

Convert the Data into Flat file using third party database command.
Create the Table Structure in Oracle Database using appropriate datatypes
Write a Control File, describing how to interpret the flat file and options to load the data.
Execute SQL Loader utility specifying the control file in the command line argument


Example:
----------------
Creating a directory:

[oracle@pavanorcl101 app]$ mkdir sqlloader
[oracle@pavanorcl101 sqlloader]$ pwd
/u01/app/sqlloader

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

[oracle@pavanorcl101 sqlloader]$ ls
customer.csv


In this example, i have created a text file customer.txt and rename the file to customer.csv

The data in the file look like:

1,pavan,bhimavaram
2,harish,vijayawada
3,patty,guntur
4,bhanu,vijayawada
5,thrilok,pulivendhula


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


[oracle@pavanorcl101 sqlloader]$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 4 15:12:17 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Dec 04 2017 15:02:32 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user
USER is "SCOTT"


SQL> create table customer (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 customer.ctl

LOAD DATA
INFILE '/u01/app/sqlloader/customer.csv'
BADFILE '/u01/app/sqlloader/customer.bad'
DISCARDFILE '/u01/app/sqlloader/customer.dsc'
INSERT INTO TABLE CUSTOMER
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(id,name,city)


[oracle@pavanorcl101 sqlloader]$ ls
customer.csv  customer.ctl

Notes:
-----------
1.The LOAD DATA statement is required at the beginning of the control file.
2.The INFILE option specifies where the input file is located
3.Specifying BADFILE is optional. If you specify,  then bad records found during loading will be stored in this file.
4.Specifying DISCARDFILE is optional. If you specify, then records which do not meet a WHEN condition will be written to this file.
5.You can use any of the following loading option
    i.INSERT : Loads rows only if the target table is empty
    ii.APPEND: Load rows if the target table is empty or not.
    iii.REPLACE: First deletes all the rows in the existing table and then, load rows.
    iv.TRUNCATE: First truncates the table and then load rows.
6.This line indicates how the fields are separated in input file. Since in our case the fields are separated by “,” so we have specified “,” as the terminating char for fields. You can replace this by any char which is used to terminate fields. Some of the popularly use terminating characters are semicolon “;”, colon “:”, pipe “|” etc. TRAILING NULLCOLS means if the last column is null then treat this as null value, otherwise,  SQL LOADER will treat the record as bad if the last column is null.
7.In this line specify the columns of the target table. Note how do you specify format for Date columns


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=customer.ctl log=customer. log

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Dec 4 15:02:32 2017

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

Path used:      Conventional
Commit point reached - logical record count 5

Table CUSTOMER:
  5 Rows successfully loaded.

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


SQL> show user
USER is "SCOTT"
SQL>
SQL>
SQL>
SQL> column id format a10
SQL> column name format a20
SQL> column city format a20
SQL>
SQL> select * from customer;

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



###Load data from fixed length files to oracle database using SQL Loader###

https://oracleinsightspk.blogspot.com/2017/12/sql-loader-loading-data-from-fixed.html