Utl_file fseek function giving error with large absolute offset?

Question

I have to make a program that will use fseek to read data from pirticular position. i have to read data line by line,and in pirticular line i have to use fseek to read data. But when i am using fseek and absolute_offset is of big size (little bit less then file size),it gives out this error

ORA-29284: file read error
ORA-06512: at "SYS.UTL_FILE", line 219
ORA-06512: at "SYS.UTL_FILE", line 1145
ORA-06512: at line 15"

but with small absolute_offset value like 4000 range works properly and picks data. Line no 15 where i am using fseek giving error.

DECLARE
  lv_utl UTL_FILE.FILE_TYPE;
  v_buff VARCHAR2(2000);
  l_exists boolean;
  l_block VARCHAR2(2000);
  l_file_length number;
  v_line varchar2(5000);
BEGIN
  UTL_FILE.fgetattr('/d04/data/edi/inbound','POO0001.dat',l_exists,l_file_length,l_block);
  lv_utl := UTL_FILE.FOPEN('/d04/data/edi/inbound','POO0001.dat','R');
  --utl_file.get_line(lv_utl,v_line,50);
  --dbms_output.put_line(v_line);
  --l_file_length:=length();
  dbms_output.put_line(l_file_length);
  utl_file.fseek(lv_utl,1261061);
  utl_file.get_line(lv_utl,v_buff,100);
  dbms_output.put_line(v_buff);
  --lv_msg_txt := 'empno ename job manager hiredate commission salary department_no';
  --UTL_FILE.PUT_LINE(lv_utl,lv_msg_txt);
  --UTL_FILE.PUT_LINE(lv_utl,'------------------------------------------------------------------------------------');
  --UTL_FILE.PUT_LINE(lv_utl,' ');
  --UTL_FILE.FCLOSE(lv_utl);
END;

Is the file_length being returned accurate (ie consistent with an "ls" output) ? — Gary Myers, Dec 04 '10 at 04:03

score 1 · Answer 1 · answered Jan 25 '11 at 21:11

I ran this in my environment and also had this pop up. Oddly, if I specify a maximum line lengh on FOPEN it seems to work correctly.

lv_utl := UTL_FILE.FOPEN('/d04/data/edi/inbound','POO0001.dat','R');

doesn't work, but

lv_utl := UTL_FILE.FOPEN('/d04/data/edi/inbound','POO0001.dat','R',5000);

works perfectly. I have no justification for this, but it sure looks like a bug.

As a work-around, you may want to try the DBMS_LOB package using BFILEs instead.

score 0 · Answer 2 · answered Dec 20 '13 at 15:38

I have encountered this in the past.

Max Line size is 1024, unless specified in the call to FOPEN (as you have done so in second example above (with 5000).

So Oracle would argue - it is expected behaviour, rather than a bug. http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm

Utl_file fseek function giving error with large absolute offset?

2 Answers2