Well, heres a script that will dump out contents of blob that I used to test
things out. The script takes 3 arguments:
1) Name of table
2) Name of CLOB/BLOB field
3) ROWID of record with the LOB that you want, OR an asterisk * for all
records
Saving it into the file system is left as an exercise for the reader using
UTL_FILE (mostly because I don't actually have anything that deals with
UTL_FILE.)
YMMV
...Rudy
-------Begin script-------
exec dbms_output.disable;
set serveroutput off
set serveroutput on
exec dbms_output.enable(2000000);
set verify off
define BLOB_TABLE=&1
define BLOB_FIELD=&2
define BLOB_ROWID=&3
declare
dType USER_TAB_COLUMNS.DATA_TYPE%type;
maxLen number;
readLen number;
position number;
lastPosition number := 0;
offset number;
vBuffer varchar2(32767);
rBuffer raw(32767);
needFinalCount number := 0;
begin
select DATA_TYPE
into dType
from USER_TAB_COLUMNS
where COLUMN_NAME = upper('&&BLOB_FIELD') and
TABLE_NAME = upper('&&BLOB_TABLE');
for tCursor in (
select t.*, ROWID ROW_ID
from &&BLOB_TABLE t
where '&&BLOB_ROWID' = '*' or
ROWID = '&&BLOB_ROWID')
loop
maxLen := 32767;
offset := 1;
dbms_output.put_line(' ');
dbms_output.put_line('RowID=' || tCursor.ROW_ID ||
' getLength()=' ||
dbms_lob.getlength(tCursor.&&BLOB_FIELD) || ':');
if (tCursor.&&BLOB_FIELD is not null and
nvl(dbms_lob.getlength(tCursor.&&BLOB_FIELD), 0) <> 0) then
begin
if (dType = 'CLOB') then
dbms_lob.read(tCursor.&&BLOB_FIELD, maxLen, offset, vBuffer);
dbms_output.put_line(substr(vBuffer, 1, 255));
readLen := maxLen;
elsif (dType = 'BLOB') then
dbms_lob.read(tCursor.&&BLOB_FIELD, maxLen, offset, rBuffer);
dbms_output.put_line(
substr(
utl_raw.cast_to_varchar2(
utl_raw.translate(rBuffer,
utl_raw.cast_to_raw(chr(0)),
utl_raw.cast_to_raw('?'))),
1, 255));
vBuffer := utl_raw.cast_to_varchar2(rBuffer);
readLen := utl_raw.length(rBuffer);
end if;
for position in 1..readLen
loop
dbms_output.put(
substr('000' ||
ascii(substr(vBuffer, position, 1)), -3, 3) || '
');
if (mod(position, 20) = 0) then
dbms_output.put_line(' : ' || position);
needFinalCount := 0;
else
needFinalCount := 1;
end if;
lastPosition := position;
end loop;
if (needFinalCount <> 0) then
dbms_output.put(' : ' || lastPosition);
end if;
dbms_output.put_line(' ');
exception
when others then
dbms_output.put_line(' ?Exception?');
end;
end if;
end loop;
end;
/
undefine 1
undefine 2
undefine 3
-------End script-------
-----Original Message-----
Sent: Wednesday, July 02, 2003 4:56 PM
To: Multiple recipients of list ORACLE-L
Hi,
I have a file called file1.doc stored in a BLOB column that I would like to
retrieve and save it to the filesystem. Can someone post a sample PLSQL code
or tell me where I can get the information.
Thanks!
elain
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rudy Zung
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
