Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Sunday, February 17, 2013

Overview on Oracle SQL*Loader


This article will give you a general overview of the syntax used in SQL*Loader. The article will not go into any detailed level of the syntax and the associated arguments/options to the SQL*Loader utility.
The command line syntax is comprised of the command itself (sqlldr), two mandatory arguments and several optional arguments. The arguments are specified as keywords assigned a value, e.g. LOG=<filename>. Below is a short description of some of the arguments, for an extensive and full description of all the keywords please read ch. 4 in the Oracle 9i Utilities Guide and ch. 6 in the Oracle 8i Utilities Guide.
Mandatory arguments:
username/password
CONTROL=<filename>
Optional arguments:
LOG=<filename> Specifies a log file
BAD=<filename> Specifies the file where all bad records are put
DATA=<filename> Specifies the data to be loaded into the database
DISCARD=<filename> Specifies where the discarded records go
ERRORS=<integer> Specifies number of errors allowed before termination
DIRECT=<TRUE|FALSE> Specifies if loading method is DIRECT or CONVENTIONAL
PARFILE=<filename> Specifies the parameter file to be used by SQL*Loader
If the username/password argument is not specified, you will be prompted for it if there is a 2nd argument on the command line. If there are no arguments at all on the command line, e.g. sqlldr , you will get the help page for SQL*Loader. Here you can see what options are available to you.
All the arguments can be supplied on the command line, but they can also be put into a parameter file, which will be read by SQL*Loader if the PARFILE argument is used. If SQL*Loader is not supplied with a username/password or a control file, you will be prompted for the missing information. Any other argument can be left out and will take a default value. The are more arguments available and they can be listed by running the sqlldr command without any arguments. For a more detailed description of all arguments, please refer to the Utilities Guide.
The control file contains further parameters used by SQL*Loader to load the data. These are described in the Utilities Guide.
Example:
——–
sqlldr scott/tiger control=emp.ctl log=emp.txt direct=true
This example will load data into the database using the DIRECT method. Furthermore, the data will be loaded into the schema of SCOTT. The control file emp.ctl will hold all the other directives (arguments) needed by SQL*Loader to
load the data into the proper table(s).

No comments:

Post a Comment