Page 1 of 1
Insert Picture into Oracle BLOB
Posted: Thu Jul 18, 2013 1:10 pm
by wittyme
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.
Posted: Thu Jul 18, 2013 2:30 pm
by rameshrr3
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.
Posted: Thu Jul 18, 2013 2:45 pm
by wittyme
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?
Posted: Thu Jul 18, 2013 3:24 pm
by rameshrr3
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.
Posted: Wed Jul 31, 2013 9:42 am
by wittyme
is there any other way using datastage?
In the oracle connector there is BFile function would that be helpful?
Posted: Wed Jul 31, 2013 11:45 am
by rameshrr3
I guess it will work , provided the column bfile operates on contains a valid DIRECTORY object as seen by oracle and file name.
Posted: Thu Jun 12, 2014 4:17 pm
by wittyme
We had to use Java stage to make it work.
Thank you for all the feedback.