1 2 Previous Next 25 Replies Latest reply: Oct 23, 2013 11:48 PM by Billy~Verreynne RSS

    select exception

    francy77

      Hi all,

      i'm asking what kind of exception can raise a select statement excluding NO_DATA_FOUND;

       

      For example i try to run the following:

       

      select * from departments where departments_id=11;

       

      In a situation like that what kind of error oracle can raise?

      I'm asking this because i have some procedure that just do a select statment and i want to know if there is a valid reason to put the exception others at the end of the procedure.

       

      thanks really much

       

      Francesco;

        • 1. Re: select exception
          Karthick_Arp

          When ever I do a SELECT..INTO i check for these 2 exceptions

           

          ORA-01403: no data found

          ORA-01422: exact fetch returns more than requested number of rows

           

          You can use NO_DATA_FOUND and TOO_MAYN_ROWS to handle the above exceptions.

          • 2. Re: select exception
            Pablolee

            too_many_rows

            table does not exist

            into clause is expected in this statement

            + plenty of others.

            • 3. Re: select exception
              Keith Jamieson

              In general, you should avoid using the when others exception.

              You should always explicitly code for the particular exception.

               

              also plsql also allows you to declare your own exceptions, so you can raise application_specific exceptions.

               

              When it is used , it is generally used incorrectly like the example below:

               

              Wrong way

               

              begin

                 null;

              exception

              when others

              then

                   null;

              end;

               

              The reason this is wrong, is because it will hide any error that you get.

               

               

              If it is used, it should have a raise statement or a raise_application-error statement

              Correct Way

               

              begin

                 null;

              exception

              when others

              then

                   raise;

              end;

               

              The issue however is this when an error is reported, then the line number that is reported is the line number that the raise statement is on, rather than the line number that the actual error occurs on.

              We can actually find the line number, but only if we code the exception handler to report it.

               

              Here are actual examples

               

              declare

              v_dummy

              begin

                     select  dummy

                     into     v_dummy

                     from dual

                    where 1=0;

                 

              exception

              when no_data_found

              then

                   raise;

              end;

               

               

              or equally valid depending on your business rues;

               

              declare

              v_dummy

              begin

                     select  dummy

                     into     v_dummy

                     from dual

                    where 1=0;

                 

              exception

              when no_data_found

              then

                   null;

              end;

              • 4. Re: select exception
                Hoek

                i want to know if there is a valid reason to put the exception others at the end of the procedure.

                Usually there's no reason at all to use the WHEN OTHERS exception, if that's what you mean.

                You only catch errors you expect, like a NO_DATA_FOUND, DUP_VAL_ON_INDEX and so on.

                Some more insights on this subject (and there are plenty):

                Ask Tom ""unhandled exceptions" a problem?"

                Ask Tom "when other than exception, and raise ap..."

                • 5. Re: select exception
                  Mukesh75

                  You can handle in procudure or you can use user defined exception, but for that you have to handle by

                  checking the count in the table, other wise you can use others in place of no_data_found.


                  • 6. Re: select exception
                    AlbertoFaenza

                    Hi,

                     

                    If I have to be picky the statement you posted it is an SQL statement and will not return any exception or Oracle error if data is not found.

                    See below an example with sample DEPT table:

                     

                    SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 23 10:28:03 2013

                     

                    Copyright (c) 1982, 2010, Oracle.  All rights reserved.

                     

                     

                    Connected to:

                    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

                    With the Partitioning, OLAP, Data Mining and Real Application Testing options

                     

                    SQL> select * from dept where deptno=11;

                     

                    no rows selected

                     

                    SQL>

                     

                    Different question if you include it in a PL/SQL block but in this case you need to store the output in a variable.

                     

                    SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 23 10:32:30 2013

                     

                    Copyright (c) 1982, 2010, Oracle.  All rights reserved.

                     

                     

                    Connected to:

                    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

                    With the Partitioning, OLAP, Data Mining and Real Application Testing options

                     

                    SQL> declare

                      2     v_dname    dept.dname%type;

                      3  begin

                      4     select dname into v_dname from dept where deptno=11;

                      5  end;

                      6  /

                    declare

                    *

                    ERROR at line 1:

                    ORA-01403: no data found

                    ORA-06512: at line 4

                     

                     

                    SQL>

                    You can handle this case with no_data_found exception:

                    SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 23 10:37:21 2013

                     

                    Copyright (c) 1982, 2010, Oracle.  All rights reserved.

                     

                     

                    Connected to:

                    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

                    With the Partitioning, OLAP, Data Mining and Real Application Testing options

                     

                    SQL> declare

                      2     v_dname    dept.dname%type;

                      3  begin

                      4     select dname into v_dname from dept where deptno=11;

                      5  exception

                      6     when no_data_found

                      7     then

                      8        -- you may put some code additional code here before raise

                      9        raise;

                    10  end;

                    11  /

                    declare

                    *

                    ERROR at line 1:

                    ORA-01403: no data found

                    ORA-06512: at line 9

                     

                     

                    If this error is something you want to handle you may decide to replace raise with a specific code.

                    I.e.: if I don't find data then I set dname to a default value.

                     

                    In case you need additional information post an example (some code), describe the logic and post and your expected output.

                    Have a look at: Re: 2. How do I ask a question on the forums?

                     

                    Regards.

                    Alberto

                    • 7. Re: select exception
                      Purvesh K

                      There are couple of more errors you might face. Most prominent of them would be Not Enough Values (ORA-00947) because you continue to use Asterix (*) instead of specific column names. Addition of new columns will cause the SQL to fail as it will not have any holder for the new columns, unless you specify them.

                      Another error could be datatype conversion error etc. There will be many more but if the code you write is robust enough, you will avoid most to exceptions.

                       

                      It is not a bad practice to use When Others, provided you are logging the error into your error tables and propogating the Exception using RAISE. For any other implementation that does not follow this, When Others should be not considered for use.

                      • 8. Re: select exception
                        Nikolay Savvinov

                        Hi,

                         

                        the purpose of EXCEPTION clause is to handle situations when an error might be okay. "This SELECT didn't return any rows, but that's ok, please don't interrupt the normal program execution workflow". Otherwise you don't have to do anything about exceptions -- you just let them happen. For example, suppose that somebody messed up privileges on the table and the SELECT statement fails with ORA-00942 -- does it really make sense to catch this exception just to re-throw it in the next line? No (unless you're doing some additional database error logging, e.g. for scheduler jobs), so you just leave it un-caught. The end user or calling code will see the error and handle it on their own.

                         

                        Best regards,

                          Nikolay

                        • 9. Re: select exception
                          Billy~Verreynne

                          Hoek wrote:

                           

                          Usually there's no reason at all to use the WHEN OTHERS exception, if that's what you mean.

                           

                          Kind of disagree with that statement.

                           

                          IMO it should simply read "usually there is no reason for using exceptions".

                           

                          When there is a reason, make sure it is a valid reason and sane exception. Same basic rule for all exceptions - and not just for WHEN OTHERS.

                           

                          People seem to tend to treat WHEN OTHERS differently. It is no worse than any other exception. As with all exceptions, there need to be valid reasons for handling that specific exception. It is not an issue or more justification needed for a WHEN OTHERS and less justification needed for something like WHEN NO_DATA_FOUND. It is an issue of understanding WHAT an exception handler does, and WHEN and HOW to use an exception handler.

                          • 10. Re: select exception
                            Nikolay Savvinov


                            Very nicely put.

                             

                            Best regards,

                              Nikolay

                            • 11. Re: select exception
                              Billy~Verreynne

                              francy77 wrote:

                              In a situation like that what kind of error oracle can raise?

                               

                              Wrong question to ask. The right questions to ask are

                              a) do I need to use an exception to protect resources?

                              b) do I need to react when an error occurs?

                               

                              There are 3 basic reasons for dealing with an exception.

                               

                              1. The code has an open UTL_FILE handle, or UTL_TCP handle, or an allocated temp LOB or ref cursor - and that resource needs to be released when there is an error. In which case the standard approach is to use OTHERS, trap unknown exceptions, release the resource, and re-raise the exception. (question a)

                               

                              2. It is not an error. Your function returns funky date. If there is no funky date found in table foo, funky date defaults to today. This means NO_DATA_FOUND is not an error. In which case the code needs to catch that exception, prevent it from propagating up the call stack, and return sysdate. (question b)

                               

                              3.  It is an error, but not a meaningful enough application specific error. E.g. function foo returns funky date and does lookups in the CUSTOMER and INVOICES  tables. A NO_DATA_FOUND does not tell the caller what the application error is - but if that is changed to an application NO_CUSTOMER_FOUND or NO_INVOICE_FOUND exception, the caller knows the application error. (question b)

                               

                              Asking what exceptions there could be, ignores the basic above - ignores the questions you need to ask to determine if you need to address one of these three basic reasons for dealing with exceptions.

                              • 12. Re: select exception
                                Hoek

                                I understand your point, Billy, and agree with you (except for one thing), however nowadays after seeing so many wrong when others exceptions (not followed by a raise) every day on the forum, I'll tend to discourage the use of a when others by default, until I'm sure an OP really understands what the few pro's and many con's of a WHEN OTHERS are.

                                It is no worse than any other exception.

                                Here I tend to disagree.

                                It is worse because it is often abused as a 'one size fits all'- exception handler (just see Mukesh' reply in this very same thread: "other wise you can use others in place of no_data_found." which is BAD advice) , as a "swallowing"-mechanism.

                                Using a RAISE is still often not done. Always smells like "let's just catch anything that occurs and be done with it, Joe", imho.

                                • 13. Re: select exception
                                  Hoek

                                  other wise you can use others in place of no_data_found.

                                  Classic example of abusing a WHEN OTHERS, why on earth would you do that?

                                  • 14. Re: select exception
                                    francy77

                                    May be i wasn't clear; try to explain better:

                                     

                                    I know to catch NO_DATA_FOUND and TOO_MANY_ROW;

                                     

                                    A part this two kind of exception, what kind of error can occurs during a select inside a pl/sql procedure?

                                     

                                    Memory finished? disk broken?

                                     

                                    it is necessary to catch the exception with WHEN OTHERS and of course do a raise statement? or it is not necessary?

                                    the procedure is called from a package and obviusoly when the exception occurs the control is given back to the package.

                                    1 2 Previous Next