1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Predefined exceptions in PL/SQL

Discussion in 'SQL PL/SQL' started by tyro, Nov 17, 2008.

  1. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Predefined exceptions in PL/SQL

    Predefined exceptions in PL/SQL are declared globally in a package called STANDARD, defining the PL/SQL environment. So, one does not need to declare these exceptions. Instead we can write handlers for these predefined exceptions using their standard names. Below is a list giving the Exception Name, Oracle Error and their SQLCODE value.

    [table]Exception|Oracle Error|SQLCODE Value
    ACCESS_INTO_NULL|ORA-06530|-6530
    CASE_NOT_FOUND|ORA-06592|-6592
    COLLECTION_IS_NULL|ORA-06531|-6531
    CURSOR_ALREADY_OPEN|ORA-06511|-6511
    DUP_VAL_ON_INDEX|ORA-00001|-1
    INVALID_CURSOR|ORA-01001|-1001
    INVALID_NUMBER|ORA-01722|-1722
    LOGIN_DENIED|ORA-01017|-1017
    NO_DATA_FOUND|ORA-01403|+100
    NOT_LOGGED_ON|ORA-01012|-1012
    PROGRAM_ERROR|ORA-06501|-6501
    ROWTYPE_MISMATCH|ORA-06504|-6504
    SELF_IS_NULL|ORA-30625|-30625
    STORAGE_ERROR|ORA-06500|-6500
    SUBSCRIPT_BEYOND_COUNT|ORA-06533|-6533
    SUBSCRIPT_OUTSIDE_LIMIT|ORA-06532|-6532
    SYS_INVALID_ROWID|ORA-01410|-1410
    TIMEOUT_ON_RESOURCE|ORA-00051|-51
    TOO_MANY_ROWS|ORA-01422|-1422
    VALUE_ERROR|ORA-06502|-6502
    ZERO_DIVIDE|ORA-01476|-1476[/table]​

    The above list is not exhaustive, feel free to add to this codes and exceptions you might be familiar with.

    Cheers :D
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Let see how and when these predefined exceptions are raised (as per the information from PL/SQL User's Guide and Reference )

    [table]EXCEPTION| CAUSE FOR EXCEPTION
    ACCESS_INTO_NULL|Your program attempts to assign values to the attributes of an uninitialized (atomically null) object.
    COLLECTION_IS_NULL|Your program attempts to apply collection methods other than EXISTS to an uninitialized (atomically null) nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
    CURSOR_ALREADY_OPEN|Your program attempts to open an already open cursor. A cursor must be closed before it can be reopened.A cursor FOR loop automatically opens the cursor to which it refers. So, your program cannot open that cursor inside the loop.
    DUP_VAL_ON_INDEX|Your program attempts to store duplicate values in a database column that is constrained by a unique index.
    INVALID_CURSOR|Your program attempts an illegal cursor operation such as closing an unopened cursor.
    INVALID_NUMBER|In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.)
    LOGIN_DENIED|Your program attempts to log on to Oracle with an invalid username and/or password.
    NO_DATA_FOUND|A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table.SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls a aggregate function will never raise NO_DATA_FOUND.The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised.
    NOT_LOGGED_ON|Your program issues a database call without being connected to Oracle.
    PROGRAM_ERROR|PL/SQL has an internal problem.
    ROWTYPE_MISMATCH|The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. For example, when an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible.
    SELF_IS_NULL|Your program attempts to call a MEMBER method on a null instance. That is, the built-in parameter SELF (which is always the first parameter passed to a MEMBER method) is null.
    STORAGE_ERROR|PL/SQL runs out of memory or memory has been corrupted.
    SUBSCRIPT_BEYOND_COUNT|Your program references a nested table or varray element using an index number larger than the number of elements in the collection
    SUBSCRIPT_OUTSIDE_LIMIT|Your program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.
    SYS_INVALID_ROWID|The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid.
    TIMEOUT_ON_RESOURCE|A time-out occurs while Oracle is waiting for a resource.
    TOO_MANY_ROWS|A SELECT INTO statement returns more than one row.
    VALUE_ERROR|An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR.In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)
    ZERO_DIVIDE|Your program attempts to divide a number by zero.
    [/table]
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    It is also possible to associate a pre-defined Oracle error to user defined exception. This is done using PRAGMA EXCEPTION_INIT .

    PRAGMA EXCEPTION_INIT is a compiler which causes all references to the exception name to be interpreted as the associated Oracle Error. ie it binds pre-defined oracle error to the user defined exception.

    Basically this is used to name the unnamed pre-defined Oracle errors. There is no need to RAISE as it will be raised by Oracle as and when the error is encountered.


    Code (Text):

    SQL> BEGIN
      2   INSERT INTO EMP_EXC_DEMO(EMPNO, DEPTNO, SAL)
      3                    VALUES (1000,NULL,13000);
      4   commit;
      5  EXCEPTION
      6  WHEN OTHERS THEN
      7     DBMS_OUTPUT.PUT_LINE (SQLERRM);
      8  END;
      9  /
    ORA-01400: cannot insert NULL into ("SCOTT"."EMP_EXC_DEMO"."DEPTNO")

    PL/SQL procedure successfully completed.

    SQL>
     
    Now we need to correlate -01400 Oracle error to used defined exception .

    Code (Text):

    SQL> DECLARE
      2   null_not_allowed EXCEPTION;
      3   PRAGMA EXCEPTION_INIT(null_not_allowed, -01400);
      4  BEGIN
      5   INSERT INTO EMP_EXC_DEMO(EMPNO, DEPTNO, SAL)
      6                    VALUES (1000,NULL,13000);
      7   commit;
      8  EXCEPTION
      9  WHEN null_not_allowed THEN
     10     DBMS_OUTPUT.PUT_LINE ('Null is not allowed here');
     11  WHEN OTHERS THEN
     12     DBMS_OUTPUT.PUT_LINE (SQLERRM);
     13  END;
     14  /
    Null is not allowed here

    PL/SQL procedure successfully completed.

    SQL>
     
    In short unnmaed pre-defined Oracle error (ORA:-01400 ) is named null_not_allowed in this session.