Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Converting a blob to file

sgalaxyMay 13 2022

Hi,
I have declared the following db procedure:

CREATE OR REPLACE PROCEDURE convBlobToFile_(p_dir in varchar2,p_file VARCHAR2,p_lob blob) IS

vblob BLOB;
vstart NUMBER := 1;
bytelen NUMBER := 32767;
len NUMBER;
my_vr RAW(32767);
x NUMBER;

l_output utl_file.file_type;

BEGIN
BEGIN

--utl_raw.CONVERT(a,el8mswin,ww8msee)

-- define output directory
l_output := utl_file.fopen(p_dir,p_file, 'WB', 32767);
-- get length of blob
len:=dbms_lob.getlength(p_lob);
-- save blob length
x := len;
dbms_output.put_line('LEN: '|| x);
-- select blob into variable
vblob:=p_lob;

-- if small enough for a single write
IF len < 32767 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);
EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_output) THEN
UTL_FILE.fclose(l_output);
END IF;
RAISE;
END;
END;

Executing the above routine, although :
a) no error message appears,
b) the message "Len: " (according to the dbms_output.put_line in this procedure) appears with value greater than 0 (specifically: 8676),
no file created in the oracle directory (p_dir as parameter in the procedure).
Have anyone the slightest idea what may be the reason...?

Note:
I use Oracle11g v.2.

Comments
Post Details
Added on May 13 2022
14 comments
3,202 views