RE: Perplexing UTL_FILE.PUT_LINE Problem

From: Booth.Steve <Steve.Booth_at_we-energies.com>
Date: Thu, 16 Oct 2008 11:12:18 -0500
Message-ID: <D8B6ACB8EA9BBE4AB9A7EFBEA6986D6204E7837C@mlxdpm1w3.we.dirsrv.com>


Figured it out... we have a 3 tiered environment. I was in the process of moving a set of changes from Dev to Stg. There was a problem with Stage (hadn't gotten to that point in testing). Stage started writing to Production due to a bug in the Stage Configuration. The Production Job runs at 10 minutes after the hour, and the Stage job runs at 11 minutes after the hour. Stage data is incomplete. It overwrote the Good Data.  

What a difficult problem to find.  

Thanks for your suggestions.  

Steve...


From: Roman Podshivalov [mailto:roman.podshivalov_at_gmail.com] Sent: Wednesday, October 15, 2008 15:30
To: Booth.Steve
Cc: oracle-l_at_freelists.org
Subject: Re: Perplexing UTL_FILE.PUT_LINE Problem

Steve,

Possibilities are endless, just to guess a couple of obvious ones: - same file is being written from more then one of your Duhprocesses - some uncaught exception is raised between put_line and fetch and UNIX file never closed properly

--romas

On Wed, Oct 15, 2008 at 3:42 PM, Booth.Steve <Steve.Booth_at_we-energies.com> wrote:

        Running Oracle 10g (10.2.0.4.0) on UNIX.

        I'm a Duhveloper and have recently experienced a problem that I just can't figure out.

        Background:

                We have an application that exports Oracle data from a table to a flat file (on UNIX). The application was originally written in Version 8. The basic methodology of this part of the application issues an SQL Select statement which returns set of records of strings of 33 characters in length. The receiving variable is allocated as 100 characters.

                This code has been working fine and hasn't been changed for almost a year. The general logic is:

  • Build SQL Select Statement
  • Open Cursor with Select Statement
  • Open UNIX File for writing
  • Read first SQL Data record (FETCH Results_cur INTO Output_buffer)
  • While Results_cur%FOUND
    • UTL_FILE.Put_line( File_handle, Output_buffer );
    • ***
    • FETCH Results_cur INTO Output_buffer;
    • END Loop;
    • Close UNIX File.

                Since about noon on Monday we have been having intermittent problems where the data being written is incorrect (just bad data values, like a 0 instead of 274). In a vain attempt to figure out what is (occasionally happening), I dumped the "Output_buffer" to a logging table and it was not the same values as what was in the file. I did this at the location of the "***".

                In summary, it's intermittent and driving us nuts.

        Thanks in advance for any suggestions.

        Steve Booth

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 16 2008 - 11:12:18 CDT

Original text of this message