Home » SQL & PL/SQL » SQL & PL/SQL » Write image BLOB's to files using PL/SQL (Oracle10g, sql*plus)
Write image BLOB's to files using PL/SQL [message #435321] Tue, 15 December 2009 10:18 Go to next message
Green-Card-Lottery
Messages: 2
Registered: December 2009
Location: Orlando
Junior Member

/* First create the directory physically on disk, then
** as sysdba execute the following commands, 
** where your_schema is the schema where you will be executing 
** the procedure from */
create or replace directory datadump as '/home/oracle/datadump';
GRANT read, write ON DIRECTORY datadump TO your_schema; 
GRANT EXECUTE ON UTL_FILE TO your_schema;

If you have an BLOB image table that you want to dump all images to file, this routine will do the trick.
We have a table blob_image_table with the following columns:
image_name varchar2(128)
image_extension varchar2(128)
image_blob BLOB

CREATE OR REPLACE PROCEDURE unload_images IS
  v_file UTL_FILE.FILE_TYPE;
  l_buffer    RAW(32000);
  l_amount    BINARY_INTEGER := 32000;
  l_pos       number := 1;
  l_blob      BLOB;
  l_blob_len  number;
  v_name VARCHAR2(128);

CURSOR Cimage IS 
  select image_name,image_extension,image_blob l_blob,
         dbms_lob.getlength(image_blob) l_blob_len  
  from blob_image_table;

 RecordsAffected PLS_INTEGER := 0;
 v_CONTENT_LENGTH PLS_INTEGER := 0;
 v_CONTENT_LENGTH2 PLS_INTEGER := 0;
 v_content  clob;
 j PLS_INTEGER := 0;
 k PLS_INTEGER;
 l PLS_INTEGER;
 vn_the_rest PLS_INTEGER :=0;
 vn_the_rest2 PLS_INTEGER :=0;
 v_image_count number;
 x number;
  BEGIN
   DBMS_OUTPUT.PUT_LINE('Begin Time: '||to_char(sysdate,'MM/DD/YYYY hh:mm:ss'));


   FOR Rec IN Cimage LOOP
        x := l_blob_len;
    -- Open the destination file.
        v_name := Rec.image_name||'.'||Rec.image_extension;
        v_file := UTL_FILE.FOPEN('DATADUMP',v_name,'wb',32767);
        if Rec.l_blob_len < 32000 then
            DBMS_LOB.read(Rec.l_blob, Rec.l_blob_len, l_pos, l_buffer);
            utl_file.put_raw(v_file,l_buffer, TRUE);
            utl_file.fflush(v_file);
        else

            WHILE l_pos < Rec.l_blob_len and l_amount > 0 LOOP
                DBMS_LOB.read(Rec.l_blob, l_amount, l_pos, l_buffer);
                UTL_FILE.put_raw(v_file, l_buffer, TRUE);
                utl_file.fflush(v_file);
    -- set the start position for the next cut
                l_pos := l_pos + l_amount;
    -- set the end position if less than 32000 bytes
                x := x - l_amount;
                IF x < 32000 THEN
                    l_amount := x;
                END IF;
            END LOOP;
            l_pos := 1;
            l_buffer := null;
            l_blob := null;
            l_amount := 32000;
        end if;
        RecordsAffected := RecordsAffected + 1;
        UTL_FILE.FCLOSE(v_file);
    end if;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('End Time ===> '||to_char(sysdate,'MM/DD/YYYY hh:mm:ss'));
   DBMS_OUTPUT.PUT_LINE('Image Records Affected: '||RecordsAffected);
   EXCEPTION
    WHEN utl_file.invalid_path THEN
        raise_application_error(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR.');
    WHEN utl_file.WRITE_ERROR THEN
        raise_application_error(-20001, 'Operating system error occurred during the write operation.');
    WHEN utl_file.INTERNAL_ERROR THEN
        raise_application_error(-20002, 'Unspecified PL/SQL error.');
    WHEN utl_file.INVALID_OPERATION THEN
        raise_application_error(-20003, 'File could not be opened or operated on as requested.');
    WHEN utl_file.INVALID_FILEHANDLE THEN
        raise_application_error(-20004, 'File handle was invalid.');
    WHEN utl_file.INVALID_MODE THEN
        raise_application_error(-20005, 'The open_mode parameter in FOPEN was invalid.');
    WHEN utl_file.INVALID_MAXLINESIZE THEN
        raise_application_error(-20006, 'Specified max_linesize is too large or too small.');
    WHEN OTHERS THEN
    -- Close the file if something goes wrong.
    IF UTL_FILE.is_open(v_file) THEN
      UTL_FILE.fclose(v_file);
    END IF;
    RAISE;
END;
/

The in SQL*PLUS execute the routine
execute unload_images;


[Edit MC: add code tags]

[Updated on: Tue, 15 December 2009 10:31] by Moderator

Report message to a moderator

Re: Write image BLOB's to files using PL/SQL [message #435324 is a reply to message #435321] Tue, 15 December 2009 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the code.
Next time please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use code tags and "Preview Message" button to verify.

Regards
Michel
Re: Write image BLOB's to files using PL/SQL [message #435328 is a reply to message #435324] Tue, 15 December 2009 10:58 Go to previous message
Green-Card-Lottery
Messages: 2
Registered: December 2009
Location: Orlando
Junior Member

Hi Michel,
I will read the guidelines and make sure to format my post correctly before posting again.
Thanks,
Previous Topic: Please help with ORA-01427: single-row subquery returns more than one row
Next Topic: Needed PL/SQL Procedure for this 2nd Table.(and others merged 5)
Goto Forum:
  


Current Time: Fri Mar 29 08:59:21 CDT 2024