DECLARE
 v_blob_content       BLOB;
 v_byte_length        VARCHAR2(32000);
 v_start_idx          NUMBER :=1;
 v_read_limit         RAW(32000);
 v_max_limit          NUMBER := 32000*100; 
 
 v_data_dir           VARCHAR2(30) := 'DATA_OUT_DIR';  --use defined value 
 v_op_file            UTL_FILE.filetype;

 X_FILE_MISSING       EXCEPTION;
 X_MULTI_FILES        EXCEPTION;
 X_ZERO_LENGTH        EXCEPTION;

BEGIN
 BEGIN
   SELECT file_data  INTO  v_blob_content
   FROM  stg_data_raw                     --table with BLOB data
   WHERE file_id = 10;                    --to be made as parameter

 EXCEPTION
   WHEN NO_DATA_FOUND THEN
     DBMS_OUTPUT.PUT_LINE('No file/data found');
     RAISE X_FILE_MISSING;
   WHEN TOO_MANY_ROWS THEN
     DBMS_OUTPUT.PUT_LINE('Too many files found');
     RAISE X_MULTI_FILES;
 END;

 IF DBMS_LOB.GETLENGTH(v_blob_content)= 0 THEN
     DBMS_OUTPUT.PUT_LINE('Zero length file found');
     RAISE X_ZERO_LENGTH;
 END IF;
 
 v_output_file := UTL_FILE.FOPEN(
                   location     => v_data_dir,
                   filename     => 'FILE_NAME.txt',  --to be parameterized
                   open_mode    => 'wb',             --write byte mode
                   max_linesize => 32767
                 );

 WHILE (v_start_idx < DBMS_LOB.GETLENGTH(v_blob_content)) LOOP
   DBMS_LOB.READ(
            lob_loc  => v_blob_content,
            amount   => v_byte_length,
            offset   => v_start_idx,
            buffer   => v_read_limit
          );

   UTL_FILE.PUT_RAW(v_op_file, v_read_limit);
   UTL_FILE.FFLUSH(v_op_file);
 
   v_start_idx := v_start_idx + v_byte_length;
 
   IF v_start_idx > v_max_limit THEN
      UTL_FILE.FCLOSE(v_op_file);
      v_output_file := UTL_FILE.FOPEN(
                        location     => v_data_dir,
                        filename     => 'FILE_NAME.txt', 
                        open_mode    => 'ab',            --append byte mode 
                        max_linesize => 32767
                     );
   END IF;
 END LOOP;

 UTL_FILE.FFLUSH(v_op_file);
 UTL_FILE.FCLOSE(v_op_file);

EXECPTION
  WHEN X_FILE_MISSING THEN
     DBMS_OUTPUT.PUT_LINE('No file/data found');
     RAISE;

  WHEN X_ZERO_LENGTH THEN
    DBMS_OUTPUT.PUT_LINE('Zero length file found');
    RAISE;

  WHEN X_MULTI_FILES THEN
    DBMS_OUTPUT.PUT_LINE('Too many files found');
    RAISE;

  WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_LINE('Invalid Path for File I/O');
    RAISE;

  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    DBMS_OUTPUT.PUT_LINE('Invalid File Handle');
    RAISE;

  WHEN UTL_FILE.INVALID_OPERATION THEN
    DBMS_OUTPUT.PUT_LINE('Invalid File I/O Operation');
    RAISE;

  WHEN UTL_FILE.READ_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Error in Reading File');
    RAISE;

  WHEN UTL_FILE.WRITE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('Error in Writing to File');
    RAISE;

  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_op_file);
    RAISE;
END;
/