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.
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.
INFILE * specifies
that the data is found in the control file and not in an 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/file01.txt’
specifies the file-path and the filename.
Example: – For the data-file is an external
file.
LOAD DATA
INFILE ‘
/home/demo/phani/file01.txt’
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=aottest.ctl
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 aottest.ctl 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.
2) Control file name should be given which you want to run. In the above Example aottest.ctl 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
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
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
)
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.
INFILE * specifies
that the data is found in the control file and not in an 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/file01.txt’ 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=aottest.ctl
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 aottest.ctl 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.
2) Control file name should be given which you want to run. In the above Example aottest.ctl 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.
Demo fixed script
It is always good to
run some script successfully and tries to learn more about it, features etc.
This page is design for the SQL*LOADER scripts for the Fixed format data-file.
Follow the steps
given below to run the fixed format data-file script.
1) Create one Folder
in your Operating System. Let the folder name ‘XYZ’
2) Download the
Data-File in the XYZ folder.
Click on the link
given to download the Data-File:
3) Download the
CONTROL in the XYZ folder.
Click on the Link
given to download the CONTROL file:
4) Download the
CREATE Table script in the XYZ Folder.
Click on the Link
given to Download the create table script:
5) Execute/Run the
Create Table script in your database to create the table.
6) Run the command to
run the Control file.
sqlldr
USERID=scott/tiger CONTROL=aottest.ctl
7) Verify the log
file created and also check if there is any bad or discard file created.
8) Query the table in
database where you have INSERTED the records
No comments:
Post a Comment