I am trying to read a blob field from Oracle
database and wrote a query (Actually googled) to get it into base64 string. Below is a screenshot that how the data is retrieved:
Initial query:
SELECT dbms_lob.getlength(A.PICTURE) AS FILE_CONTENT_LENGTH,
MOD(dbms_lob.getlength(A.PICTURE),2000) AS MOD,
CASE WHEN MOD(dbms_lob.getlength(A.PICTURE),2000) > 0 AND MOD(dbms_lob.getlength(A.PICTURE),2000) > 0 THEN TRUNC((dbms_lob.getlength(A.PICTURE)/2000) + 2) ,
WHEN MOD(dbms_lob.getlength(A.PICTURE),2000) > 0 THEN TRUNC((dbms_lob.getlength(A.PICTURE)/2000) + 1) END INTERATION_COUNT,
A.EMP_PIC FILE_CONTENT FROM USERINFO A WHERE A.ID = '123456';
With this query, I get the below data:
FILE_CONTENT_LENGTH MOD ITERATION_COUNT BLOB_FIELD
110869 869 57 (HUGEBLOB)
With the above query, I retrieve the file content size or length as am unable to get the blob once for an exception TTC error. There is a limitation when I try to get the blob result as base64 string (2k character). So using the above, I check the file length and how many iterations required to get that string up to 2k characters. Everything is fine except one, the query I’ve counts up to 56 that I made to 57 (It has to be as the mod 869 has to be calculated to get the proper base64 string, right now it excludes). The reason, above query excludes the last base64 string as you can see in the screenshot.
It should retrieve the last one as well 110869 (Given below). So the full query looks like the below:
CONTENT OFFSET FILE_CONTENT_LENGTH MOD ITERATION_COUNT
base64 string 110869 110869 869 57
WITH INFO AS
(
SELECT dbms_lob.getlength(A.PICTURE) AS FILE_CONTENT_LENGTH,
MOD(dbms_lob.getlength(A.PICTURE),2000) AS MOD,
CASE WHEN MOD(dbms_lob.getlength(A.PICTURE),2000) > 0 AND MOD(dbms_lob.getlength(A.PICTURE),2000) > 0 THEN TRUNC((dbms_lob.getlength(A.PICTURE)/2000) + 2), --If mod is there, get the iteration added with value 2
WHEN MOD(dbms_lob.getlength(A.PICTURE),2000) > 0 THEN TRUNC((dbms_lob.getlength(A.PICTURE)/2000) + 1) END INTERATION_COUNT,
A.EMP_PIC FILE_CONTENT FROM USERINFO A WHERE A.EMP_AUTO_ID = 'Z3434'
),
OFFSETS AS
(
SELECT CASE WHEN I.MOD > 0 THEN (2000 * (ROWNUM-1)) + 1 END AS OFFSET, I.MOD, I.FILE_CONTENT_LENGTH,
I.FILE_CONTENT, I.INTERATION_COUNT FROM INFO I CONNECT BY LEVEL <= I.INTERATION_COUNT
),
RESULT AS
(
SELECT DBMS_LOB.SUBSTR(O.FILE_CONTENT, 20, O.OFFSET) AS CONTENT, O.OFFSET, O.MOD, O.FILE_CONTENT_LENGTH,
O.INTERATION_COUNT FROM OFFSETS O
)
SELECT R.CONTENT, R.OFFSET, R.MOD, R.FILE_CONTENT_LENGTH, R.INTERATION_COUNT FROM RESULT R ORDER BY R.OFFSET ASC;
As the last string is missed, am unable to get the exact string. Is it possible to add the mod as well, so it can get the full base64 string using query?