| UTl_File - Problem - Records written intermittently [message #201506] |
Sun, 05 November 2006 06:53  |
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 #201526 is a reply to message #201506] |
Sun, 05 November 2006 10:36  |
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 #201533 is a reply to message #201506] |
Sun, 05 November 2006 12:29 |
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 |
 |
Mahesh Rajendran
Messages: 10708
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
