Home » Other » Client Tools » Same query, same server, different clients and different results (Oracle9i Enterprise Edition)
Same query, same server, different clients and different results [message #318057] Mon, 05 May 2008 08:38 Go to next message
g_velvet
Messages: 2
Registered: May 2008
Junior Member
Hey guys!
I have a quick & tricky question for you Smile

I have a query (SQL select statement - no DML) with a strange behaviour. This is failing when the application executes it, but in some cases I can perform it manually with no issues. These are the different panoramas/cases I get, when I execute it (same query all the time) through different Oracle clients:

1. Client: TOAD 9.0 (using Oracle client 9i)
Status: Query succeeds (everything works perfect)

2. Client: Oracle SQL Developer (using Oracle client 9i, as well as 10G)
Status: Query succeeds (everything works perfect)

3. Client: TOAD 9.0 (using Oracle client 10G)
Status: Query failed
Reason: A function (called within my query) is returning more than one result (ORA-01422: exact fetch returns...)

4. Client: Oracle SQLPlus (using Oracle client 9i)
Status: Query failed
Reason: A function (called within my query) is returning more than one result (ORA-01422: exact fetch returns...)

5. Client: Oracle SQLPlus (using Oracle client 10G)
Status: Query failed
Reason: A function (called within my query) is returning more than one result (ORA-01422: exact fetch returns...)

Note: I have executed the function on all sessions (failed and non-failed) but it succeeds on all of them bringing just one result.

All sessions are connected with same user, to same Oracle instance and the values regarding "v$nls_parameters" are the same for each of them.

I hope you have some idea about this Smile

Thanks in advance!!
Re: Same query, same server, different clients and different results [message #318058 is a reply to message #318057] Mon, 05 May 2008 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated above

[Updated on: Mon, 05 May 2008 08:42] by Moderator

Report message to a moderator

Re: Same query, same server, different clients and different results [message #318062 is a reply to message #318057] Mon, 05 May 2008 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Note: I have executed the function on all sessions

Which function?

You think you access the same database but you don't.
You think you execute the same query but you don't.
You think you use the same schema but you don't.
You think you use the same function but you don't.
You think you [...] but you don't.
Change [...] by all what we can imagine.

Regards
Michel
icon7.gif  Re: Same query, same server, different clients and different results [message #318094 is a reply to message #318062] Mon, 05 May 2008 09:50 Go to previous messageGo to next message
g_velvet
Messages: 2
Registered: May 2008
Junior Member
Hi Michel...
When I compared the NLS values for all sessions at the beginning, I didn't see that NLS_COMP one, was set to BINARY instead of ANSI on sessions where it was failing. After changing this, the query was successful on all sessions (different clients).

I realized that same Toad client(v9) changes this parameter automatically, depending on which Oracle client version you use.

Thanks anyway!!
Regards!!
Re: Same query, same server, different clients and different results [message #318096 is a reply to message #318094] Mon, 05 May 2008 10:12 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I forgot NLS settings in my list.
Thanks next tim eI will add it.

Regards
Michel
Previous Topic: SP2-0158: unknown SET option "up"
Next Topic: How does a SQL Statement work?
Goto Forum:
  


Current Time: Fri Mar 29 04:34:34 CDT 2024