Appreciate all the responses, but I shall give a little more information
about the scenario.
BEGIN
select...from tab1
where col1=c1.col1 and col2=c1.col2
EXCEPTION
WHEN TOO_MANY_ROWS THEN
BEGIN
select...from tab1
where col1=c1.col1 and col2=c1.col2 and col3=c1.col3
EXCEPTION
WHEN TOO_MANY_ROWS THEN
BEGIN
select ...from tab1
where col1=c1.col1 and col2=c1.col2 and col3=c1.col3
and col4=c1.col4
EXCEPTION
WHEN TOO_MANY_ROWS THEN
---handle here
WHEN NO_ROWS_FOUND THEN
RAISE;
END
WHEN NO_ROWS_FOUND THEN
RAISE;
END
WHEN NO_ROWS_FOUND THEN
--handle here
WHEN OTHERS THEN
--handle here
END
Input record(which is in C1=cursor record) needs to be validated with the
data in table(tab1).
Say If Query-2 finds single record, it just comes out of all the loops, need
not run Query 3(i.e., it need not match column 4 at all).
As of now, It is success in traversing into the TOO_MANY_ROW exceptions
conditions, but when there is a NO_ROWS_FOUND exception, "RAISE" is not able
to traverse back, its throwing an error. I want to re-raise the NO_ROW_FOUND
exception and handle it in one place(in outer most loop).
How can I handle NO_ROW_FOUND error in one place in current situation?
or what an alternative way to handle the entire structure?
Thanks
Manju
On Tue, May 25, 2010 at 8:38 AM, Michael Moore <[email protected]>wrote:
> PS
> SELECT count(*) should be
>
> Select count(*) cnt from mytab where rownum < 3;
>
>
> On Tue, May 25, 2010 at 8:36 AM, Michael Moore <[email protected]>wrote:
>
>> The answer depends on the requirement of the application. It seems
>> unlikely that you would not be expecting TOO_MANY_ROWS, but if you did get
>> that exception, then you want to SELECT from a different table.
>>
>> If this is really what you want to do, then my solution would probably be
>> to put each of the SELECT statements in a Package procedure. The output
>> parameters from those procedures would include a STATUS which would tell me
>> if I got TOO_MANY_ROWS or NO_ROWS_FOUND.
>>
>> Other solutions would be to SELECT count(*) first and then check the
>> result, thereby avoiding exception handling all together. Raising exceptions
>> can slow your program down. Also, depending on your data you could do
>>
>> SELECT count(*) cnt , max( myvar1), max(myvar2) from whatever;
>> You can then look at cnt, and if it is 1, continue on as normal else deal
>> with problem.
>> If you use this technique, use code comments to document why you are using
>> MAX.
>>
>>
>> Regards,
>> Mike
>>
>>
>>
>> On Mon, May 24, 2010 at 9:01 PM, Manju <[email protected]> wrote:
>>
>>> Hi Team,
>>>
>>> I need help in the below structure of PL/SQL block asap. Is this good
>>> to do? or any better way to handle the same.
>>>
>>> BEGIN
>>> select...
>>> EXCEPTION
>>> WHEN TOO_MANY_ROWS THEN
>>> BEGIN
>>> select ...
>>> EXCEPTION
>>> WHEN TOO_MANY_ROWS THEN
>>> BEGIN
>>> select ...
>>> EXCEPTION
>>> WHEN TOO_MANY_ROWS THEN
>>> ---handle here
>>> WHEN NO_ROWS_FOUND THEN
>>> RAISE;
>>> END
>>> WHEN NO_ROWS_FOUND THEN
>>> RAISE;
>>> END
>>> WHEN NO_ROWS_FOUND THEN
>>> ---handle here
>>> WHEN OTHERS THEN
>>> ---handle here
>>> END
>>>
>>> Thanks!
>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "Oracle PL/SQL" group.
>>> To post to this group, send email to [email protected]
>>> To unsubscribe from this group, send email to
>>> [email protected]
>>> For more options, visit this group at
>>> http://groups.google.com/group/Oracle-PLSQL?hl=en
>>
>>
>>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to [email protected]
> To unsubscribe from this group, send email to
> [email protected]
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
