SQL LOADER BASICS..

Sql*Loader loads the data from flat file to oracle database.

FLAT FILE: The file which contains the database data in a plain text format .In RDBMS contains table with one records per line. Data stored in flat files have no folders or paths associated with them. 

The flat files are mainly used in the data warehousing project to import the data but for people who are working in the database line then it is must known topic for them ,how to import the data from flat file to the oracle database or any other database.

The information that are stored in the file is only for reading ,stored and sent are the only limitation.


The alternate definition of  the sql*loader:      It loads the data from external files to tables of an oracle database. 

In general, SQL*Loader follows the SQL standard for specifying object names (for example, table and column names)

CONTROL FILE is the basic file(a text file) that we are going to work with SQL LOADER. The control file as we know the most important file in the oracle database which provides the information of all other file and name of the database(provides the physical structure of the database) . But here in this case it is little different.

Control file  provides the description of the data which are going to be loaded into the database also tell who will going to active(i mean to say which table and which row or column) when the data gets loaded.



The Input data file which contains all the data to be loaded into the database are read by the SQL*LOADER and execute the data and provides the data to the Oracle database .

The SQL*LOADER Control file describes:

1. As in case of the DATABASE control file , it describes the location and name of the data file.

2. Describes where the data will going to be stored(i.e in which table and in which row).

3. the name and location of the bad file and discarded file.



Description of the above control file be:

2. --  Begin wit the comment line .

4.  The LOAD DATA describes that sql*loader should enter a new data record into the database.

5. The INFILE describes the name and location of the file that you are going to load into the oracle database.

6.  The BAD FILE ,acts like a container for the rejected records and possible errors while loading the records into the database by loader.

7. The discarded file specifies the name of a file into which discarded records are placed. 

8. The APPEND clause is one of the options you can use when loading data into a table that is not  empty. IF Data already exists in the table, SQL*Loader appends the new rows to it. If data does not already exist, the new rows are simply loaded.


TYPE OF LOADING:

INSERT -- If the table you are loading is empty, INSERT can be used.

APPEND -- If data already exists in the table, SQL*Loader appends the new rows to it. If data doesn't already exist, the new rows are simply loaded.

REPLACE -- All rows in the table are deleted and the new data is loaded

TRUNCATE -- SQL*Loader uses the SQL TRUNCATE command.



To load data into a table that is empty, you would use the INSERT clause.

9. And all other are you know ,same like in sql query.

IF you want to load data from multiple data files in one SQL*Loader run, use an INFILE clause for each data file.Also can declare the discard and bad file for each data file separate.

Example:

 INFILE 'dkcontrol.dat' DISCARDEDFILE 'dkcontrol.dsc'

INFILE 'dkcontril1.dat' BADFILE 'dkcontrol.bad' DISCARDEDFILE 'dkcontrol1.dsc'

INFILE 'dkcontrol3.dat'



The LOG FILE

It a record of SQL*LOADER's activites during load session.
In this case the log file is the main file 

1.  It contains the information regarding the names of the control file,bad file ,discarded file and all other logfiles.

2.  All the details of the datatype and field that are present in the data file are described .

 3.  Error messages for records that may cause the error.

BAD FILE & DISCARDED FILE

 Whenever we are creating a file ,we try to make a backup of that file and a handler for possible errors.

 Whenever you insert data into a database, you run the risk of that insert failing because of some type of error.
There are so many minor mistakes we are doing while we are making a record like while declaring the constraints and declaring the data type for example.

So,whenever the there is an error found while loading the record into the database ,SQL*LOADER encounter or writes that record to a file known as BAD FILE.(Same as ERROR file- Handle all kind of error).

Discard files, on the other hand, are used to hold records that do not meet selection criteria specified in the SQL*Loader control file.

This file is created when it is needed and only if you specified that discarded file should be enabled.

The discard file contains records that were filtered out of the load because they did not match any record-selection criteria specified in the control file.

The  architectural  diagram of the above explanation.



Now lets do a REAL TIME example how to import the data file into the database by using the SQL*LOADER.

I am going to the import the below .csv file into my table life_frnds. 
STEP1
  saved the file as dkfrnds.csv

STEP2
If you have the UNIX box then type the command cat to find out the information in the file.

$ cat dkfrnds.csv 

STEP3

I have created a new table called life_frnds for better understanding . You can use a old table too.

Now check whether you have any data present in the table by executing the below query.

create table life_friends
 {

 rank number(10,0),

 Name Varchar(20)

 }

SQL> select * from life_frnds.;

No records (expected results  because i have created a  new table )


STEP4

Now you need a control file to import the .csv file into the database ,so lets create a control file for this.

load data

infile "dkfrnds.csv" badfile "dkfrnds.bad" discardfile "life_frnds.dsc"

 insert 

 into table life_frnds

 Fields terminated by ':' TRAILING NULLCOLS
 {
 rank,
 name
 }

and saved in any folder .I have saved it my  C:\BestBuddies directory as dkfrnds.ctl

When a control file gets executed, it may create 3 files log file, Discarded file and Bad file.
Log file is the mandatory file which will going to create whether the control file gets fail or pass. BAD file will be created when there is some Oracle validation(Errors) gets failed but DISCARDED file gets created whenever the condition gets fail(particular WHEN Condition,if the control file contains the WHEN condition).


 STEP5

Need a BATCH FILE to invoke the control file 

So lets create a batch file and call the control file which  contains the information of our .csv file which will going to insert into DB.

The Syntex for creating the Batch File

sqlldr debashis/debashis@college control-dkfrnds.ctl log-dkfrnds.log
 pause

Saved as dkfrnds.bat (.bat extension) 

( I have saved all the file in the same name(dkfrnds)  because  it will remind me easily and make the things easy for me ,you can save in any name).

Some Guidelines of the above query.

 sqlldr -  This you can find  if you will redirect your self  to ORCLE\BIN\

debashis\debashis -these are the username and password 

@college- It is the name of the database.

control-dkfrnds.ctl - Name of the control file that we have created just 10 minutes back .

dkfrnds.log - Name of the log file 

ALL the terms that are used in the above query all are explained above page or you can visit the below link as i mentioned bottom of my page for better undersatnding.

STEP6. 

 Now times to run the commands .

open your command prompt and type the name of the batch file.

(Please go to that directory where you have saved your batch file ,otherwise you will face the error(the path does not found)).



And you are done.

Step 7.

Again execute the query

select * from life_frnds.;

And found 8 records are inserted into the table by my csv file.


Thanks .....Have fun..



I can recommended you to visit my below link for more clarity....

http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_control_file.htm#i1005162 

http://oreilly.com/catalog/orsqlloader/chapter/ch01.html 

http://alloracletech.blogspot.in/2008/08/oracle-application-story.html