Ask the Expert

Importing data from text file to table in 9i

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?

    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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: