Vishal Gupta's Blog

Loading File into a Blob

Posted by Vishal Gupta on Sep 23, 2008


Yesterday I received a request from a developer to load file into a BLOB. To be honest I have never loaded a file into a BLOB in past. I had some idea that I would have to use DBMS_LOB pl/sql package to achieve this.

Here are the steps to do this.

1. Create an oracle directory object.

create directory tmp as ‘/tmp’;


2. Load the file into BLOB


l_blob BLOB;

l_bfile BFILE;

l_offset_dest INTEGER :=1;

l_offset_src INTEGER :=1;


/* Get a BFILE pointer to OS file. */


INTO l_bfile

FROM dual;

/* Open the BFILE */


/* Initialize the BLOB */



,src_bfile => l_bfile


,dest_offset => l_offset_dest

,src_offset => l_offset_src


update table1

set col1 = l_blob;


/* Close the BFILE */





5 Responses to “Loading File into a Blob”

  1. Gus Spier said

    What is the ddl for table1? Is this a table with a single column of type BLOB?

  2. Naeeym said

    How do I put the FILE in the TMP directory of the Server from a Client?? As I don’t want the Client to have any direct share to Server Directory..

    I don’t want to user to Upload the file to the server from their client machine, and ask somebody else to load it from there.

    I want the user to insert directly into the BLOB….

    Is there any way to use this Procedure from the Client??

    I tried, but it seems Oracle does not support client call to DBMS_LOB.

  3. Gus,

    table1 is just an example. It can be any table with a BLOB column. Lets say it has following definition.

    CREATE TABLE table1
    (COL1 BLOB);

  4. Nayeem,

    Example above was more directed towards DBAs. And dba would normally have access to server. Its using oracle directory object which is a directory on server.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: