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


DECLARE

l_blob BLOB;

l_bfile BFILE;

l_offset_dest INTEGER :=1;

l_offset_src INTEGER :=1;

BEGIN

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

SELECT bfilename (‘TMP’,’CLIENT_CUST_BLEUPRINT.xml’)

INTO l_bfile

FROM dual;

/* Open the BFILE */

DBMS_LOB.FILEOPEN(l_bfile);

/* Initialize the BLOB */

DBMS_LOB.CREATETEMPORARY(l_blob,TRUE);

DBMS_LOB.LOADBLOBFROMFILE(dest_lob => l_blob

,src_bfile => l_bfile

,amount => DBMS_LOB.LOBMAXSIZE

,dest_offset => l_offset_dest

,src_offset => l_offset_src

);

update table1

set col1 = l_blob;

commit;

/* Close the BFILE */

dbms_lob.FILECLOSE(l_bfile);

end;

/

 

About these ads

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:

WordPress.com Logo

You are commenting using your WordPress.com 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: