Skip to Main Content
  • Questions

  • How to UPDATE a CLOB column with a file that resides on a different server than the database

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Gary.

Asked: November 13, 2019 - 3:36 pm UTC

Last updated: November 26, 2019 - 5:59 pm UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Greetings

Environment:

Oracle 12.1.0.2 on Exadata.

I have a table with a CLOB column in which I would like to store relatively small (< 20K bytes) TXT files. The rows contain other data and updating the CLOB would be an UPDATE after other processing.

The files exist on a different server from the database.

I was initially trying to use DBMS_LOB but that requires a database directory object to be created and as far as I can tell the directory can only reference locations on the database server.

I don't have access to the server.

We do not and cannot have database links.

I can't move the files as they contain sensitive information and are on a secure server.

What fun!!

What other methods exist to get the text files into the CLOB column when the files exist on a different server from the database?

Any suggestions are most welcome!!

Thanks in advance!

-gary


and Chris said...

If you want to read the files using PL/SQL in the database you need a directory object. Which means they must be accessible from the database server.

You can do this by mounting the remote location as a file system on the database server.

If "security" means you're not able to do this, you're kinda out of options for database-side PL/SQL.

AFAIK you'll have to build an application that has access to both servers.

Rating

  (2 ratings)

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

Comments

What about APEX?

Ramon Caballero, November 26, 2019 - 5:02 pm UTC

I guess APEX can be an option, right?
Like something similar to what is shown here: https://www.talkapex.com/2012/08/how-to-sendupload-clob-from-browser-to/
Chris Saxon
November 26, 2019 - 5:58 pm UTC

Possibly...

It really comes down to how the OP wants the process to work

...and what their security team allows them to do!

Not a C programmer but

Thomas Brotherton, November 26, 2019 - 5:39 pm UTC

I'm not a C programmer but would something like LOBDEMO from https://docs.oracle.com/cd/E18283_01/appdev.112/e10825/pc_agsmp.htm be a good starting point?
Chris Saxon
November 26, 2019 - 5:59 pm UTC

Neither am I ;)

You could write the application in any language you want really. As long as it has access to both the secure file and the database.
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library