CODEWITHSUNDEEP
javasqlhexbloboracle12c
Jorge Avila
Jorge Avila

Reputation: 149

How to get a complete hexadecimal value from blob datatype?

I am trying to get an hexadecimal value from a BLOB column from Java using a query so:

SELECT RAWTOHEX(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(thumbnail, 2000,1))) thumbnail FROM thumbnails

if I use the next query:

SELECT LENGTH(thumbnail) FROM thumbnails

the byte length of all thumbnails are over 6000 bytes

the first query it works because I am giving it 2000 bytes because that is the size of varchar2 file type:

How can I modify the query to get all the thumbnail (all characters) and not only 2000 characters? or another way to get hexadecimal complete from a blob value?

Upvotes: 1

Views: 2234

Answers (1)

Jorge Avila
Jorge Avila

Reputation: 149

I solved this problem without using SQL, only using Java: the query would be executed normal:

String query = "SELECT thumbnail FROM Thumbnails"
ResultSet rs = DBManager.getInstance().execute(query);

and get the value as Blob in Java:

Blob thumbnail;
if (rs.next()){
    thumbnail = rs.getBlob("thumbnail");
    byte[] blobBytes = thumbnail.getBytes(1, (int)thumbnail.length());
    return Hex.encodeHexString(blobBytes).toUpperCase();
}

very simple...

Upvotes: 2

Related Questions