What is SQL Loader with Examples
What is SQL Loader
SQL
LOADER is an Oracle utility used to load data into table given a
datafile which has the records that 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 can create Three (3) files called a
log file, bad file or reject file, discard file.
- Log file tells you the state of the tables and indexes and the number of logical records already read from the input datafile. This information can be used to resume the load where it left off.
- Bad file or reject file gives you the records that were rejected because of formatting errors or because they caused Oracle 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.
What is Structure of the data file:
The data file can be in fixed record format or variable 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:
7369 SMITH CLERK 7902 12/17/1980 800
7499 ALLEN SALESMAN 7698 2/20/1981 1600
7521 WARD SALESMAN 7698 2/22/1981 1250
7566 JONES MANAGER 7839 4/2/1981 2975
7654 MARTIN SALESMAN 7698 9/28/1981 1250
7698 BLAKE MANAGER 7839 5/1/1981 2850
7782 CLARK MANAGER 7839 6/9/1981 2450
7788 SCOTT ANALYST 7566 12/9/1982 3000
7839 KING PRESIDENT 11/17/1981 5000
7844 TURNER SALESMAN 7698 9/8/1981 1500
7876 ADAMS CLERK 7788 1/12/1983 1100
7900 JAMES CLERK 7698 12/3/1981 950
7902 FORD ANALYST 7566 12/3/1981 3000
7934 MILLER CLERK 7782 1/23/1982 1300
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 "|"
1196700|9|0|692.64
1378901|2|3900|488.62
1418700|2|2320|467.92
1418702|14|8740|4056.36
1499100|1|0|3.68
1632800|3|0|1866.66
1632900|1|70|12.64
1637600|50|0|755.5
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
)
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 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.
Example where datafile is an external file:
LOAD DATA
INFILE '/home/vision/kap/import2.csv'
INTO TABLE kap_emp
FIELDS TERMINATED BY ","
( emp_num, emp_name, department_num, department_name )
Example where datafile is in the Control file:
LOAD DATA
INFILE *
INTO TABLE kap_emp
FIELDS TERMINATED BY ","
( emp_num, emp_name, department_num, department_name )
BEGINDATA
7369,SMITH,7902,Accounting
7499,ALLEN,7698,Sales
7521,WARD,7698,Accounting
7566,JONES,7839,Sales
7654,MARTIN,7698,Accounting
Example where file name and path is sent as a parameter when registered as a concurrent program
LOAD DATA
INFILE '$FILE'
INTO TABLE kap_emp
FIELDS TERMINATED BY ","
( emp_num, emp_name, department_num, department_name )
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 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.
How to Loading a fixed format data file:
LOAD DATA
INFILE 'sample.dat'
INTO TABLE emp
( empno POSITION(01:04) INTEGER EXTERNAL,
ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR,
mgr POSITION(27:30) INTEGER EXTERNAL,
sal POSITION(32:39) DECIMAL EXTERNAL,
comm POSITION(41:48) DECIMAL EXTERNAL,
deptno POSITION(50:51) INTEGER EXTERNAL)
What are 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.
How to 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.
How to Skip columns:
You can skip columns using the 'FILLER' option.
Load Data
--
--
--
TRAILING NULLCOLS
(
name Filler,
Empno ,
sal
)
--
--
--
TRAILING NULLCOLS
(
name Filler,
Empno ,
sal
)
here the column name will be skipped.
Mar 29, 2010
Payables Open Interface's Control File
load data infile 'AP_INV.TXT'
append into table AP_INVOICES_INTERFACE
fields terminated by '^' optionally enclosed by '"'
trailing nullcols
(INVOICE_NUM CHAR "RTRIM(:INVOICE_NUM)",
INVOICE_TYPE_LOOKUP_CODE CHAR "RTRIM(:INVOICE_TYPE_LOOKUP_CODE)",
INVOICE_DATE DATE "DD-MON-RRRR",
VENDOR_NUM CHAR "RTRIM(:VENDOR_NUM)",
VENDOR_SITE_CODE FILLER,
INVOICE_AMOUNT CHAR "RTRIM(:INVOICE_AMOUNT)",
TERMS_NAME CHAR "RTRIM(:TERMS_NAME)",
DESCRIPTION CHAR "RTRIM(:DESCRIPTION)",
DUMMY1 FILLER,
DUMMY2 FILLER,
DUMMY3 FILLER,
DUMMY4 FILLER,
GL_DATE DATE "TO_DATE((TO_CHAR(:GL_DATE,'DD-')||DECODE(TO_CHAR(:GL_DATE,'MON'),'AUG','SEP','SEP','SEP','OCT','OCT',TO_CHAR(:GL_DATE,'MON'))||TO_CHAR(:GL_DATE,'-YYYY')))",
VOUCHER_NUM CHAR "RTRIM(:VOUCHER_NUM)",
INVOICE_RECEIVED_DATE DATE,
AMOUNT_APPLICABLE_TO_DISCOUNT,
TERMS_DATE DATE,
SOURCE CONSTANT 'MAXCIM INVOICE',
PAYMENT_CURRENCY_CODE CONSTANT 'USD',
PAYMENT_METHOD_LOOKUP_CODE CONSTANT 'CHECK',
CALC_TAX_DURING_IMPORT_FLAG CONSTANT 'N',
ORG_ID CONSTANT '166',
SHIP_TO_LOCATION CONSTANT 'Materials Warehouse',
INVOICE_CURRENCY_CODE CONSTANT 'USD',
creation_date SYSDATE,
last_updated_by CONSTANT 1093,
last_update_date SYSDATE,
created_by CONSTANT 1093,
INVOICE_ID "AP_INVOICES_INTERFACE_S.NEXTVAL")
load data infile 'AP_LINE_TEST.TXT'
append into table AP_INVOICE_LINES_INTERFACE
fields terminated by '^' optionally enclosed by '"'
trailing nullcols
(REFERENCE_2 CHAR "RTRIM(:REFERENCE_2)",
TYPE FILLER,
ACCOUNTING_DATE DATE "DD-MON-RRRR",
DESCRIPTION CHAR "RTRIM(:DESCRIPTION)",
DIST_CODE_COMBINATION_ID CHAR "RTRIM(:DIST_CODE_COMBINATION_ID)",
LAST_UPDATED_BY CHAR "RTRIM(:LAST_UPDATED_BY)",
AMOUNT CHAR "RTRIM(:AMOUNT)",
CREATED_BY CHAR "RTRIM(:CREATED_BY)",
CREATION_DATE DATE "DD-MON-RRRR",
ATTRIBUTE1 CHAR "RTRIM(:ATTRIBUTE1)",
ATTRIBUTE2 CHAR "RTRIM(:ATTRIBUTE2)",
ATTRIBUTE3 CHAR "RTRIM(:ATTRIBUTE3)",
TYPE_1099 CHAR "RTRIM(:TYPE_1099)",
UNIT_OF_MEAS_LOOKUP_CODE CHAR "RTRIM(:UNIT_OF_MEAS_LOOKUP_CODE)",
TAX_RATE CHAR "RTRIM(:TAX_RATE)",
QUANTITY_INVOICED CHAR "RTRIM(:QUANTITY_INVOICED)",
UNIT_PRICE CHAR "RTRIM(:UNIT_PRICE)",
INVOICE_ID "AP_INVOICE_LINES_INTERFACE_S.NEXTVAL")
append into table AP_INVOICES_INTERFACE
fields terminated by '^' optionally enclosed by '"'
trailing nullcols
(INVOICE_NUM CHAR "RTRIM(:INVOICE_NUM)",
INVOICE_TYPE_LOOKUP_CODE CHAR "RTRIM(:INVOICE_TYPE_LOOKUP_CODE)",
INVOICE_DATE DATE "DD-MON-RRRR",
VENDOR_NUM CHAR "RTRIM(:VENDOR_NUM)",
VENDOR_SITE_CODE FILLER,
INVOICE_AMOUNT CHAR "RTRIM(:INVOICE_AMOUNT)",
TERMS_NAME CHAR "RTRIM(:TERMS_NAME)",
DESCRIPTION CHAR "RTRIM(:DESCRIPTION)",
DUMMY1 FILLER,
DUMMY2 FILLER,
DUMMY3 FILLER,
DUMMY4 FILLER,
GL_DATE DATE "TO_DATE((TO_CHAR(:GL_DATE,'DD-')||DECODE(TO_CHAR(:GL_DATE,'MON'),'AUG','SEP','SEP','SEP','OCT','OCT',TO_CHAR(:GL_DATE,'MON'))||TO_CHAR(:GL_DATE,'-YYYY')))",
VOUCHER_NUM CHAR "RTRIM(:VOUCHER_NUM)",
INVOICE_RECEIVED_DATE DATE,
AMOUNT_APPLICABLE_TO_DISCOUNT,
TERMS_DATE DATE,
SOURCE CONSTANT 'MAXCIM INVOICE',
PAYMENT_CURRENCY_CODE CONSTANT 'USD',
PAYMENT_METHOD_LOOKUP_CODE CONSTANT 'CHECK',
CALC_TAX_DURING_IMPORT_FLAG CONSTANT 'N',
ORG_ID CONSTANT '166',
SHIP_TO_LOCATION CONSTANT 'Materials Warehouse',
INVOICE_CURRENCY_CODE CONSTANT 'USD',
creation_date SYSDATE,
last_updated_by CONSTANT 1093,
last_update_date SYSDATE,
created_by CONSTANT 1093,
INVOICE_ID "AP_INVOICES_INTERFACE_S.NEXTVAL")
load data infile 'AP_LINE_TEST.TXT'
append into table AP_INVOICE_LINES_INTERFACE
fields terminated by '^' optionally enclosed by '"'
trailing nullcols
(REFERENCE_2 CHAR "RTRIM(:REFERENCE_2)",
TYPE FILLER,
ACCOUNTING_DATE DATE "DD-MON-RRRR",
DESCRIPTION CHAR "RTRIM(:DESCRIPTION)",
DIST_CODE_COMBINATION_ID CHAR "RTRIM(:DIST_CODE_COMBINATION_ID)",
LAST_UPDATED_BY CHAR "RTRIM(:LAST_UPDATED_BY)",
AMOUNT CHAR "RTRIM(:AMOUNT)",
CREATED_BY CHAR "RTRIM(:CREATED_BY)",
CREATION_DATE DATE "DD-MON-RRRR",
ATTRIBUTE1 CHAR "RTRIM(:ATTRIBUTE1)",
ATTRIBUTE2 CHAR "RTRIM(:ATTRIBUTE2)",
ATTRIBUTE3 CHAR "RTRIM(:ATTRIBUTE3)",
TYPE_1099 CHAR "RTRIM(:TYPE_1099)",
UNIT_OF_MEAS_LOOKUP_CODE CHAR "RTRIM(:UNIT_OF_MEAS_LOOKUP_CODE)",
TAX_RATE CHAR "RTRIM(:TAX_RATE)",
QUANTITY_INVOICED CHAR "RTRIM(:QUANTITY_INVOICED)",
UNIT_PRICE CHAR "RTRIM(:UNIT_PRICE)",
INVOICE_ID "AP_INVOICE_LINES_INTERFACE_S.NEXTVAL")
Jul 26, 2008
What is 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.
Which files in SQL*loader?
1. Flat or Data File
2. Control File
3. Bad File
4. Discard File
5. Log File
What is 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).
What is 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.
SQL* Loader Modes:
INSERT
APPEND
REPLACE
We can replaced the data in to the table by using any one of the allowing method
INSERT: When we are using this statement, table should be empty. SQL * loader will insert the new data form the file.
APPEND: This mode will be use to attach the new record to the existing records.
REPLACE: This will replace the existing records with new records.
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
SQL*loader is one of the Oracle tool which will be used to transfer the data from Flat-File to oracle Database table.
Which files in SQL*loader?
1. Flat or Data File
2. Control File
3. Bad File
4. Discard File
5. Log File
What is 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).
What is 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.
SQL* Loader Modes:
INSERT
APPEND
REPLACE
We can replaced the data in to the table by using any one of the allowing method
INSERT: When we are using this statement, table should be empty. SQL * loader will insert the new data form the file.
APPEND: This mode will be use to attach the new record to the existing records.
REPLACE: This will replace the existing records with new records.
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
Please post your Comments
No comments:
Post a Comment