Write image BLOB's to files using PL/SQL [message #435321] |
Tue, 15 December 2009 10:18 |
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
[Edit MC: add code tags]
[Updated on: Tue, 15 December 2009 10:31] by Moderator Report message to a moderator
|
|
|
|
|