Hi Mike Please correct me If i am wrong - I write this 2nd query in no_row_found exception of the 1st query and handle both the exceptions there?
hm it works, but since I have used this logic in many places with different criteria, do you think it will be good? or any suggestion to handle this better? Thanks Manju On Wed, Jun 2, 2010 at 11:46 AM, Michael Moore <[email protected]>wrote: > 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 > -- 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
