Display a blob

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

6 thoughts on “Display a blob”

  1. Hi Laurent,

    I have some problems with DBMS_METADATA and CLOB type, I try with sqlplus from oracle server with 10gR1 and Linux Red Hat AS and lines from clob with more than 80 characters cut and pass to new line. Do you know what environment SET to solve this problem ?

    Regards
    PGutierrez

  2. I am getting “invalid change string” (SP2-0025) , I have a bmp file inside the BLOB.

    Do you have any idea what is the problem ?

    10x.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>