Laurent Schneider

I have a table with a blob

create table t(b blob);
insert into t values ('585858');

In 11g sql*plus, I can display raw data

select b from t;
B
------
585858

Ok, but if I want to display XXX (the character content)

select utl_raw.cast_to_varchar2(b) from t;
UTL
--- 
XXX

However, in sql, a raw cannot be more than 2000 bytes long.

Another way to print your blob content is to use DBMS_LOB.CONVERTTOCLOB

var c clob
set autoprint on
declare
  b blob;
  dest_offset integer := 1 ;
  src_offset  integer := 1 ;
  lang_context integer:= 1 ;
  warning integer;
begin
  select b into b from t for update;
  dbms_lob.createtemporary(:c,true);
  dbms_lob.converttoclob(
    :c, b, DBMS_LOB.LOBMAXSIZE,
    dest_offset, src_offset,
    1, lang_context, warning);
end;
/
C
---
XXX

Blogroll, dba, sql, sqlplus