DB JDBC Oracle big Blob Management by chunks

Lately I've been thinking on how to store a blob and fetching it from the data base without consuming a lot of HeapMemory, in other words is there a way to stream data blobs into/from data base? like when we stream from/into files? -Let's suppose that your Blob's size is more than 2Go, then, if you use the traditional way of sending the blob into the data base, you are going to be using more than 2Go of RAM at a moment during the the RunTime.

1. The common way to fetch a Blob From database

•  Given SQL Function for getting a given blob by id: (This first item is called once in your code)

• Then you define your function in DB server by calling the following JDBC code:

• Then the JDBC call for fetching the blob from the database whenever you want to recover a blob

> as you can see at that stage we can be already overusing the memory, because of we are holding the whole blob in memory

2. The common way to Insert a Blob into the database 

•  For inserting a Blob into database: there are two steps to follow, the first one is: creating an empty blob:

•  Then you get your empty blob locator and you fill it with your data: 

3. Trick/Solution to avoid that HeapMemory OverUsage: For Insertion

The Trick is quiet simple, There are three steps to follow:

1.  Inserting an Empty blob, as it was introduced previously
2.  Create a temporary sub-blob which will contain a chunk of data that you want to send to your blob

3. You define an SQL procedure which will be capable to recover your predefined blob and updates it with the amount of data and loop on the call of this procedure while there are chunks of data to update with your referenced blob

This is the function which will be running in the DB side to update the blob

This sample code below shows how to call the procedure from the java side to update the blob:

4. Trick/Solution to avoid HeapMemory OverUsage: For Fetching 

Here is a simple trick to fetch a chunk of blob from the database: again, it is based on the same concept as the inserting idea which is based on delegating a part of the job to the sql code: So in order to perform this operation, you need to:

• Define the following SQL function which will be called to fetch a specified part of the blob, by giving the parameters the size of the this amount and the starting offset:

• Here is the java code which calls this function: