RE: retrieving BLOB

From: Rudy Zung <rzung_at_printcafe.com>
Date: Wed, 02 Jul 2003 13:53:49 -0700
Message-ID: <F001.005C1F62.20030702133920@fatcity.com>

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). Received on Wed Jul 02 2003 - 15:53:49 CDT