Importing text file to Oracle table

We've been using SQL Server, and now we switched to Oracle. We have to import a text file (delimited with commas) to an Oracle table via our application. Our application tool is Microsoft .NET. Is there any way to import a text file in Oracle via coding?

I am a newbie for the Oracle platform; for the last five years we've been using SQL Server, and now we switched to Oracle. Coming to the point, we have an application in which we have to import a text file (delimited with commas) to an Oracle table via our application. Our application tool is Microsoft .NET. So is there any way to import a text file in Oracle via coding? Earlier we were using BCP (bulk insert utility) in SQL Server. Please suggest an alternative method. Thanks.

If you can place the text file on the database server, then you can use Oracle External Tables to load the data with a simple INSERT..SELECT statement. Assume that I have created an External Table called MY_EXT_TABLE, which is just a pointer to the text file, I can insert the data into MY_TABLE with the following command:

INSERT INTO my_table SELECT * FROM my_ext_table;

For more information on External Tables, read the Oracle Utilities Guide. Part III covers External Tables.

If the text file resides on the client side, then you'll have to install Oracle Client software on the workstation....

Step 2 of 2:

You forgot to provide an Email Address.

This email address doesn’t appear to be valid.

This email address is already registered. Please login.

You have exceeded the maximum character limit.

Please provide a Corporate E-mail Address.
- I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.
Please check the box if you want to proceed.
- I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.
Please check the box if you want to proceed.

This should be OK since you were using "bcp," which requires you to install SQL Server client tools on the workstation. Once you have Oracle Client on the workstation, you can use Oracle's utility for loading text files into a table. This utility is called SQL*Loader. The document I referenced above also covers SQL*Loader in Part II. SQL*Loader can also be used on the database server in lieu of External Tables.

Either solution does essentially the same thing, as both SQL*Loader and External Tables use the same engine to read the contents of the text file.

This was last published in August 2006

Dig Deeper on Oracle database design and architecture

All
News
Get Started
Evaluate
Manage
Problem Solve

Have a question for an expert?

Please add a title for your question

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

Meet all of our Oracle Database / Applications experts

View all Oracle Database / Applications questions and answers

SearchDataManagement

Related Resources

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Start the conversation

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

Please create a username to comment.