Home » SQL & PL/SQL » SQL & PL/SQL » Single row query returns more than one row (Oracle 10G)
Single row query returns more than one row [message #477699] Sun, 03 October 2010 21:38 Go to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
Hi

I am getting an error "Single row query returns more than one row" in an Exception block.

But in subqueries I am using IN operator not using =.

And I don't have duplicates rows in tables.

What might be reason for this error?

Thanks
HK

Report message to a moderator

Re: Single row query returns more than one row [message #477700 is a reply to message #477699] Sun, 03 October 2010 21:40 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>What might be reason for this error?
Single row query returns more than one row

Why do you expect us to debug code we can not see?

I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Single row query returns more than one row [message #477701 is a reply to message #477700] Sun, 03 October 2010 21:43 Go to previous messageGo to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
Your right. But the code is a long package which I can not post here and the reason for posting this is if any one has experienced such error for other than obvious reason they can share.

Thanks anyway

Report message to a moderator

Re: Single row query returns more than one row [message #477702 is a reply to message #477701] Sun, 03 October 2010 21:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The error should enumerate the EXACT SQL statement that fails; unless of course the package has a brain dead EXCEPTION handler that masks the failure.

You have tables. We don't.
You have data. We don't.
You have code. We don't.
You can not debug with all these details.
We can't provide specific solution for some generic error message.
Oracle is too dumb to lie. Trust the error message.

Enjoy your mystery!

Report message to a moderator

Re: Single row query returns more than one row [message #477716 is a reply to message #477701] Mon, 04 October 2010 01:08 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
hari_bk wrote on Mon, 04 October 2010 04:43
Your right. But the code is a long package which I can not post here and the reason for posting this is if any one has experienced such error for other than obvious reason they can share.

Thanks anyway

Whenever you get such a basic type of exception, doubt yourself, not Oracle.

Report message to a moderator

Re: Single row query returns more than one row [message #477721 is a reply to message #477716] Mon, 04 October 2010 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I am getting an error "Single row query returns more than one row" in an Exception block.

Exception block should contains ONLY exceptions you know what to do with, not WHEN OTHERS.

Regards
Michel

Report message to a moderator

Re: Single row query returns more than one row [message #477723 is a reply to message #477701] Mon, 04 October 2010 01:58 Go to previous messageGo to next message
sathishraj
Messages: 53
Registered: July 2010
Location: chennai
Member
its very vague!

Report message to a moderator

Re: Single row query returns more than one row [message #477780 is a reply to message #477701] Mon, 04 October 2010 07:45 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA
Senior Member
Comment out the exception block temporarily, then run it, so that your error will show you what line of code contains the problem, then post the entire query containing that line.

Report message to a moderator

Re: Single row query returns more than one row [message #477809 is a reply to message #477780] Mon, 04 October 2010 09:56 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Scalar sub queries may also generate this exception

Report message to a moderator

Re: Single row query returns more than one row [message #477840 is a reply to message #477716] Mon, 04 October 2010 15:01 Go to previous messageGo to next message
sunroser
Messages: 16
Registered: September 2010
Junior Member
Frank wrote on Mon, 04 October 2010 02:08
hari_bk wrote on Mon, 04 October 2010 04:43
Your right. But the code is a long package which I can not post here and the reason for posting this is if any one has experienced such error for other than obvious reason they can share.

Thanks anyway

Whenever you get such a basic type of exception, doubt yourself, not Oracle.


Hi guys, we are here suppose to help, not to criticize.

Understand you can't post the code, which is fine. Just please do as the following as you can address this type of issue easily.

I assume you have Toad or PL/SQL developer installed. then please choose the package, and enter debug mode. (find the way how to enter debug mode, or let us know which tool you are using), set up the watcher (or variable's value monitor) for the declared section in the package, the step over codes line to line. You will see which line of codes caused the issue.

Remember, using IN or = in your subquery is not related to this issue until you address the exact code line with issue.

Report message to a moderator

Re: Single row query returns more than one row [message #477867 is a reply to message #477840] Tue, 05 October 2010 00:28 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
What makes you think I was criticizing the original poster? I merely pointed out to him/her that his persistent search for someone who encountered the same issue (in other words, expecting Oracle was wrong) was not the path he should follow as a start.

[Edit: Post screwed up after fixing typo]

[Updated on: Tue, 05 October 2010 00:31]

Report message to a moderator

Re: Single row query returns more than one row [message #477981 is a reply to message #477867] Tue, 05 October 2010 08:42 Go to previous messageGo to next message
sunroser
Messages: 16
Registered: September 2010
Junior Member
Hi, Frank, apology if i misunderstood you, but what makes you think he is "expecting Oracle was wrong"? my understanding is that he is trying to find a guy who experienced the same issue as his one, and be able to point out the issue directly...

But anyway, seems that guy has gone though.
[EDITED by LF: removed unnecessary quote of the whole previous message]

[Updated on: Tue, 05 October 2010 15:49] by Moderator

Report message to a moderator

Re: Single row query returns more than one row [message #478415 is a reply to message #477840] Fri, 08 October 2010 10:06 Go to previous messageGo to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
Thank you sunroser! Yours is most useful reply I got for my query.

I am using Toad.

Report message to a moderator

Re: Single row query returns more than one row [message #478417 is a reply to message #478415] Fri, 08 October 2010 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Each one understands the usefulness of an answer at his/her level.

Regards
Michel

Report message to a moderator

Re: Single row query returns more than one row [message #478419 is a reply to message #478417] Fri, 08 October 2010 10:18 Go to previous messageGo to next message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
May be he is expecting like this ...
Question: What might be reason for this error?
Answer: 
ORA-01427: single-row subquery returns more than one row 
Cause: The outer query must use one of the keywords ANY, 
ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row. 
Action: Use ANY, ALL, IN, or NOT IN to specify which values 
to compare or reword the query so only one row is retrieved.


So after that he will comes with the other details.
sriram

[Updated on: Fri, 08 October 2010 10:19]

Report message to a moderator

Re: Single row query returns more than one row [message #478421 is a reply to message #478419] Fri, 08 October 2010 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
May be he is expecting like this ...

Which is totally irrelevant to what I've said.

Regards
Michel

Report message to a moderator

Re: Single row query returns more than one row [message #478422 is a reply to message #478421] Fri, 08 October 2010 10:31 Go to previous message
ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Yes as I said,May be he is expecting the answer without specifying more details....
(As everyone is asking for those )
And I totally agree with you there.

Report message to a moderator

Previous Topic: column to row
Next Topic: view
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jun 25 21:09:20 CDT 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.