Re: Select Intermittently Returning Incorrect Number of Rows

Niall Litchfield wrote:

> "Steve" <sjc_at_NOSPAM.chollie.com> wrote in message
> news:97582v8gjd0rst85kilstltvvccbc02b9r_at_4ax.com...
> > I also very much doubt that it is fully down to Oracle, it wouldn't be
> > where it was if it was incapable of a simple select,I suspect it may
> > be the application enviroment (possibly the middleware), but all tests
> > have so far *seemed* to show that this is what is returned by the
> > server.
>
> Can you replicate the problem just using sql+ ? The other thing that you
> could consider doing is setting sql_trace=true for the session that is
> affected, this would tell you for sure if the sql was changing between
> calls.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************

I agree with Niall. Can you duplicate in SQL*Plus? If not ... it isn't the database.

Do your queries involve more than one table? I have seen situations like this where the problem was an inner join between two or more tables where a change to one table affects results returned from others.

In addition to the trace suggestion, a good one, take a look at the log files using Oracle's DBMS_LOGMNR built-in package. If the rows aren't showing in the log file (and I'll bet two fingers of a good single malt on this one) the problem is definitely elsewhere.

Daniel Morgan Received on Tue Jan 14 2003 - 15:35:21 CST