I have to import data from a text file to a table in Oracle9i. Could you please explain in steps how to mount the
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.