Oracke大欄位Blob匯出到檔案

Export BLOB Contents Using UTL_FILE

[@more@]

In a previous article I explained how to export the contents of a BLOB to a file using a Java stored procedure. In Oracle9i (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 'C:';
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 Utl_File.Put_Raw.