Export BLOB Contents Using UTL_FILE
Related articles.
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.
CONN / AS SYSDBA CREATE OR REPLACE DIRECTORY BLOBS AS '/tmp/'; GRANT READ, WRITE ON DIRECTORY BLOBS TO my_user;If the directory
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); l_file := UTL_FILE.fopen('BLOBS','MyImage.gif','wb', 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.
Port-specific Bug #2546782, raised against 9.2.0 on Windows 2000, reports wrong output from UTL_FILE.PUT_RAW
.
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
- DBMS_LOB
- UTL_FILE
Hope this helps. Regards Tim...