Skip to Main Content
  • Questions

  • Insert a text file into oracle table , the file is in oracle directory

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Devsena.

Asked: October 18, 2017 - 10:31 am UTC

Last updated: October 23, 2017 - 10:36 am UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

I want to insert a text file into a table . But the file is in oracle directory something like /tmp/test.txt ,
Is it possible , a procedure with a parameter which will take the file path as parameter and fetch the file from server and copy it to the database table

and Chris said...

There are several methods for reading files into the database, including:

- An external table
- SQL*Loader

Tom contrasts these at:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6611962171229

Or you can read more about these in the docs:

http://docs.oracle.com/database/122/CNCPT/tables-and-table-clusters.htm#CNCPT1141
http://docs.oracle.com/database/122/SUTIL/oracle-sql-loader-concepts.htm

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

My question was different

Devsena Chakravarty, October 23, 2017 - 6:09 am UTC

I want to insert the file as a CLOB in a table , ( the table has a column with datatype CLOB) .

I will use this table to fetch the .txt file later in a OAF page .

Its like i will pass the path of the file like /tmp/test.txt and then the procedure will fetch the file and insert it as a CLOB or BLOB into a table ..

The solution you provide is like data will be read from the file and then insert the data using SQL* Loader .
Chris Saxon
October 23, 2017 - 10:36 am UTC

You mean you want to store the file, as-is in the database, not parse its contents?

In that case you can use BFILEs to point to the file and load it as needed:

https://oracle-base.com/articles/8i/import-blob
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.