Writing from a BLOB to a FIle on the Server - OO4O and Wizards(Archived)

I need to be able to save the data from a BLOB column to an external file on the server, preferably in a temporary directory. Afterwards I will add a header to the file and send to the web client. I believe I know how to add the header. However, everything I have read in DBMS_LOB or UTIL_FILE describes saving a BFILE or CLOB to a BLOB, but not the reverse. Can you save from a BLOB to an external file on the server? The processing must be done on the Server.
I have been able to select TEXT from a VARCHAR column and save to a file on the server but unable to select a BLOB and save to the file.
Thank you for your help,

UTL_FILE would be the correct thing to use, but I seem to recall that it didn't work for binary files. It apparently adds CR LF data into the files when written. I am not sure if this has changed recently or not. You might try posting in the database or PL/SQL forum about UTL_FILE.


Reading and Writing a Blob using ODP.NET.

How can I write an image as a Blob (not a bfile) using ODP.NET? How can I get this image back again? I want to use c# to call procs in Oracle to do the work. 
Think I have worked out how to do this (see link) ... but have a further question below.
However when I stream the the image to a folder the creation date has been reset. What I would really like to do, is maintain the creation date of the original image which was stored as a Blob in Oracle. (I don't want to do this by storing all the details of the image in a table ... there must be some way of getting the original image creation date from the Blob).
Does ANYONE know how to do this??? 
Are we talking JPGs here? If so, the creation date is generally stored inside the image itself, perhaps this helps:
Other than that, I'm not sure there is such a thing as an "original image creation date" of a BLOB. Those are file system properties, not properties of the raw data itself, which is what the BLOB is. If you want to store the file system property along with the blob itself, you'd need to store that externally.
Hope it helps,

Php Query

Hi everyone,
I am using zend framework(PHP) and oracle database.
I'm downloading blob data from the database, but i'm not able to get the actual downloading file name.
Please show me how to go about it
Hmmm, well if I remember correctly ( and I may not ) the file name of your blob is not stored since it is not part of the blob itself.
My typical approach to storing blobs is to have the table contain the file and path name and any other particulars in the table.
What Zend might have going on I do not know but from just a raw PHP standpoint the code for capturing a blob does not entertain such things as file names, it simply stores binary data which could be anything from a file to a memory core dump or a memory stream.

Converting an image to a Thumbnail

Is there a way, using the ORDSYS.ORDIMAGE datatype, to copy an image into a thumbnail during a bulk load, and load both the original image AND the thumbnail into the database at the same time?
What environmet are you working in? PL/SQL, SQL Loader?
In general, the original image is loaded into the database using PL/SQL, and a thumbnail is produced using the processcopy method into another image data type. Why would you prefer to scale the image outside of the database and then load them there? I don't think there would be any performance to be gained, probably the opposite since files would have to be created, opened, closed deleted etc.
I hadn't decided yet between pl/sql and sql loader. I have text files being supplied with each folder holding the images. The text files contain a listing of every image in the folder along with some other attributes. I could easily use either method to load the data.
I have at least 2400 tables (a table for every route in the state of New Mexico) holding images with a total of 500 GB worth of jpegs. I'm trying to avoid having to convert the images to thumbnails on all of the different tables after the data loads. I was wondering today if I could set up a trigger on each table so that after the image is initially inserted, the trigger would use the processcopy() method to produce the thumbnail. 
I guess I understand a little better.
I would use PL/SQL if I were you since it is more flexible, and for images there is not a noticable perfomance between the two.
I don't know why you would want a trigger. The general PL/SQL procedure used in most of these cases:
For every row to be inserted
Load data and image into row.
Use processCopy to create thumbnail from original image.
end for;
You would not need a procedure that first creates the rows and then creates the thumbnails.
Since there is are restrictions on using new LOB contents in a trigger, I doubt a trigger would work.
You can take the load example and add code to create the thumbnail at the same tim ethe image is created.
If the same thumbnail may be used in different tables, you can have it stored in one lob and out the same lob locator in different tables.
I've set up my tablespaces for the thumbnails and the full size images. I've established a procedure to install a file and convert it into a thumbnail. My problem now is this: for each folder of images I have a text file that contains all of the file names and another detail column of each image in the folder, comma delimited. How can I pass this file to the PL/SQL procedure to load and loop through the images? 
You can use the utl_file package to read the text file and use the SQL string functions to parse the lines in the file (INSTR, SUBSTR...). Make sure you read the security section. You will need to create a directory and grant access to the directory to the DB user of the directory. Loop though all the data while inserting the indicated images.
First, thanks for all of your advice.
I have the procedures working to insert the image and the thumbnail. I'm getting what seems to be a common error using the UTL_FILE package, ORA-06512/ORA-06510. Any suggestions. The Directory is RFI_IMAGES and it has been granted to public. I am running the package server side and the images are on the V:\ local drive of the server. 
Could you do a show errors and show us the error stack? The error numbers you have given are extremly general. There should be more information in the text.
A common mistake is that directory names are case sensitive, and when creating the directory, if the directory name is not in quotes, it is uppercased.
Create Directory foo...
Creates directory 'FOO'
So you have to use an uppercased 'FOO' to accesss the directory 
I've got everything working now, using UTL_FILE. The images are being imported properly and copying to a Thumbnail. I have to open a file that has a listing of all text files in the directory and then open each of file in that list one by one to get the image names and their attritubes. I was making a mistake with my string parsing and when I fixed that everything worked fine (Although, loading 30,000 jpegs(100KB each) at a time takes a little while!! Only about 3,000,000 to go.).
Thanks for all the help Larry.

audio blob vs. audio bfile storage

I stored real audio content in the database. This all goes fine. I can then retrieve the songs from the database, using realserver and the oracle/real plugin or the intermedia clipboard.
Next, I tried to insert audio content in the database with a bfile (so the content is not stored in the database, just a pointer). However, I cannot succeed in this. I do the insert with a pl/sql procedure. The (snippet)code is as follows:
insert into tracks t
It looks as the insert goes fine (no errors), but when I try to retrieve the song realserver/plugin cannot find it. I also tried to retrieve the song using the clipboard. The songs, I stored as a blob inside the database, I can retrieve, but I cannot retrieve the songs that are stored as a bfile. (Therefore, I don't think it's a realserver problem).
I also tried to do the insert with the method setProperties(ctx). This won't work, because the mimetype for real is not supported. [I don't think I really need this method, cause I don't use it when I store the audio as a blob in the database, which works fine].
I followed all the available sample code, but nothing works when working with a bfile. Again, working with blob's is no problem.
Hopefully, someone can help me out!
I don't know whether or not the Real server plug-in supports BFILEs.
However, the interMedia Clipboad and Web Agent certainly do. If the
Clipboard can successfully play Real audio retrieved from a BLOB but
not from a BFILE, then I suspect there is something amiss with either
the database directory specification, or the naming of the directory
and/or file name for the BFILE.
Can you provide the error displayed by the Clipboard when you try
this, plus the correspoding entry from the Web Agent's log file. The
Web Agent's log file is in <oracle-home>\ord\web\logs\wscnnnn.log.
Thanks for your reply!
The intermedia clipboard can successfully retrieve my bfile. That means that the track is inserted correctly as a bfile. So I think the problem can only be the Oracle/real server plugin.
interMedia has full support for BLOBs that are stored locally, and BFILE support is
supported at a secondary level. This is simply due to the nature of the built in
support that Oracle itself provides. As a work around, you could use the method,
getContentInLOB(), copying the data into a temporary LOB. The example with
the documentation shows how to do just this.
As a word of warning, if the videos are very large, or this is a high throughput
video server application, this work around will probably not be acceptable for
performance reasons.


Hi there,
Can someone help us with uploading files (pdf, word etc) from a jsp into BFILE columns in Oracle Database. Also we would like to display such files from BFILE columns in another JSP page.
Irshad Buchh. 
Check the sample on manipulating BFile type column in database through JDBC at following url.
When you use BFile type, the file is not actually stored in the database. It is only the locator that is stored in the database. The file resides external to the database on the file system and should be present on the machine where database server is installed. You cannot update such a file using database operation on BFile type. You can only modify or change the locator in the BFile type.
Thanks for the reply. Please point me to an example of a file (doc, pdf) download (display) using a JSP . Also would it be better to store the files in the database using BLOB or BFILE colums? Can you also point us to a web site that is using such a technique.