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
January 20th, 2008 at 22:02
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
January 21st, 2008 at 08:32
set long 1000000
set longchunksize 1000000
June 24th, 2008 at 15:30
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.
June 24th, 2008 at 16:01
with which query did you get the error? select *? utl_raw? dbms_lob
June 25th, 2008 at 11:30
I’m just running the script as it is and getting the error.
June 25th, 2008 at 12:38
which one? what is your client? what is your version?
You can paste output here using the <code> and </code> tags