Order by statement stopped working but not in all cases

------_=_NextPart_001_01C61DEE.41F0CFB1
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable


Has anyone ever heard of this? What do you think might be the cause and
importantly, the solution?

We have a bunch of applications that use DBI and DBD-Oracle and they've
been running for quite some time now (months and years, depending on
which app). Then within the last few days, one particular query within
the bunch of apps stopped returning results. When I took out the order
by statement in the query, it again returned results though not sorted.
If I take out one join in the query, the query with the order by
statement returns sorted results.

The apps have not been changed in the past week. Most have not been
changed since last summer, and only for cosmetic reasons (no query or
code changes). The DBI and DBD-Oracle has not been changed. I checked
with the DBA and he says there's been no Oracle software changes.

I'd like to find the single source of this behavior change rather than
make changes to a bunch of applications as I doubt changing the
applications will solve the underlying problem.


TIA,
Anna

--------------------------------------------------------

Anna Q. Fong, Webmaster=09
California Data Exchange Center=09




------_=_NextPart_001_01C61DEE.41F0CFB1--

RE: Order by statement stopped working but not in all cases

Yes, and it works as expected in SQLPLUS with no modifications to the
original query. One thought was the sort space, but this instance
doesn't support it.
=20

-----Original Message-----
From: Kong, Alan [mailto:KongA@coned.com]=20
Sent: Friday, January 20, 2006 10:27 AM
To: Fong, Anna
Subject: RE: Order by statement stopped working but not in all cases

Have you try running the query in sqlplus command prompt? Is it working
properly?

-----Original Message-----
From: Fong, Anna [mailto:anna@water.ca.gov]
Sent: Friday, January 20, 2006 1:21 PM
To: dbi-users@perl.org
Subject: Order by statement stopped working but not in all cases



Has anyone ever heard of this? What do you think might be the cause and
importantly, the solution?

We have a bunch of applications that use DBI and DBD-Oracle and they've
been running for quite some time now (months and years, depending on
which app). Then within the last few days, one particular query within
the bunch of apps stopped returning results. When I took out the order
by statement in the query, it again returned results though not sorted.
If I take out one join in the query, the query with the order by
statement returns sorted results.

The apps have not been changed in the past week. Most have not been
changed since last summer, and only for cosmetic reasons (no query or
code changes). The DBI and DBD-Oracle has not been changed. I checked
with the DBA and he says there's been no Oracle software changes.

I'd like to find the single source of this behavior change rather than
make changes to a bunch of applications as I doubt changing the
applications will solve the underlying problem.


TIA,
Anna

--------------------------------------------------------

Anna Q. Fong, Webmaster=09
California Data Exchange Center=09

Re: Order by statement stopped working but not in all cases

Most likly your indexes need to be rebuilt.

If what you say is true and none of you SW (Perl, DBI, DBD, Oracle) has
changed this could be the only cause that comes to mind



""Fong, Anna"" wrote in message
news:DA51F22DCD147C429BAA8007F4B909407AD9CC@jocex2.ad.water. ca.gov...

Has anyone ever heard of this? What do you think might be the cause and
importantly, the solution?

We have a bunch of applications that use DBI and DBD-Oracle and they've
been running for quite some time now (months and years, depending on
which app). Then within the last few days, one particular query within
the bunch of apps stopped returning results. When I took out the order
by statement in the query, it again returned results though not sorted.
If I take out one join in the query, the query with the order by
statement returns sorted results.

The apps have not been changed in the past week. Most have not been
changed since last summer, and only for cosmetic reasons (no query or
code changes). The DBI and DBD-Oracle has not been changed. I checked
with the DBA and he says there's been no Oracle software changes.

I'd like to find the single source of this behavior change rather than
make changes to a bunch of applications as I doubt changing the
applications will solve the underlying problem.


TIA,
Anna

--------------------------------------------------------

Anna Q. Fong, Webmaster
California Data Exchange Center

RE: Order by statement stopped working but not in all cases

Fong, Anna wrote:
> We have a bunch of applications that use DBI and DBD-Oracle
> and they've
> been running for quite some time now (months and years, depending on
> which app). Then within the last few days, one particular
> query within
> the bunch of apps stopped returning results. When I took out
> the order
> by statement in the query, it again returned results though
> not sorted.
> If I take out one join in the query, the query with the order by
> statement returns sorted results.

Do you have error handling on the statement? If not, add it. There's
various ways that changes in data could cause certain sorts of query to fail
with an error. Here's one example:

SQL> create table t (c varchar2(1));

Table created

SQL> insert into t values ('1');

1 row inserted

SQL> select * from t order by to_number(c);

C
-
1

SQL> insert into t values ('x');

1 row inserted

SQL> select * from t order by to_number(c);

select * from t order by to_number(c)

ORA-01722: invalid number



Or is the statement executing successfully, but genuinely returning zero
rows?

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

RE: Order by statement stopped working but not in all cases

Yes, and there is no DBI::errstr returned.

It is executing _as_if_ there are no rows to return. It is returning
false results:

1) the same query in SQLPLUS returns rows
2) the same query with no order by statement in the web app returns rows
3) the same query minus one join but including the order by statement in
the web app returns rows

What am I missing? I'm stumped:

- three days ago the query in the web app worked as expected
- no changes have been made to the affected web apps in 6 months or more
- no changes have been made to the DBI and DBD-Oracle
- no changes have been made to the Oracle system software



-----Original Message-----
From: Andy Hassall [mailto:andy@andyh.co.uk]=20
Sent: Friday, January 20, 2006 12:39 PM
To: Fong, Anna; dbi-users@perl.org
Subject: RE: Order by statement stopped working but not in all cases



Do you have error handling on the statement? If not, add it. There's
various ways that changes in data could cause certain sorts of query to
fail with an error. Here's one example:

[snip]

Or is the statement executing successfully, but genuinely returning
zero rows?

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

RE: Order by statement stopped working but not in all cases

On Fri, 20 Jan 2006 13:11:16 -0800, Fong, Anna wrote:

Go Anna! [1]

> - three days ago the query in the web app worked as expected - no
> changes have been made to the affected web apps in 6 months or more
> - no changes have been made to the DBI and DBD-Oracle - no changes
> have been made to the Oracle system software

But /something/ must have changed, right?

What needs to change is your presumably false belief that nothing's changed=
:-)!
And, yes, I mean that in the nicest possible way.

[1] We're in the middle of the Australian Tennis Open, and a goanna is a=
native
animal, and Go Anna! is a joke about a Russian tennis player called Anna K.=

(whose fame seems to have faded).

--
Cheers
Ron Savage, ron@savage.net.au on 21/01/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

RE: Order by statement stopped working but not in all cases

I could not find the underlying reason why this query no longer worked
as expected, but have modified each of the affected applications to work
around the issue. I'll keep my fingers crossed...



Anna =20

-----Original Message-----
From: Ron Savage [mailto:ron@savage.net.au]=20
Sent: Friday, January 20, 2006 3:22 PM
To: List - DBI users
Subject: RE: Order by statement stopped working but not in all cases



But /something/ must have changed, right?

What needs to change is your presumably false belief that nothing's
changed :-)!
And, yes, I mean that in the nicest possible way.



--
Cheers
Ron Savage, ron@savage.net.au on 21/01/2006
http://savage.net.au/index.html Let the record show: Microsoft is not an
Australian company

RE: Order by statement stopped working but not in all cases

On Fri, 20 Jan 2006 15:57:26 -0800, Fong, Anna wrote:

Hi Anna

> I could not find the underlying reason why this query no longer
> worked as expected, but have modified each of the affected
> applications to work around the issue. I'll keep my fingers
> crossed...

Which is clearly a PITA. Good luck!

--
Cheers
Ron Savage, ron@savage.net.au on 21/01/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

RE: Order by statement stopped working but not in all cases

Sorry for the OT response but I couldn't let this go.

Anna K's fame has faded?? She still has a few fans :)
Ron, if you have any influence at the Australian Open, next year
try to get them to sign a tv contract with ABC, NBC, or CBS - instead
of ESPN2 which I don't get. Thanks.

Hardy Merrill

>>> Ron Savage 1/20/2006 6:21 PM >>>

[1] We're in the middle of the Australian Tennis Open, and a goanna is a =
native=20
animal, and Go Anna! is a joke about a Russian tennis player called Anna =
K.=20
(whose fame seems to have faded).

--=20
Cheers
Ron Savage, ron@savage.net.au on 21/01/2006
http://savage.net.au/index.html=20
Let the record show: Microsoft is not an Australian company

RE: Order by statement stopped working but not in all cases

On Mon, 23 Jan 2006 07:52:05 -0500, Hardy Merrill wrote:

Hi Hardy

> Anna K's fame has faded?? She still has a few fans :)

Interest in her physical side of her database remains strong.
--
Cheers
Ron Savage, ron@savage.net.au on 24/01/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company