CREATE OR REPLACE PROCEDURE hcm_estructures AS
  /*-Files validation variables-*/
  l_file_exists BOOLEAN;
  l_file_len    NUMBER;
  l_blocksize   BINARY_INTEGER;
  /*Variables read file*/
  v_archivo utl_file.file_type;
  v_linea clob;
  v_path varchar2(100):= 'PREPROCESSOR_DIRECTORY';
BEGIN
  utl_file.fgetattr(
    location    => v_path,
    filename    => 'Worker_19032019.dat',
    fexists     => l_file_exists,
    file_length => l_file_len,
    block_size  => l_blocksize);
   if l_file_exists then
    if l_file_len > 0 then
      --dbms_output.put_line('The file will read correctly');
      v_archivo := utl_file.fopen ('PREPROCESSOR_DIRECTORY', 'Worker_19032019.dat', 'r',32767);
      loop
         utl_file.get_line (v_archivo, v_linea);
         dbms_output.put_line (v_linea);
      end loop;
      --utl_file.fclose(v_archivo);
    else
      dbms_output.put_line('The file is empty');
    end if;
  else
    dbms_output.put_line('The file does not exists');
  end if;
END;

CREATE OR REPLACE PROCEDURE hcm_estructures(p_file_name in varchar2,p_line out clob,p_retcode OUT number,p_errmes out varchar2) AS
  /*-Files validation variables-*/
  l_file_exists BOOLEAN;
  l_file_len    NUMBER;
  l_blocksize   BINARY_INTEGER;
  /*Variables read file*/
  v_file utl_file.file_type;
  v_path varchar2(100):= 'PREPROCESSOR_DIRECTORY';
  v_line clob;
BEGIN
  utl_file.fgetattr(
    location    => v_path,
    filename    => p_file_name,
    fexists     => l_file_exists,
    file_length => l_file_len,
    block_size  => l_blocksize);
   if l_file_exists then
    if l_file_len > 0 then
      v_file := utl_file.fopen (v_path, p_file_name, 'r',32767);
      LOOP
    BEGIN
      utl_file.get_line(v_file, v_line);
      if p_line is null then
         p_line := v_line;
      else
         p_line := p_line ||chr(10)|| v_line;
      end if;  
    EXCEPTION
      WHEN no_data_found THEN
        exit;
    END;
  END LOOP;
    else
      p_errmes := 'The file is empty';
      p_retcode := 2;
    end if;
  else
    p_errmes := 'The file does not exists';
    p_retcode := 1;
  end if;
END;
/

utl_file.get_line (file_v, v_line);

utl_file.get_line(v_file, v_line);
if p_line is null then
      p_line: = v_line;
else
      p_line: = p_line || chr (10) || v_line;
end if;