Hi All,
I have a table with BLOB as a column. I need to extract the blob as a file and rename it ( probably adding some prefix to the original file name).
Could you please help me on while executing this below procedure, where we can change the destination file name.
Say Ex: My file name in the table is 'Test.jpg'. And I want to extract it to directory on the server with the name 'SR001-Test.jpg'. How can i acheive this.
Thanks for your help.
CREATE OR REPLACE PROCEDURE blob2file(
pdname VARCHAR2, psname VARCHAR2, pfname VARCHAR2) IS
vblob BLOB;
vstart NUMBER := 1;
bytelen NUMBER := 32000;
len NUMBER;
my_vr RAW(32000);
x NUMBER;
l_output utl_file.file_type;
BEGIN
-- define output directory
l_output := utl_file.fopen('ORALOAD', pfname, 'WB', 32760);
-- get length of blob
SELECT dbms_lob.getlength(iblob)
INTO len
FROM pdm
WHERE dname = pdname
AND sname = psname
AND fname = pfname;
-- save blob length
x := len;
-- select blob into variable
SELECT iblob
INTO vblob
FROM pdm
WHERE dname = pdname
AND sname = psname
AND fname = pfname;
-- if small enough for a single write
IF len < 32760 THEN
utl_file.put_raw(l_output,vblob);
utl_file.fflush(l_output);
ELSE -- write in pieces
vstart := 1;
WHILE vstart < len
LOOP
dbms_lob.read(vblob,bytelen,vstart,my_vr);
utl_file.put_raw(l_output,my_vr);
utl_file.fflush(l_output)
-- set the start position for the next cut
vstart := vstart + bytelen;
-- set the end position if less than 32000 bytes
x := x - bytelen;
IF x < 32000 THEN
bytelen := x;
END IF;
END LOOP;
END IF;
utl_file.fclose(l_output);
END blob2file;