Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle function using Java to return Blob is zero size

 
Colm Dickson
Ranch Hand
Posts: 89
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic