Q

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? 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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close