Introduction of SQL LOADER
Introduction of SQL LOADER:-
-----------------------------
SQL LOADER is an Oracle utility used to load data from the given data-
file into table or tables created in the database. The data-file which has
the records those need to be loaded. SQL*Loader takes data file, as
well as a control file, to insert data into the table. When a Control file is
executed, it may create 3 files called log file, bad file, and discard
file.
Log file will be created for sure in any case (Pass or fail). Bad file would
only be created if any record validation fails and Discard file would be
created only WHEN condition fails (If Control file has the WHEN
condition).
Log file tells you the number of logical records already read from
the input data-file and the state of the tables and indexes. This
information can be used to resume the load where it left off.
bad file gives you the records that were rejected because of
formatting errors or because they caused Oracle/validation
errors .
discard file specifies the records that do not meet any of the
loading criteria like when any of the WHEN clauses specified in
the control file. These records differ from rejected records.
CONTROL file will have the path and name of the DATA-FILE. If both the
CONTROL and DATA files are present in the same directory then you do
not need to give any path of the data-file (Name of the file will be
enough).
SQL LOADER utility can be used from any Operating system like
Windows, UNIX or Linux etc.
Types of the data file structure:
They are two different types format structure of the data-file.
<!--[if !supportLists]-->1) <!--[endif]-->Fixed record format
<!--[if !supportLists]-->2) <!--[endif]-->Variable record format.
Variable record format
Variable record format:-
-------------------------
Variable Record Format would like below where the data fields are
separated by a delimiter. Note: The Delimiter can be anything you like.
In this case it is "|"
NAME1|0001|9999|08-JAN-08|10
NAME1112|0033|989|08-FEB-08|20
NAME1231|0021|9999|08-SEP-08|30
NAME45601|0041|9999|08-AUG-08|40
NAME23451|0051|9999|08-JUL-08|30
NAME56541|0023|9999|08-JAN-08|40
Structure of a Control file:
Sample CONTROL/CTL file for loading a variable record data file.
OPTIONS (SKIP = 2) --The first 2 rows will be skipped in data file.
LOAD DATA -- It’s kind of the BEGIN statement in CTL file.
INFILE '$FILE' -- Specify the data file path and name
APPEND -- Type of loading (INSERT, APPEND, REPLACE, TRUNCATE
INTO TABLE ‘Your Table Name’ -- the table to be loaded into
FIELDS TERMINATED BY '|' -- Symbol (|) which used to separate the
col’s
OPTIONALLY ENCLOSED BY '"' --Values of the data fields may be enclosed (")
TRAILING NULLCOLS -- columns that are not present in the record
treated as null
(COL1 “TRIM (:COL1)", -- Can use all SQL functions on columns
COL2 DECIMAL EXTERNAL,
COL3 DECIMAL EXTERNAL,
COL4 "TO_CHAR(:COL,'DD-MON-YY')", --Date format can be given
COL5 CONSTANT "AOT"
-- Can specify constant value instead of getting value from data-file
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)
LOAD DATA statement is required at the beginning of the control file.
INFILE: INFILE keyword is used to specify location and name of the
data-file.
specifies that the data is found in the control file and not in an
INFILE *
external file. You will find the data in the same CONTROL file.
INFILE '$FILE', can be used to send the file path and filename as a
parameter when registered as a concurrent program in the Oracle
Application.
INFILE '/home/demo/phani/[Link]' specifies the file-path and the
filename.
Example: - For the data-file is an external file.
LOAD DATA
INFILE ' /home/demo/phani/[Link]'
INTO TABLE XX_EMP
FIELDS TERMINATED BY ","
(NAME, ENUM, SAL, JDATE, DEPTNO)
Example: - For the data-file is in the Control file.
LOAD DATA
INFILE *
INTO TABLE xx_emp
FIELDS TERMINATED BY "|"
( NAME, ENUM, SAL, JDATE,DEPTNO )
BEGINDATA
PHANI|0001|9999|08-JAN-08|10
REDDY|0033|989|08-FEB-08|20
RAHUL|0021|9999|08-SEP-08|30
MANOJ|0041|9999|08-AUG-08|40
RANA|0051|9999|08-JUL-08|30
Example: - For the file name and path is sent as a parameter when
registered as a concurrent program
LOAD DATA
INFILE '$FILE'
INTO TABLE SCOTT.XX_EMP
FIELDS TERMINATED BY ","
(NAME, ENUM, SAL, JDATE, DEPTNO)
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.
INTO TABLE is required to identify the table to be loaded and in the
above example INTO TABLE "SCOTT"."XX_EMP", SCOTT refers to the
Schema and XX_EMP is the Table name.
Note: - The Schema name is not the mandatory. Since, when we run
the command to run the CTL file, we will provide the login information.
So, what ever the username we give in the command It will look for the
object in that schema. (Schema name is nothing but the Username you
are using to login into the database).
FIELDS TERMINATED BY specifies how the data fields are terminated
in the data-file. (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.
Command to run the SQL*Loader:-
sqlldr USERID='User_name'/'Password' CONTROL='Your control
filename' LOG='Log file name'
Example:- sqlldr USERID=scott/tiger CONTROL=[Link]
1) You provide the Username and password to connect to the
database.
2) Control file name should be given which you want to run. In the
above Example [Link] control file name.
3) Log file name can be given. So that when log file is create by the
command would create with same name as given. But this is not the
default parameter. If log name is not given then by default name would
be given by the system.
Fixed record format
Fixed record format:-
-------------------------
Fixed Record Format would look like the below. In this case you give a
specific position where the Control file can expect a data field.
1001 PHANI CLERK
1002 REDDY SALESMAN
1003 USER1 SALESMAN
1004 USER2 MANAGER
1005 USER3 SALESMAN
1006 USER4 MANAGER
1007 USER5 MANAGER
1008 USER6 ANALYST
1009 USER7 PRESIDENT
2010 USER8 SALESMAN
1011 USER9 CLERK
Structure of a Control file:
Sample CONTROL/CTL file for loading a Fixed record data file.
OPTIONS (SKIP = 2) --The first 2 rows will be skipped in data file.
LOAD DATA -- It’s kind of the BEGIN statement in CTL file.
INFILE '$FILE' -- Specify the data file path and name
APPEND -- Type of loading (INSERT, APPEND, REPLACE, TRUNCATE
INTO TABLE ‘Your Table Name’ -- the table to be loaded into
( COL1 POSITION(01:04) INTEGER EXTERNAL,
COL2 POSITION(06:10) CHAR,
COL3 POSITION(16:24) CHAR
)
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)
LOAD DATA statement is required at the beginning of the control file.
INFILE: INFILE keyword is used to specify location and name of the
data-file.
specifies that the data is found in the control file and not in an
INFILE *
external file. You will find the data in the same CONTROL file.
INFILE '$FILE', can be used to send the file path and filename as a
parameter when registered as a concurrent program in the Oracle
Application.
INFILE '/home/demo/phani/[Link]' specifies the file-path and the
filename.
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.
INTO TABLE is required to identify the table to be loaded.
Command to run the SQL*Loader:-
sqlldr USERID='User_name'/'Password' CONTROL='Your control
filename' LOG='Log file name'
Example:- sqlldr USERID=scott/tiger CONTROL=[Link]
1) You provide the Username and password to connect to the
database.
2) Control file name should be given which you want to run. In the
above Example [Link] control file name.
3) Log file name can be given. So that when log file is create by the
command would create with same name as given. But this is not the
default parameter. If log name is not given then by default name would
be given by the system.