This would probably best be handled by a separate select statement like:
begin
select 1 into v_dummy from tab where c1 = var1 and c2 = var2 and c3 = var3
and c4 = var4;
exception
WHEN TOO_MANY_ROWS THEN
---handle here
WHEN NO_ROWS_FOUND THEN
NULL;
end;
On Wed, Jun 2, 2010 at 11:18 AM, reach manju <[email protected]> wrote:
> Hi Mike, All,
>
> Hope you all are doing well.
>
> I need your help with the same issue that was resolved.
>
> *Say if the table has data like below(2 rows with same data).
> Below query give "No data found", but I need to trap the TOO_MANY_ROW
> Exception. How can I get this done?*
>
>
> Insert into TAB
> (C1,
> C2, C3, C4)
> Values
> *(1, 2, 3, 4);*
> Insert into TAB
> (C1,
> C2, C3, C4)
> Values
> * (1, 2, 3, 4);*
>
> COMMIT;
>
> set linesize 132
> define var1 = 1
> define var2 = 2
> define var3 = 3
> define var4 = 4
> SELECT c1,
> case when (c1c = 1) then null else c2 end c2,
> case when (c1c = 1 or c2c = 1) then null else c3 end c3,
> case when (c1c = 1 or c2c = 1 or c3c = 1) then null else c4 end c4
> FROM (SELECT c1,
> c2,
> c3,
> c4,
> COUNT (*) OVER (PARTITION BY c1) c1c,
> COUNT (*) OVER (PARTITION BY c1, c2) c2c,
> COUNT (*) OVER (PARTITION BY c1, c2, c3) c3c,
> COUNT (*) OVER (PARTITION BY c1, c2, c3, c4) c4c
> FROM tab t1)
> WHERE (c1 = &var1 AND c1c = 1)
> OR (c1 = &var1 and c2 = &var2 AND c2c = 1 and c1c != 1)
> OR (c1 = &var1 and c2 = &var2 AND c3 = &var3 AND c3c = 1 and c2c != 1)
> OR (c1 = &var1 and c2 = &var2 AND c3 = &var3 AND c4 = &var4 AND c4c = 1
> and c3c != 1);
>
>
> On Tue, May 25, 2010 at 2:51 PM, Michael Moore <[email protected]>wrote:
>
>> Glad it worked. Make sure you test it well.
>> Mike
>>
>>
>> On Tue, May 25, 2010 at 2:32 PM, reach manju <[email protected]> wrote:
>>
>>> Perfect Mike!.. You are great. Just modified a bit to fit in.. It
>>> works..:-)
>>> I can trap, no_data_found as well.
>>>
>>> Thanks Tons!
>>> Thanks for all the help.
>>>
>>> ~Manju
>>>
>>>
>>> On Tue, May 25, 2010 at 1:00 PM, reach manju <[email protected]>wrote:
>>>
>>>> You are right Andrej, I forgot to mention INTO in the statement,
>>>>
>>>> I am working on the suggestion given by Mike. Shall post the results
>>>> soon. m sure it should work for the scenario I have..:-)
>>>>
>>>>
>>>> On Tue, May 25, 2010 at 12:31 PM, Andrej Hopko <[email protected]>wrote:
>>>>
>>>>> it's okay - I hadn't even read the error details and just shout what I
>>>>> saw
>>>>>
>>>>> now I found, that NO_ROWS_FOUND may be used
>>>>> I use only NO_DATA_FOUND for handling this kinf og exception
>>>>> (and have no idea of difference between them =) )
>>>>>
>>>>> regards,
>>>>>
>>>>> Andrej Hopko
>>>>>
>>>>>
>>>>> On 25. 5. 2010 21:26, Michael Moore wrote:
>>>>>
>>>>> Yes, my example was for straight SQL. I assumed that Manju would know
>>>>> how to put it into a PL/SQL context.
>>>>> Regards,
>>>>> Mike
>>>>>
>>>>> On Tue, May 25, 2010 at 12:21 PM, Andrej Hopko <[email protected]>wrote:
>>>>>
>>>>>> in PL/SQL context this select syntax does not work
>>>>>>
>>>>>>
>>>>>> On 25. 5. 2010 18:56, reach manju wrote:
>>>>>>
>>>>>> select...from tab1
>>>>>> where col1=c1.col1 and col2=c1.col2
>>>>>>
>>>>>>
>>>>>> select...from tab1
>>>>>> *INTO variable*
>>>>>>
>>>>>> where col1=c1.col1 and col2=c1.col2
>>>>>>
>>>>>> you forgot INTO statement which is mandatory!
>>>>>>
>>>>>> Andrej Hopko
>>>>>> --
>>>>>> 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
>>>>>
>>>>
>>>>
>>> --
>>> 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
>
--
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
