1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

SQL*Loader in Deatils

What is SQL*Loader?
One of the most common tasks of DBA is loading data from external sources. SQL*Loader has always been an important tool for loading data into oracle databases.

Before we can run applications on an oracle database we need to populate our database. One of the most common sources of database data is a set of flat files from legacy systems or some other sources.

SQL*Loader is technically oracle supplied utility to load data from external files.

Recent advancement is External tables feature which enables us to load data from external data files. SQL*Loader is used for this purpose.

Use Of SQL*Loader Utility
SQL*Loader is the oracle supplied utility to load data into database from external sources. SQL*Loader comes along with oracle database server..

SQL*Loader is an immensely powerful tool that can perform more than just a data load from text files.

Some powerful capabilities of SQL*Loader are given below:

1) We can load data across a network

2) We can selectively load from input files based on condition

3) We can perform simultaneous data loads

4) We can automate the load process so that it runs at scheduled times

5) We can load complex object-relational data

6) We can load data from multiple data sources includng disk, tape and named pipes. We can also use multiple data files in the same loading session

7) We can use SQL*Loader to transform data before it is loaded into the database or during the data load itself

We can use SQL*Loader to perform the following types of data loading:

Conventional data Loading :

SQL*Loader reads multiple rows at a time and saves them in a bind array. It subsequently inserts the whole array into the database at once and commits its operation.

Direct-path Loading :

Column array structures are built from the data to be loaded and these structures are used to format oracle data blocks that are then written directly to the database tables. It doesn't use SQL INSERT statement.

External data Loading :

This is the recent oracle technology which relies on the functionality of SQL*Loader to access data in external files as if it were a part of the database tables. When we use ORACLE_LOADER access driver to create an external table we are basically using the SQL*Loader functionality. We can also use ORACLE_DATAPUMP access driver which provides the ability to write to external tables.

Loading data using SQL*Loader involves two main steps:

1) Select the datafile that conatins the data we want to load. The datafile usually ends with the .dat extension and contains data we want to load. Theata could be in several formats.

2) Create a control file. The control file tells the SQL*Loader how to map the data fields to an oracle table and specifies if the data needs to be transformed in some way. The control file usually ends with .ctl.

Note :

We can specify data file along with the control file for load. We can include datafile in the control file itself after we specify the load control information such as field specification and son. The data can be supplied in fixed-length fieldsor in free format seperated by characters such as comma(,)or a pipe().


Invoking SQL*Loader
We can invoke SQL*Loader utility in a numer of ways. the standard syntax for invoking SQL*Loader utility is as follows:

SQLLDR keyword=value [,keyword=value,......]

An example showing how to invoke SQL*Loader is as follows:

$sqlldr USERID=/ CONTROL= \
DATA=<.dat file path> LOG=<.log file path> ERRORS=0 DIRECT= \


Exploring The SQL*Loader Log File
The SQL*Loader logfile offers a host of information regarding a SQL*Loader run

It tells how many records were supposeo be loaded and how many are loaded actually.

It tells us which record fails to get loaded and why it failed

It describes the field columns provided in the SQL*Loader control file

When we examine the log file , we have to focus on total logical record reads and records that are skipped,rejected, and discarded

When we encounter difficulty running a job logfile is the first place we should look into to see whether or not the data records are being loaded


SQL*Loader Return Codes
The log file provides a wealth of information about the load, but oracle also allows us to trap the exit code after each load run. This enables us to check the results of the load when you run it through cron job or a shell script. for windows server we may use at command to schedule a load job.

Key exit codes for Unix/Linux OS are given below:

EX_SUCC /0 - Indicates that all the rows were loaded successfully

EX_FAIL/1 - Indicates that there were command-line or syntax errors

EX_WARN/2 - Indicates that some or all rows were rejected

EX_FTL/3 - Indicates Operating System errors


Reviews and Comments

PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016

About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy