TechnicalBits

SET SERVEROUTPUT ON;
DECLARE
 V1 VARCHAR2(32767);
 l_file UTL_FILE.FILE_TYPE;
 V_COUNT    NUMBER      :=0;
 V_FILENAME VARCHAR2(50):=NULL;
 l_buffer raw(32767);
 l_amount NUMBER := 32767;
 l_pos    NUMBER := 1;
 L_BLOB BLOB;
 L_BLOB_LEN NUMBER:=0;

  CURSOR GET_CLAIM
  IS SELECT RECEIPT,ATTACH_FILENAME FROM EMPLOYEE_DATA ;
 
BEGIN
FOR REC IN GET_CLAIM
LOOP
L_BLOB     := REC.RECEIPT;
v_FILENAME := rec.ATTACH_FILENAME;
l_blob_len := dbms_lob.getlength(l_blob);
L_POS      := 1; --re-init l_pos var
-- Open the destination file.
l_file     := UTL_FILE.FOPEN('LOCAL_DIR',v_FILENAME, 'WB');
DBMS_OUTPUT.PUT_LINE(V_FILENAME||'  '||l_blob_len);
 
-- 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;

UTL_FILE.FCLOSE(l_file);
V_COUNT:=V_COUNT+1;

END LOOP;
DBMS_OUTPUT.PUT_LINE(V_COUNT||' '||'files created');

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
 RAISE_APPLICATION_ERROR(-20000, 'ERROR: Invalid PATH FOR file.');
 DBMS_OUTPUT.PUT_LINE('error');
-- Close the file if something goes wrong.
WHEN OTHERS THEN
 IF utl_file.is_open(l_file) THEN
utl_file.fclose(l_file);
 END IF;
 DBMS_OUTPUT.PUT_LINE('error');


END ;