Mega Code Archive
Deal with blob data
<![CDATA[
SQL>
SQL> create table demo
2 ( id int primary key,
3 theBlob blob
4 )
5 /
Table created.
SQL>
SQL> create or replace directory my_files as 'c:\temp\';
Directory created.
SQL>
SQL> create sequence blob_seq;
Sequence created.
SQL>
SQL> create or replace function clean( p_raw in blob,
2 p_from_byte in number default 1,
3 p_for_bytes in number default 4000 )
4 return varchar2 as
5 l_tmp varchar2(8192) default utl_raw.cast_to_varchar2( dbms_lob.substr(p_raw,p_for_bytes,p_from_byte));
6 l_char char(1);
7 l_return varchar2(16384);
8 l_whitespace varchar2(25) default chr(13) || ' ' || chr(9);
9 l_ws_char varchar2(50) default 'rnt';
10
11 begin
12 for i in 1 .. length(l_tmp)
13 loop
14 l_char := substr( l_tmp, i, 1 );
15 if ( ascii(l_char) between 32 and 127 )
16 then
17 l_return := l_return || l_char;
18 if ( l_char = '\' ) then l_return := l_return || '\';
19 end if;
20 elsif ( instr( l_whitespace, l_char ) > 0 )
21 then
22 l_return := l_return || '\' || substr( l_ws_char, instr(l_whitespace,l_char), 1 );
23 else
24 l_return := l_return || '.';
25 end if;
26 end loop;
27 return substr(l_return,1,4000);
28 end;
29 /
Function created.
SQL>
SQL> select id,dbms_lob.getlength(theBlob) len,clean(theBlob,30,40) piece,
2 dbms_lob.substr(theBlob,40,30) raw_data
3 from demo
4 /
no rows selected
SQL> drop table demo;
Table dropped.
SQL> drop sequence blob_seq;
Sequence dropped.
]]>