Home » SQL & PL/SQL » SQL & PL/SQL » ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason (Oracle 10g Release 10.2.3.0, AIX (UNIX))
ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422258] Tue, 15 September 2009 05:22 Go to next message
bestbefore00
Messages: 9
Registered: September 2009
Junior Member
Hi,
I have this code

V_INPUT := UTL_FILE.FOPEN (PZ_CDR_PATH, PZ_FILENAME, 'r', 3000);
WRITE_TO_LOG ('Processing file ' || PZ_FILENAME);
LOOP
BEGIN
VN_LINE := VN_LINE + 1;
UTL_FILE.GET_LINE (V_INPUT, VZ_RECORD);
WRITE_TO_LOG ('Reading line ' || VN_LINE);
EXCEPTION
WHEN NO_DATA_FOUND THEN
WRITE_TO_LOG ('EOF: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
SQLERRM, SQLCODE);
EXIT;
END;
-- Process VZ_RECORD..................................
END LOOP;

For some reason, NO_DATA_FOUND is raised even if the file is not read entirely. I have different files and this problem doesn't happen always. If I run the procedure again, for the same file, it may work or not. The files have 2000-4000 lines, and the problem appears from time to time. If some lines are skipped from a file, then the next files will have lines skipped too (all the lines from the end of the file). The number of lines skipped appears to be random. This procedure is called from a background application, which runs permanently.
It looks like it reached the end of the file, but I have VN_LINE which tells me the number of lines read.
The database is Oracle 10g Release 10.2.3.0. I have another database (another computer), and this problem does not appear even if I run the application for the same files.
The log looks like his:
........
02-SEP-09 04.45.58.01007 PM Reading line 2640
02-SEP-09 04.45.58.01955 PM Reading line 2641
EXCEPTION 02-SEP-09 04.45.58.02699 PM ORA-01403: no data found 100
EXCEPTION 02-SEP-09 04.45.58.02715 PM EOF: ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 656
ORA-06512: at ", line 3107 -- this line is UTL_FILE.GET_LINE (V_INPUT, VZ_RECORD);

Any idea?
Re: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422260 is a reply to message #422258] Tue, 15 September 2009 05:30 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Please format your code properly using CODE tag to make your post more readable

regards,
Delna
Re: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422289 is a reply to message #422258] Tue, 15 September 2009 06:47 Go to previous messageGo to next message
bestbefore00
Messages: 9
Registered: September 2009
Junior Member
V_INPUT := UTL_FILE.FOPEN (PZ_CDR_PATH, PZ_FILENAME, 'r', 3000);
WRITE_TO_LOG ('Processing file ' || PZ_FILENAME);
LOOP
   BEGIN
      VN_LINE := VN_LINE + 1;
      UTL_FILE.GET_LINE (V_INPUT, VZ_RECORD);
      WRITE_TO_LOG ('Reading line ' || VN_LINE);
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         WRITE_TO_LOG ('EOF: ' ||  DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, SQLERRM, SQLCODE);
         EXIT;
   END;
-- Process VZ_RECORD..................................
END LOOP;
Re: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422297 is a reply to message #422289] Tue, 15 September 2009 07:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The only way I know of for UTL_FILE to raise No_Data_Found is to try to read past the end of the file.
Re: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422300 is a reply to message #422297] Tue, 15 September 2009 07:17 Go to previous messageGo to next message
bestbefore00
Messages: 9
Registered: September 2009
Junior Member
Hi, thanks for the reply,

Normally, I agree, but I still get NO_DATA_FOUND, and I'm 100 % sure that is not the end of a file. If a file has, let's say, 2500 lines, utl_file.get_line reads 300 lines (more or less). As you see in my code, I count the lines read. maybe it 'sees' end of file, but why? And how do I avoid that? If I process that file again, all the lines are read (If I get lucky).
Re: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422302 is a reply to message #422300] Tue, 15 September 2009 07:36 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I'm 100 % sure that is not the end of a file.

But maybe it contains the EOF (end of file) character.

Regards
Michel
Re: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422305 is a reply to message #422300] Tue, 15 September 2009 07:40 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
bestbefore00 wrote on Tue, 15 September 2009 14:17
If I process that file again, all the lines are read (If I get lucky).


It is highly unlikely that it switches between working/not-working without anything changing. Things that come to mind if that is REALLY happening:

- Something/someone is changing the file while you are trying to read it.
- The disk are failing.
Re: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422312 is a reply to message #422302] Tue, 15 September 2009 08:18 Go to previous messageGo to next message
bestbefore00
Messages: 9
Registered: September 2009
Junior Member
Quote:

But maybe it contains the EOF (end of file) character.

I tried to process the same file on the other machine in order to see if I get the same error, but it works fine. At the moment, the application runs on the both machines, and the problem appears only on one of them.

I also processed the same file again on the machine with the problem, and it was read entirely.

Thanks for the reply.

Re: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422315 is a reply to message #422305] Tue, 15 September 2009 08:28 Go to previous messageGo to next message
bestbefore00
Messages: 9
Registered: September 2009
Junior Member
Quote:
Something/someone is changing the file while you are trying to read it.

I don't think so, because the application runs at night too, and nobody access the file, only my application reads it. I'll verify this again.

Quote:
The disk are failing

I don't know what to say about this; How can I verify that? Why is it crashing only at this instruction? Other applications works fine on this machine.

Appreciate your ideas..

one more thing... If I log every step, by writing in the log file, the problem doesn't appear anymore. I think it's something related to the speed. When I write in the log, the application works slower. But I'm not happy with this solution...
Re: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422345 is a reply to message #422315] Tue, 15 September 2009 09:47 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Two things you could check, if anything "funny" is in there:

- The Oracle alert log.

- The OS error log.

I believe in AIX that's

errpt |more


to see the log "titles" and

errpt -a |more


to see the complete messages.
Re: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422398 is a reply to message #422345] Wed, 16 September 2009 02:28 Go to previous messageGo to next message
bestbefore00
Messages: 9
Registered: September 2009
Junior Member
Quote:

Two things you could check, if anything "funny" is in there:

- The Oracle alert log.

- The OS error log.

I've check them and nothing... no error... no warning... nothing.
The only thing I got is NO_DATA_FOUND from my log file. Is there another way to read from the file up to the end without waiting for the NO_DATA_FOUND error?
Or maybe a Oracle undocumented function which can read from a specified line? I have the number of the last line read... I can try to close the file, open it again and read from the last line + 1.. but I don't know any function for that.
If you have another idea... I'm happy to try anything.
Thanks.
Re: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422404 is a reply to message #422398] Wed, 16 September 2009 02:41 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Depending on what you want do do, defining an External Table to read the file (even if it is just a table with one big varchar2 field) might work.
Re: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422406 is a reply to message #422398] Wed, 16 September 2009 03:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could use UTL_FILE.GET_RAW - that just reads in the required number of bytes, and ignored EOL terminators.

You can find the current read position in a file, and reposition the pointer within a file using UTL_FILE.FGETPOS and UTL_FILE.FSEEK
Re: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422413 is a reply to message #422406] Wed, 16 September 2009 04:31 Go to previous messageGo to next message
bestbefore00
Messages: 9
Registered: September 2009
Junior Member
Quote:
You could use UTL_FILE.GET_RAW

Thanks, I'll try this approach, although doesn't solve the problem, only will avoid it. I hope this will work.
External tables may be a good idea too, but I'll try with GET_RAW first. Seems easier Very Happy
Thanks a lot for the help.
Re: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422528 is a reply to message #422413] Thu, 17 September 2009 02:58 Go to previous messageGo to next message
bestbefore00
Messages: 9
Registered: September 2009
Junior Member
A part of the problem was solved. I've posted this message on another forum and someone said that this might be a problem related to the fact that the files are transferred through FTP. This works slow, and my application reads the incomplete file. This is true, but it seems this is not all. It was a really helpful idea, but it looks like I have 2 problems. One with FTP transfer and one unsolved.
I will come back with others details... If I discover something new.
Re: ULT_FILE.GET_LINE raise NO_DATA_FOUND without a reason [message #422529 is a reply to message #422528] Thu, 17 September 2009 03:09 Go to previous message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
If the problem is related to "someone changing the file" while you read it, the way we usually do it is that we rename files at the end of any transfers/processes.

For example, in all FTP transfers we do we transfer the files with an additional underscore before the name, and then rename them via ftp command after the transfer is finished.

Previous Topic: Beginner question on Objects
Next Topic: Nested table join order - weird behaviour
Goto Forum:
  


Current Time: Sun Sep 25 00:53:05 CDT 2016

Total time taken to generate the page: 0.07070 seconds