SQL*Loader

SQL*Loader In Oracle

*Loading data's to table from the external File(Like CSV).
*SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. 

STEPS::

==>Login into your SQLPLUS Tool
==>CREATE The Table Or Check the Table already existing in the Database
==>Create the Controler File(.ctl Format)
==>Get ready the External file with full of data's(External like Excel(.csv) File)
==>The table Should be Empty While Insert(load data) or other wise the Error Message Will thrown
  [like --> SQL*Loader-601: For INSERT option, table must be empty.]

Commands::


Controller file Syntax::

load data
infile 'External File Path'
  into table Table_Name
  fields terminated by "," optionally enclosed by '"'
  (Col..1,Col...2....etc)

Example Coding::

load data
  infile 'E:\XAVIER WORK\SQL ORACLE\UTL_Load\Testing.csv'
into table TEST_LDR
fields terminated by "," optionally enclosed by '"'
(Name,salary)

========>>Queries to be Follow in SQLPLUS TOOL<<=========

----------------------------------------------------------------------
C:\Users\JESUS>SQLPLUS HR/open
----------------------------------------------------------------------


SQL*Plus: Release 10.2.0.1.0 - Production on Sat Jun 11 11:50:22 2016

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


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
-------------------------------------------------------------------------
SQL> $SQLLDR HR@XE/open

control = E:\ORACLE\SQL ORACLE\UTL_Load\Control.ctl
-------------------------------------------------------------------------

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Jun 11 11:17:59 2016

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

Commit point reached - logical record count 3

-----------------------------------------
SQL> SELECT * from TEST_LDR;

NAME                               SALARY
------------------------------ ----------
Test                                    1000
xxx                                      2000
yyy                                      3000


========>>If already Some data's in the table then What happens??<<=========

SQL> SELECT * FROM SQL_LRD;

NAME                               SALARY
------------------------------ ----------
Test                                 1000
xxx                                  2000
yyy                                  3000

SQL> $SQLLDR HR@XE/OPEN

control = E:\ORACLE\SQL ORACLE\UTL_Load\Control.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Jun 11 12:26:46 2016

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

SQL*Loader-601: For INSERT option, table must be empty.  Error on table SQL_LDR

LOAD Data's If the Table Having Some data's::

Use APPEND Key word to load data's into table While have some data's in the table.

load data
  infile 'E:\XAVIER WORK\SQL ORACLE\UTL_Load\Testing.csv'
        APPEND
 into table TEST_LDR
 fields terminated by "," optionally enclosed by '"'
 (Name,salary)
----------------------------------------------------------------------------------------------
Use Truncate Keyword to Truncate the data's from table and Insert New records:

load data
  infile 'E:\XAVIER WORK\SQL ORACLE\UTL_Load\Testing.csv'
        TRUNCATE
 into table TEST_LDR
 fields terminated by "," optionally enclosed by '"'
 (Name,salary)

Draft File or Log file in SQL*Loader::

   When we load the data's using sql*loader in any case,if there is any error will happen ,the remaining data's are stored in one log file that is known as "Draft file" or "Log File".

-----SQL*Loader END-----