OraFAQ Forum: Client Tools => Same query, same server, different clients and different results

Members

Help

Home

Home » SQL & PL/SQL » Client Tools » Same query, same server, different clients and different results (Oracle9i Enterprise Edition)

Show: Today's Messages :: Show Polls :: Message Navigator

E-mail to friend

Same query, same server, different clients and different results [message #318057]

Mon, 05 May 2008 08:38

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!!

Report message to a moderator

Re: Same query, same server, different clients and different results [message #318058 is a reply to message #318057 ]

Mon, 05 May 2008 08:41

anacedent
Messages: 5027
Registered: July 2005
Location: surf meets turf in 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]

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

Michel Cadot
Messages: 15238
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

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

Report message to a moderator

Re: Same query, same server, different clients and different results [message #318094 is a reply to message #318062 ]

Mon, 05 May 2008 09:50

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!!

Report message to a moderator

Re: Same query, same server, different clients and different results [message #318096 is a reply to message #318094 ]

Mon, 05 May 2008 10:12

Michel Cadot
Messages: 15238
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

I forgot NLS settings in my list.
Thanks next tim eI will add it.

Regards
Michel

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 17 00:19:22 CDT 2008

Total time taken to generate the page: 0.05111 seconds

.:: Forum Home :: Site Home :: Wiki Home :: Contact :: Privacy ::.