aspose file tools*
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
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
Author

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?

Thanks,

Colm
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

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: http://aspose.com/file-tools
 
subject: Oracle function using Java to return Blob is zero size