2023 2024 Student Forum > Management Forum > Main Forum

 
  #1  
22nd August 2014, 11:58 AM
Unregistered
Guest
 
Erpschools SQL Loader

Give me the information about the SQL Loader of Erpschools as soon as possible.
  #2  
22nd August 2014, 01:38 PM
Super Moderator
 
Join Date: Apr 2013
Re: Erpschools SQL Loader

SQL*loader is one of the Oracle tool which is used to transfer the data from Flat-File to oracle Database table.

We can find the following files in SQL*loader
1. Flat or Data File
2. Control File
3. Bad File
4. Discard File
5. Log File

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)

Keywords:
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.
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.
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)
SQL* Loader Modes:
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.
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
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

Contact:
India Phone Number: +91 9000 333 598

USA Phone Number: +1 (847)-857-7655


Quick Reply
Your Username: Click here to log in

Message:
Options

Thread Tools Search this Thread



All times are GMT +5. The time now is 03:10 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
SEO by vBSEO 3.6.0 PL2

1 2 3 4