This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Oct 23, 2013 9:48 PM by BillyVerreynne RSS

select exception

francy77 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    too_many_rows

    table does not exist

    into clause is expected in this statement

    + plenty of others.

  • 3. Re: select exception
    KeithJamieson Expert
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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 Guru
    Currently Being Moderated


    Very nicely put.

     

    Best regards,

      Nikolay

  • 11. Re: select exception
    BillyVerreynne Oracle ACE
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points