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?
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.