more

Get The Last String With Query

Get The Last String With Query

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.

Current Output

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?

Share this post

About the author