File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases 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
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
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: 3753

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.

[OCA 8 Book] [Blog]
I agree. Here's the link:
subject: Oracle function using Java to return Blob is zero size
It's not a secret anymore!