Requires Free Membership to View
Oracle uses a tool called SQL*Loader to load a text file into an Oracle
table. First, you'll have to create a table in the database. Then,
you'll have to create a control file telling SQL*Loader how to map the
data in the text file to the table. A simple control file might look as
follows:
LOAD DATA INFILE 'ITS_data.txt' BADFILE 'ITS_data.bad' DISCARDFILE 'ITS_data.dsc' APPEND INTO TABLE pims_temp FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (wbs_parent,wbs,ffs_account,ffs_account_desc,ffs_subtask_desc, rescode,resource_name,resdept,type,hrs_dlrs_ind,)
The information above would be saved in a text file. It basically says that the input file is "ITS_data.txt". All records that are "bad" are placed into the bad file and discarded records into the discard file. The data will be appended into table PIMS_TEMP. The data in the text file is a comma-delimited file with each field optionally enclosed in double quotes. The fields in the text file then map to the columns in the table in the order shown. Then SQL*Loader is invoked as follows:
sqlldr userid=system/manager control=file.ctl
where "file.ctl" is the text control file similar to above.
There are many more options for SQL*Loader that you can find fully
documented in the Oracle Utilities Guide. Please refer to this document
for more information.
This was first published in June 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation