oracle spool blob code example

Example: oracle spool blob to file

CREATE OR REPLACE PROCEDURE retrieve_lob_to_file(temp_blob in BLOB, file_path in varchar2, file_name in varchar2) IS 
data_buffer RAW (32767); 
position INTEGER := 1; 
filehandle utl_file.file_type; 
error_number NUMBER; 
error_message VARCHAR2(100); 
blob_length INTEGER; 
chunk_size BINARY_INTEGER := 32767; 
BEGIN 
blob_length := dbms_lob.getlength(temp_blob); 

filehandle := utl_file.fopen(file_path, file_name,'wb', 1024); 

WHILE position < blob_length LOOP 

dbms_lob.read (temp_blob, chunk_size, position, data_buffer); 

utl_file.put_raw (filehandle, data_buffer); 
position := position + chunk_size; 
data_buffer := null; 
END LOOP; 

utl_file.fclose(filehandle); 


EXCEPTION 
WHEN OTHERS THEN 
BEGIN 
error_number := sqlcode; 
error_message := substr(sqlerrm ,1 ,100); 
dbms_output.put_line('Error #: ' || error_number); 
dbms_output.put_line('Error Message: ' || error_message); 
utl_file.fclose_all; 
END; 
END; 
/