Re: Oracle 8i : Copying Blobs To Os-FileSystem???

Karl R. wrote:
>
>
> Hello!
> We used to put Pictures into the Database with Bfile and insertered it
> into a BLOB-Column.
> Now we want to go the opposite direction:
> How we can get the Blobs as OS-files? With BFILE you cannot create an
> OS-FILE.
>
> Thanks for Help!
>
> /Karl
>

Use the DBMS_LOB package. There is a function that will allow you to write out the BLOB in chunks. Embed that in a loop with the UTL_FILE package. I did something similiar. The code follows.

PROCEDURE Create_file_from_LOB(p_clob IN CLOB,p_filename IN VARCHAR2) IS

li_amount               INTEGER := 1024;
li_offset               INTEGER := 1;
lv_buffer               VARCHAR2(1024); -- Make sure that this is the same 
as li_amount
lFILE_handle            UTL_FILE.FILE_TYPE;

BEGIN    lFILE_handle := UTL_FILE.FOPEN(gv_FileDir,p_filename,'w');

   BEGIN       LOOP

         DBMS_LOB.READ(p_clob,li_amount,li_offset,lv_buffer);
         UTL_FILE.PUT(lFILE_handle,lv_buffer);
         EXIT WHEN li_amount = 0;
         li_offset := li_offset + li_amount;

      END LOOP;

   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;

   END;    UTL_FILE.FCLOSE(lFILE_handle);

EXCEPTION
   WHEN UTL_FILE.INVALID_PATH THEN

      UTL_FILE.FCLOSE(lFILE_handle);
      dbms_output.put_line('Bad file handle');
   WHEN OTHERS THEN
      dbms_output.put_line('Error in Create Lob');

END Create_file_from_LOB;

I used CLOBS but the WRITE function is overloaded for BLOBs as well.

HOPE THAT HELPS RC

>
> * Sent from RemarQ http://www.remarq.com The Internet's Discussion
Network *
> The fastest and easiest way to search and participate in Usenet - Free!
>

--
Posted via CNET Help.com
http://www.help.com/ Received on Wed Dec 29 1999 - 10:30:17 CST