Insert Picture into Oracle BLOB

wittyme · Post by **wittyme** » Thu Jul 18, 2013 1:10 pm

Source is a text file with column Photo_Name which has string value for example as picture1.jpg

The image picture1.jpg is in the unix directory /a/b/c/images/

I have to load picture1.jpg into an oracle table column photo_image which is of data type blob.

I am using datastage 8.5

Any ideas on how to achieve this? I found online this can be done in oracle using bfile function.

rameshrr3 · Post by **rameshrr3** » Thu Jul 18, 2013 2:30 pm

You can create a stored procedure to do this load . Use the BFILENAME(<DirObjectName>,<PicFileName.jpg>)

1. create a directory object in oracle

Code: Select all

CREATE DIRECTORY "DIRNAME" AS '/a/b/c/images';

Use the stored proc to
2. Insert BFILE

Code: Select all

INSERT INTO YOUR_BLOB_TABLE VALUES
( v1,v2,BFILENAME('DIRNAME','picture1.jpg')

Call this stored proc from a datastage job and poll its return value.

wittyme · Post by **wittyme** » Thu Jul 18, 2013 2:45 pm

First question is should /a/b/c/images/ should be on the same server as oracle db is on?

The reason is, the Oracle database in on client server.

Client send images to me and I store in my unix box which is where datastage has access to.

If you can let me know what exactly the approach should be I can request client accordingly.

Second, I got the complete stored procedure online. So I should use the stored procedure stage to load the data into oracle?

rameshrr3 · Post by **rameshrr3** » Thu Jul 18, 2013 3:24 pm

Should be a directory accessible from the Oracle server as a local directory . I guess it can be NFS mapped also.

Otherwise FTP the *.jpg files from your datastage server path to the Oracle server Image directory path [ you may have to use Binary transfer mode for FTP] , and run the procedure in the next step using the Oracle server's local image directory being referred to by the directory object.
The FTP column definition i suppose should be of LongVarBinary datatype.

wittyme · Post by **wittyme** » Wed Jul 31, 2013 9:42 am

is there any other way using datastage?

In the oracle connector there is BFile function would that be helpful?

rameshrr3 · Post by **rameshrr3** » Wed Jul 31, 2013 11:45 am

I guess it will work , provided the column bfile operates on contains a valid DIRECTORY object as seen by oracle and file name.

wittyme · Post by **wittyme** » Thu Jun 12, 2014 4:17 pm

We had to use Java stage to make it work.

Thank you for all the feedback.