Oracle DB BLOB to String conversion

the problem is mainly with BLOBs > 2K

This is a collection of strategies:

this doesn't work, I get a "ORA-00932: inconsistent datatypes: expected NUMBER got BLOB" :
select TO_CHAR(DATA_VALUE) from wli_qs_report_data where msg_guid = 'uuid:7f9b72b69446518a:3591707b:1382c7d7451:-73f8';


this one is better, but still gives "ORA-06502: PL/SQL: numeric or value error: raw variable length too long":
select utl_raw.cast_to_varchar2(dbms_lob.substr(DATA_VALUE)) from wli_qs_report_data where msg_guid = 'uuid:7f9b72b69446518a:3591707b:1382c7d7451:-73f8';


this one gives "ORA-06502: PL/SQL: numeric or value error: raw variable length too long":
select DBMS_LOB.SUBSTR(DATA_VALUE, 32767, 1) from wli_qs_report_data where msg_guid = 'uuid:7f9b72b69446518a:3591707b:1382c7d7451:-73f8';


this one gives "ORA-00904: "DBMS_LOB"."CONVERTTOCLOB": invalid identifier":
select dbms_lob.converttoclob(DATA_VALUE) from wli_qs_report_data where msg_guid = 'uuid:7f9b72b69446518a:3591707b:1382c7d7451:-73f8';


after a while I give up, and do stuff directly in Java:


<sql:query var="rsMessageCount" dataSource="jdbc/soainfra" scope="request">
select DATA_VALUE from WLI_QS_REPORT_DATA where MSG_GUID='${msg_guid}'
</sql:query>

and I display ${rsMessageCount.getRowsByIndex()[0][0]} :
its class is oracle.sql.BLOB

I am planning to use http://docs.oracle.com/cd/E11882_01/appdev.112/e13995/oracle/sql/BLOB.html#getBinaryStream__

which is a java.sql.blob

how to display a java.sql.Blob as a String?

http://www.jguru.com/faq/view.jsp?EID=1325

java.sql.Blob blob = (java.sql.Blob)((org.apache.taglibs.standard.tag.common.sql.ResultImpl)(request.getAttribute("rsMessageCount"))).getRowsByIndex()[0][0];

byte[] bdata = blob.getBytes(1, (int) blob.length());

String text = new String(bdata);
text = text.replace("<", "&lt;");

out.write(text);