Skip to Main Content
  • Questions
  • Inserting a BLOB file into oracle using a pl/sql script

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Henry.

Asked: June 28, 2016 - 6:14 pm UTC

Answered by: Connor McDonald - Last updated: June 29, 2016 - 3:43 am UTC

Category: Developer - Version: Oracle Sql Developer Version 3.2.20.09

Viewed 10K+ times! This question is

You Asked

I cannot for the life of me figure out how to successfully insert a file into a blob column in a DB, using a PL/SQL script.
Should be able to reuse this script indefinitely, preferably, with several files at the same time, but as long as I can do one, I'll be fine with it.

I do not have permissions to create a directory, so something like this:
--!START
CREATE OR REPLACE DIRECTORY blobfile_dir AS 'C:\Users\wyatt\Desktop\test';
/
--!END


Will NOT work.


There has to be another way to insert a file, into a blob column without the need to create a directory and without having to manually add it into the column through SQL Developer. I'm just unable to figure out what that way might be.

Any ideas?

and we said...

If you can't create a directory, then options are limited, because obviously you need to *tell* the database is some way *where* the file is.

You could use SQLLDR to do it.

Examples here

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

Alternatively, if you're using Application Express you could upload files using the standard means, and then transfer them to your table.

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