SQL * Loader

SQL * Loader

What is SQL* Loader?

SQL*loader is one of the Oracle tool which will be used to transfer the data from Flat-File to oracle Database table.

We can find the fallowing files in SQL*loader
1. Flat or Data File
2. Control File
3. Bad File
4. Discard File
5. Log File

Flat Or Data File: This file contains the records in a special format; these records will be fetching for other legacy. The extension of these files might be .dat, .txt, or .csv (comma separated view).

Control File: This is SQL loader execution file, which will be used to transfer the date from file to table. In side of these control file, we will mention the Data file path, table name, column mapping. The extension of control file is .ctl

Control File Creation:

Load data
INFILE ‘Data File Path’
INSERT INTO ‘Table Name’
FIELD TERMINATED BY ‘,’
WHERE deptno = 10
TRAILING NULL COLS(column1 , empno
column2, ename
column3, deptno)

Once we develop the control file we will execute this by using fallowing command
C:\> sqlldr user/passward @ Database Control = name of control file (with extension .ctl)This command will start the control file execution, and it will try to read the data and inserting into table. After completion of this execution, automatically three files will gets created
Bad file
Discard file
Log file

Bad File: Bad file contain the records, which are rejected by the SQL*loader. SQL*loader will reject the records, when ever the Flat file format is not correct or if any internal error occurs it will rejected. The extension of bad file is .bad

Discard File: Discard file contains the records which are rejected by the control file, control file reject the records, if record is not satisfying the conditions, which we have mentioned inside of control files the extension of discard file is .dis

Logfile: It contains the complete info of the process, like no of records successfully loaded in to the table
No of records successfully loaded in to the bad file & discard file.
And where the bad, discard file gets created and time taken to complete the process.
Taking the complete log.

LOAD DATA statement is required at the beginning of the control file.

INFILE: INFILE keyword is used to specify location of the datafile or datafiles.

INFILE * specifies that the data is found in the control file and not in an external file. INFILE '$FILE', can be used to send the filepath and filename as a parameter when registered as a concurrent program.

INFILE   '/home/vision/kap/import2.csv' specifies the filepath and the filename.

INTO TABLE is required to identify the table to be loaded into. In the above example INTO TABLE "APPS"."BUDGET", APPS refers to the Schema and BUDGET is the Table name.

FIELDS TERMINATED BY specifies how the data fields are terminated in the datafile.(If the file is Comma delimited or Pipe delimited etc)

OPTIONALLY ENCLOSED BY '"' specifies that data fields may also be enclosed by quotation marks.

TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

OPTION statement precedes the LOAD DATA statement. The OPTIONS parameter allows you to specify runtime arguments in the control file, rather than on the command line. The following arguments can be specified using the OPTIONS parameter.

SKIP = n -- Number of logical records to skip (Default 0)

LOAD = n -- Number of logical records to load (Default all)

ERRORS = n -- Number of errors to allow (Default 50)

ROWS = n   -- Number of rows in conventional path bind array or between direct path data saves (Default: Conventional Path 64, Direct path all)

BINDSIZE = n -- Size of conventional path bind array in bytes (System-dependent default)

SILENT = {FEEDBACK | ERRORS | DISCARDS | ALL} -- Suppress messages during run

(header, feedback, errors, discards, partitions, all)

DIRECT = {TRUE | FALSE} --Use direct path (Default FALSE)

PARALLEL = {TRUE | FALSE} -- Perform parallel load (Default FALSE)

SQL* Loader Modes:

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.

C:\> sqlldr userid/passward@Database control=text1.ctl path=direct

SQL* Loader Paths: We can execution SQL* loader in two paths or nodes

Direct

Conventional
By default SQL*loader will be running in conventional mode, if we want to run in direct mode will use the fallowing syntax
C:\> sqlldr userid/passward@Database control=text1.ctl path=direct
Direct mode will disable the table and column constrains and it will insert the data.
Conventional path will check every constrains, if it is satisfied it will insert the record
Conventional path is just like ‘insert statement’

SQL Commands Limitations:to_date, to_char, upper, lower, Initcap, string, decode, nvl
when clause
sequence_name.next_value, Ref-Cursor
sysdate, ltrim, rtrim, constant

Structure of a Control file:

Sample CTL file for loading a Variable record data file:

OPTIONS (SKIP = 1)   --The first row in the data file is skipped without loading

LOAD DATA

INFILE '$FILE'             -- Specify the data file path and name

APPEND                       -- type of loading (INSERT, APPEND, REPLACE, TRUNCATE

INTO TABLE "APPS"."BUDGET"   -- the table to be loaded into

FIELDS TERMINATED BY '|'           -- Specify the delimiter if variable format datafile

OPTIONALLY ENCLOSED BY '"'   --the values of the data fields may be enclosed in "

TRAILING NULLCOLS     -- columns that are not present in the record treated as null

(ITEM_NUMBER    "TRIM(:ITEM_NUMBER)", -- Can use all SQL functions on columns

QTY                 DECIMAL EXTERNAL,

REVENUE             DECIMAL EXTERNAL,

EXT_COST            DECIMAL EXTERNAL TERMINATED BY WHITESPACE "(TRIM(:EXT_COST))"  ,

MONTH           "to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),'DD-MON-YY')" ,

DIVISION_CODE    CONSTANT "AUD"  -- Can specify constant value instead of

Getting value from datafile

)

Skip columns:

You can skip columns using the 'FILLER' option.

Load Data
--
--
--
TRAILING  NULLCOLS
(
name Filler,
Empno ,
sal
)

here the column name will be skipped.

Steps to Run the SQL* LOADER from UNIX:

At the prompt, invoke SQL*Loader as follows:

sqlldr USERID=scott/tiger CONTROL= LOG=

name>

SQL*Loader loads the tables, creates the log file, and returns you to the system prompt. You can check the log file to see the results of running the case study.

Register as concurrent Program:

Place the Control file in $CUSTOM_TOP/bin.

Define the Executable. Give the Execution Method as SQL*LOADER.

Define the Program. Add the Parameter for FILENAME.

SQL * Loader

SQL * Loader :
==================
It is one of the Oracle Tool  will be used to transfer the data from Flat files into 
Oracle Base tables.
When we are developing the Interfaces or Conversion program in Oracle Applications 
then we will be using the SQL Loader Programs.

while working with SQL * Loader we will come across 5 Files.

1)Flat File OR data File:

Data file contains the data in the specific format which we will receive from the 
client. this file will be generated by the legacy system(Other Systems) program.
It may be either .txt format
   .dat
   .csv(Comma Separated View) Excell sheet.
Most of the times we will receive in the (.csv) format  

2)Control File : 
-----------------
Is nothing but a SQL*Loader Program will be used to load the data from file into table.
we will develop the control File
        Execute the Control File.
While developing the Control file we will specify follwoing things.
1)Data File path
2)Database Table Name
3)Column Mapping.
Control file Extension is (.ctl)

3)Bad File:
-----------
when we execute the COntrol file if any records are rejected by SQL *Loader then we
will find those rejecteds record in the Bad file.
SQl loader recjects the records if there are any datatype mismatch
              Internal Errors
       Data file Format problem
Bad File Extension is (.bad)

4)Discard File:
---------------
when we execute the COntrol file if any records are rejected by Control File then we
will find those rejecteds record in the Discard file.
Control file Recjects the record when the record is not satisfying the record which
are not satisfied the condition specified in the Control file.
Discard File Extension is (.dis)

5)Log File :
-------------

It contains the Log information like Program start time,End Time
No of succesfully records upload
Rejected Records into the Bad file
Rejected records into the Discard file
Any errors are there in the Control file those error message  details we will find.
Log file Extension is (.log)


Control File Syntax:
----------------------

LOAD DATA
INFILE 'Data File Path'
Insert into table <Table Name>
FIELDS TERMINATED by ',' OPTIONALLY ENCLOSED BY'"'
TRAILING NULLCOLS
(Column1    ,
 column2    ,
 column3    ,......)


Execution of Control File :
-----------------------------

Goto Command Prompt where the SQl loader is Installed.

D:Oracle\Proddb\8.1.7\bin\  SQLLDR  Username/Password@PROD
Control = Control file Path.