Sql Loader is the oracle utility to load data into oracle database from external source datafile specified in specific format. The different types of files comes into being during the data load.
Parameter file : specifies the parameters neccesary to invoke sql loader utility. This may contain the oracle user id and password needs to connect to database, placement of the control file need to parse data and placement of the log files
during the load. Different options may be specified in this file during load such as rows of data after which to commit, disable the archiving option , parallel load option, conventional and direct path load etc.
Control file : specifies the format of the data to be loaded and conditions necessary to load data during the load. The data can be replaced,appended, inserted, truncated in the table. Sql loader does not create table and load into the table. INFILE specifies the source data file whereas DISCARDFILE specifies logs of the rejected rows during the load.The method of field segregation is specified by keyword FIELD TERMINATED BY. Source data may be included in the control file itself using begindata option.
Data file : name of the source data file that contains the data to load as format specified in the control file.
Log file : name of log file to log the result of log during the load.
Discard file : name of file to log the rejected rows during the load.Data can be loaded into the database in two ways:
Direct : This option in parameter file load the data from memory directly into the oracle blocks without generation of the redologs. Direct path loads cannot be used in clustered tables.
Conventional : uses sql insert statement to load data. Generation of redo log, archive files, execution of triggers are the characteristic of conventional path load.
Below is the sample of sql load from the excel file saved in csv format:
Parameter file contents:
userid=username/password
control=c:\myData.ctl
errors=10
log=c:\myData.log
load=1000
Control file contents:
LOAD DATA
INFILE 'c:\myData.csv'
DISCARDFILE 'c:\myData.dsc'
append --REPLACE , APPEND, TRUNCATE
INTO TABLE myData
-- PARTITION (Partition_table_name)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(v_name, v_acct)
Sql loader can be invoked from command prompt as:
sqlldr parfile=c:\myData.par
Check the logfiles during the load
3 comments:
Nice fill someone in on and this mail helped me alot in my college assignement. Gratefulness you on your information.
Genial dispatch and this mail helped me alot in my college assignement. Say thank you you as your information.
Costume jewellery is becoming more popular as the buying price of rare metal increases. A number of the products are very gorgeous even though they are certainly not made of high-priced factors. You can find businesses which make and sell only costume jewelry, plus they are very effective, probably a result of golden and precious stones being extremely high valued now. [url=http://www.ss12w12ws.info]Inas665much[/url]
Post a Comment