Using BLOB in a SELECT statement

[o.germeau]

I have text in a BLOB field and i want to do a select statment.
When i use DBMS_LOB.SUBSTR the result is in hexadecimal.
Also SELECT * FROM TEST WHERE DBMS_LOB.SUBSTR(BLOBFIELD) LIKE 'Ligne%' don't work.
But SELECT * FROM TEST WHERE DBMS_LOB.SUBSTR
(BLOBFIELD) LIKE RAWTOHEX('Ligne') | | '%' work well (but it's case sensitive);
I have tested SELECT * FROM TEST WHERE HEXTORAW(DBMS_LOB.SUBSTR(BLOBFIELD)) LIKE ('Ligne%'), but the result is the same that the first select (hexadecimal)
HEXTTORAW seem don't work.
What is the problem ?

 

[DavidH]

I'm not sure, but you could try having the field as CLOB when you have character data in it.

 

[o.germeau]

I have already tested BLOB and CLOB but the result is the same.
The idea is to convert HEXA in VARCHAR2.
But i don't know how to process and why HEXTORAW don't work.