This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Oracle function using Java to return Blob is zero size Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle function using Java to return Blob is zero size" Watch "Oracle function using Java to return Blob is zero size" New topic

Oracle function using Java to return Blob is zero size

Colm Dickson
Ranch Hand

Joined: Apr 04, 2009
Posts: 89
Hi all.

I have a method in Java that takes a File object, stream it into a Blob column in the database and works fine. I then can fetch this blob and return it out of the ResultSet and pass it back out as per the method specification.

My issue is when I try to wrap this in an Oracle function whcih returns a blob. I call the java method I have just mentioned but where the Java method returns a valid blob with a given size in bytes(non zero), the Oracle function that calls this gives assigns a zero sized blob to the blob variable in the database.

I have tried initialising the oracle blob variable to an empty_blob and then assigning this to the outcome of the oracle function but it is still a zero size blob. Does anyoen know if this is possible? Can a java method that returns a blob be used by Oracle to assign this blob to an Oracle blob variable?


Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3703

Blobs, especially in Oracle, can be extremely frustrating to use. While I can't answer your question directly, I do have an alternative suggestion. In your function, return an ID (key) that uniquely identifies the row the blob resides in. Then, add a database index for this ID if one does not already exist. After your function returns, use the ID to pull the Blob in a simple SELECT statement.

Normally, taking a single function call and splitting it into multiple successive calls is not a great practice, but this is an exception to the rule since there are Blobs involved. With large enough blobs, the driver is going multiple calls as you iterate through the result set, since it can't load too many rows at once. Also, some drivers handle blobs as separate calls anyway, so the performance is the same regardless. In essence, you're just explicitly doing what the driver could do anyway.

My Blog: Down Home Country Coding with Scott Selikoff
I agree. Here's the link:
subject: Oracle function using Java to return Blob is zero size
Similar Threads
unsupported LOB (calling Java from Oracle)
How to store & retrieve a video file from database?
How to retreive a blob....
Image insertion into database
NullPointerException while trying to get BLOB object