Export BLOB Contents Using UTL_FILE
In a previous article I explained how to export the contents of a BLOB to a file using a Java stored procedure. In Oracle 9iR2 (9.2) it is also possible to perform this operation using new functionality provided by the UTL_FILE package.
First we create a directory object pointing to the destination directory.
CREATE OR REPLACE DIRECTORY BLOBS AS '/tmp/';
Next we open the BLOB, read chunks into a buffer and write them to a file.
DECLARE l_file UTL_FILE.FILE_TYPE; l_buffer RAW(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_blob BLOB; l_blob_len INTEGER; BEGIN -- Get LOB locator SELECT col1 INTO l_blob FROM tab1 WHERE rownum = 1; l_blob_len := DBMS_LOB.getlength(l_blob); -- Open the destination file. l_file := UTL_FILE.fopen('BLOBS','MyImage.gif','w', 32767); -- Read chunks of the BLOB and write them to the file -- until complete. WHILE l_pos < l_blob_len LOOP DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer); UTL_FILE.put_raw(l_file, l_buffer, TRUE); l_pos := l_pos + l_amount; END LOOP; -- Close the file. UTL_FILE.fclose(l_file); EXCEPTION WHEN OTHERS THEN -- Close the file if something goes wrong. IF UTL_FILE.is_open(l_file) THEN UTL_FILE.fclose(l_file); END IF; RAISE; END; /
Finally, you can check the file is produced correctly.
Note: Port-specific Bug #2546782, raised against 9.2.0 on Windows 2000, reports wrong output from
Oracle 10g includes extra open modes (rb, wr, ab) to signify byte mode operation. The "wb" open mode can be used along with the
PUT_RAW procedure to prevent extra newline characters being added on a Windows platform.
For more information see:
- Import BLOB Contents
- Export BLOB Contents (8i)
- Import CLOB Contents
- Export CLOB Contents
Hope this helps. Regards Tim...