Home » RDBMS Server » Server Utilities » UTl_File - Problem - Records written intermittently
UTl_File - Problem - Records written intermittently [message #201506] Sun, 05 November 2006 06:53 Go to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi,
i am using Oracle 9.2.0.6.0 on linux

We are selecting records from table in a cursor and then writing those records in a file in a loop.
We have faced a problem here.
for example, say there 1000 records selected in a cursor(as returned by select query - which we checked running on sqlplus for numbver of records it returned)
first 300 were written to file then records from 301-500 are missing then again 501 till thousand are written properly in the file.
thus records between 310-500 are missing i.e. not written to the file.

the code structure is like as below

************
utl_file.fopen(..'W')

open cursor
loop
utl_file.put_line(..);
end loop;

if IsOpen then
fclose;
********************

we have not got any exception as well.

Please advice.

Thanks in Advance,
Pratap






Re: UTl_File - Problem - Records written intermittently [message #201511 is a reply to message #201506] Sun, 05 November 2006 07:14 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Did you check for NULL entries?
What if the cursor select is pulling some null entries?
Re: UTl_File - Problem - Records written intermittently [message #201526 is a reply to message #201506] Sun, 05 November 2006 10:36 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi Mahesh,
Thanks for the quick reply

I have checked the data by running the sql on sqlplus
and found that there were no NULL entries at all.

Also major thing when we executed the same procedure (for all the records [once again]) it produced file with correct number of records(incl the missing too)

Is it possible that some buffer (overflow) or I/O issue might have created this problem? or somebody opened the file while being written by Oracle?

Thanks and Regards,
Pratap
Re: UTl_File - Problem - Records written intermittently [message #201528 is a reply to message #201526] Sun, 05 November 2006 10:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Is it possible that some buffer (overflow) or I/O issue might have created this problem? or somebody opened the file while being written by Oracle?
You have to find that with proper exceptions. Buffer i/o etc will be reported in alertlog.

Re: UTl_File - Problem - Records written intermittently [message #201533 is a reply to message #201506] Sun, 05 November 2006 12:29 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi Mahesh,
i have checked the alertlogs (both thev instances - since RAC) and found nothing related to this issue.

Also as far exception is concerned that chances is less because in the second run the procedure created proper file with all the records

Is it possible that somebody might have tried open the file while being written - thus causing some of records missing?

Thanks and Regards,
Pratap
Re: UTl_File - Problem - Records written intermittently [message #201536 is a reply to message #201533] Sun, 05 November 2006 13:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> (both thev instances - since RAC)
Well that is new piece of information.
Where are you writing this file?
Make sure you are writing to a shared disk (like a datafile or archived log). THis file should be accessible from both nodes.
>>Is it possible that somebody might have tried open the file while being written
I have no idea. Possibilities are less. Why would 'someone' have access to an Oracle server ? Only authorized DBA folks should be allowed and they must be aware of what they are doing.

[Updated on: Sun, 05 November 2006 14:44]

Report message to a moderator

Re: UTl_File - Problem - Records written intermittently [message #202421 is a reply to message #201506] Thu, 09 November 2006 12:51 Go to previous message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Mahesh,
Thanks for the reply.
Aceess to files is really issue here.
Anyways further investigations continued...

Thanks and Regards,
Pratap
Previous Topic: Table Level Export
Next Topic: Varchar data type
Goto Forum:
  


Current Time: Wed Dec 07 12:22:24 CST 2016

Total time taken to generate the page: 0.12747 seconds