Oracle blobs...

Oracle again...

We have huge amount of data stored on Oracle database in blobs. Data is processed and compressed on middle tier box and then appended to blob. When I need to get the stream back I execute stored procedure looking more or less like that:

SELECT [blobtable].blobdata INTO v_blob
FROM [blobtable] WHERE blobId=v_blobId FOR UPDATE;

Dbms_Lob.OPEN(v_blob, dbms_lob.lob_readonly);
Dbms_Lob.READ(v_blob, v_length, v_offset, p_blob);
Dbms_Lob.CLOSE(v_blob);

COMMIT;

For 99% of the data it works perfectly but.... there always have to be some "but"... and Oracle sometimes throws an exception:

ORA-21560: argument 2 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 751
ORA-06512: at "[dbname].[packagename]", line 5535

Which points to line with Dbms_Lob.READ method.
I logged all parameters for this call and length and offset are definetely in range, they are not null nor invalid....

Sometimes I really hate Oracle ;)

Solution:

Dbms_Lob.READ outputs BLOB chunk into RAW type which can store up to 32767 bytes so we have to put chunks of our BLOB into RAW buffer and append it each time to out param BLOB.

Don't forget to initialize out BLOB before the while loop:

DBMS_LOB.CREATETEMPORARY(p_blob, true);