CREATE TABLE FLATFILE
(
  NR              NUMBER,
  BINARY_OR_CHAR  NUMBER(1)                     NOT NULL,
  TEXT            VARCHAR2(1024 BYTE),
  BINARYS         BLOB
);

COMMENT ON COLUMN FLATFILE.BINARY_OR_CHAR IS '0 - Char
1 - Binary';



CREATE UNIQUE INDEX FLATFILE_PK ON FLATFILE
(NR);


ALTER TABLE FLATFILE ADD (
  CONSTRAINT CHECK1
  CHECK (binary_or_char in (0,1))
  ENABLE VALIDATE,
  CONSTRAINT FLATFILE_PK
  PRIMARY KEY
  (NR)
  USING INDEX FLATFILE_PK
  ENABLE VALIDATE);

Insert into YMEJER.FLATFILE
   (NR, BINARY_OR_CHAR, TEXT)
 Values
   (1, 0, 'TEST1');
Insert into YMEJER.FLATFILE
   (NR, BINARY_OR_CHAR, TEXT)
 Values
   (2, 0, 'TEST2');
Insert into YMEJER.FLATFILE
   (NR, BINARY_OR_CHAR, TEXT)
 Values
   (3, 0, 'TEST3');
Insert into YMEJER.FLATFILE
   (NR, BINARY_OR_CHAR,binarys)
 Values
   (4, 1, UTL_RAW.CAST_TO_RAW('xhu'));
COMMIT;

DECLARE
  /* Declare local input variables. */
  lv_location      VARCHAR2(60) := 'DIR';
  lv_file_name     VARCHAR2(40) := 'FT20160928';
 
  /* Declare a file reference pointer and buffer. */
  lv_file     UTL_FILE.FILE_TYPE;  -- File reference
  lv_line     VARCHAR2(32767);     -- Reading buffer
  chunk_size  BINARY_INTEGER := 32767;
  CURSOR c_ektr20
      is
      SELECT datensatz_upload_CHAR||CHR(13) as datensatz_upload_CHAR, BINARYS  FROM FLATFILE
      ORDER BY nr asc;
       
BEGIN
 
  /* Check for open file and close when open. */
  IF UTL_FILE.is_open(lv_file) THEN
    UTL_FILE.fclose(lv_file);
  END IF;

  lv_file := UTL_FILE.fopen( location     => lv_location
                           , filename     => lv_file_name
                           , open_mode    => 'a'
                           , max_linesize => chunk_size);
 

  FOR r_ektr20 in c_ektr20 LOOP


     UTL_FILE.PUT_LINE( file   => lv_file
                     , buffer => r_ektr20.datensatz_upload_CHAR );
 
  END LOOP;
  
  UTL_FILE.FCLOSE( file =>lv_file);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    UTL_FILE.fclose(lv_file);
END;