8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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;
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('BLOB_DIR','MyImage.gif','w', 32767); l_file := UTL_FILE.fopen('BLOB_DIR','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.
In reality you would probably put this code into a stored procedure, or packaged procedure. This example is available here.
CREATE OR REPLACE PROCEDURE blob_to_file (p_blob IN BLOB, p_dir IN VARCHAR2, p_filename IN VARCHAR2) AS l_file UTL_FILE.FILE_TYPE; l_buffer RAW(32767); l_amount BINARY_INTEGER := 32767; l_pos INTEGER := 1; l_blob_len INTEGER; BEGIN l_blob_len := DBMS_LOB.getlength(p_blob); -- Open the destination file. l_file := UTL_FILE.fopen(p_dir, p_filename,'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(p_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 blob_to_file; /
You might call this like the following.
DECLARE l_blob BLOB; BEGIN -- Get LOB locator SELECT col1 INTO l_blob FROM tab1 WHERE rownum = 1; blob_to_file(p_blob => l_blob, p_dir => 'BLOB_DIR', p_filename => 'MyImage.gif'); END; /
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...