I have to import data from a text file to a table in Oracle9i. Could you please explain in steps how to mount the...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
file and how to import data into the table? 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.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.