Skip to Main Content
  • Questions

  • UTL_FILE or EXTERNAL Table operation on files in Application Server

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, karthikeyan.

Asked: November 27, 2017 - 10:03 pm UTC

Last updated: November 29, 2017 - 1:42 pm UTC

Version: R12.2.6

Viewed 1000+ times

You Asked

Hi Tom,

We are running Oracle EBS R12.2.6.

We have application server and database server hosted on two separate physical servers. They are not having any shared file locations.

We are receiving the inbound files from a third party system into our Application server file system.

We want to use UTL_FILE or External Table to do the file operations(read file, load file, validate the contents etc..).

Unfortunately, we can not use application server path to create a DBA DIRECTORY and use it for UTL file operation or External Table.

Can you please suggest what is the best recommended way to work with file operations(except using SQL LOADER) in an architecture like these where application server and database servers are two different file system( Physical Server).

Is it recommended to receive the files SFTPed by third party system into the Database Server instead of Application Server?

Please help.

Thanks.

and Chris said...

If you want to read files using UTL_file or external tables, they have to be accessible on the database server. So you need a process to copy them there.

Personally I prefer external tables. They allow you to manipulate the file records using SQL, often enabling you to load them straight into the target tables with:

insert into ... select ... from ext_tab

If you can't do this and you've ruled out SQL*Loader, your options are rather limited...

Rating

  (2 ratings)

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

Comments

Try server mounting

Sunil, November 28, 2017 - 4:06 pm UTC

Instead of copying the files to database servers which may not always get security approval , we can use mount option to access files on application servers.
Chris Saxon
November 29, 2017 - 1:40 pm UTC

As the comment below says, mounting brings its own security issues...

Persistent connection is less secure

pmdba, November 29, 2017 - 3:47 am UTC

A persistent connection like a file share or NFS mount is less secure than a scheduled file copy using scp or SFTP. Persistent connections can more easily be tampered with or hacked. Also, if something went wrong with the mount, it could cause a problem like an OS lockup with the database server (speaking from experience). If you schedule the copy process to originate from the database server then you aren't exposing any more services to the network than you had before. Just let the database server take advantage of the same service already provided by the application server to get the files in the first place.
Chris Saxon
November 29, 2017 - 1:42 pm UTC

Yep, mounting brings extra complexity.
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.