Reading file with PL / SQL ends with error

Question

I work in a small procedure that should read a file and validate if the file is empty or does not exist. The validation works but I tried to implement the reading and it throws me the following error:

ORA-29284: file read error
ORA-06512: at "SYS.UTL_FILE", line 106
ORA-06512: at "SYS.UTL_FILE", line 746
ORA-06512: at "HCM_ESTRUCTURES", line 23
ORA-06512: at line 3

My code:

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;

The procedure only prints a part of the contents of the file and then shows error.

You should be getting "ORA-01403: no data found" when you read past the end of the file... How large is the file, how much of it is read/displayed (i.e. how many successful `read_line` calls are there before it fails), what kind of data does it contain - particularly around the failure point? What is the maximum length of a line within the file - could that be more that 32767 characters? — Alex Poole, Mar 20 '19 at 17:50

VN'sCorner · Answer 1 · 2019-03-21T14:46:12.413

UTL_FILE has limit to the length of the data.Seems like you are reading relatively larger file so might need CLOB datatype. Can you rewrite your code to replace utl_file with DBMS_LOB utility. Below approach should resolve the error,

Use DBMS_LOB.LoadCLOBFromFile to read the data to CLOB variable. Storing Files in Oracle DB's CLOB field
Once data is to CLOB variable, use substr(v_clob,1,32767) to read 32767 characters, continue substr untill you reach length of the v_clob.

Reading file with PL / SQL ends with error

1 Answers1