Oracle provides several standard exception pragmas (essentially, a compiler directive) for exceptions found on a fairly frequent basis. These are documented in the PL\/SQL User Guide and Reference, but you can also get them at runtime using what is below:<\/p>\n
\r\nSQL> select text from dba_source where name = 'STANDARD' and lower(text) like '%pragma%exception%';\r\n\r\nTEXT\r\n--------------------------------------------------------------------------------\r\n pragma EXCEPTION_INIT(CURSOR_ALREADY_OPEN, '-6511');\r\n pragma EXCEPTION_INIT(DUP_VAL_ON_INDEX, '-0001');\r\n pragma EXCEPTION_INIT(TIMEOUT_ON_RESOURCE, '-0051');\r\n pragma EXCEPTION_INIT(INVALID_CURSOR, '-1001');\r\n pragma EXCEPTION_INIT(NOT_LOGGED_ON, '-1012');\r\n pragma EXCEPTION_INIT(LOGIN_DENIED, '-1017');\r\n pragma EXCEPTION_INIT(NO_DATA_FOUND, 100);\r\n pragma EXCEPTION_INIT(ZERO_DIVIDE, '-1476');\r\n pragma EXCEPTION_INIT(INVALID_NUMBER, '-1722');\r\n pragma EXCEPTION_INIT(TOO_MANY_ROWS, '-1422');\r\n pragma EXCEPTION_INIT(STORAGE_ERROR, '-6500');\r\n pragma EXCEPTION_INIT(PROGRAM_ERROR, '-6501');\r\n pragma EXCEPTION_INIT(VALUE_ERROR, '-6502');\r\n pragma EXCEPTION_INIT(ACCESS_INTO_NULL, '-6530');\r\n pragma EXCEPTION_INIT(COLLECTION_IS_NULL , '-6531');\r\n pragma EXCEPTION_INIT(SUBSCRIPT_OUTSIDE_LIMIT,'-6532');\r\n pragma EXCEPTION_INIT(SUBSCRIPT_BEYOND_COUNT ,'-6533');\r\n pragma EXCEPTION_INIT(ROWTYPE_MISMATCH, '-6504');\r\n PRAGMA EXCEPTION_INIT(SYS_INVALID_ROWID, '-1410');\r\n pragma EXCEPTION_INIT(SELF_IS_NULL, '-30625');\r\n pragma EXCEPTION_INIT(CASE_NOT_FOUND, '-6592');\r\n pragma EXCEPTION_INIT(USERENV_COMMITSCN_ERROR, '-1725');\r\n pragma EXCEPTION_INIT(NO_DATA_NEEDED, '-6548');\r\npragma EXCEPTION_INIT(INVALID_USERENV_PARAMETER, -2003);\r\npragma EXCEPTION_INIT(ICD_UNABLE_TO_COMPUTE, -6594);\r\n\r\n25 rows selected.\r\n\r\nSQL>\r\n<\/pre>\nThis makes it easy to use in your PL\/SQL programs, as they are available to all programs since they are in the STANDARD package.<\/p>\n
For example, you can do something like:<\/p>\n
\r\nSQL> declare\r\n 2 l number;\r\n 3 begin\r\n 4 l := 'a';\r\n 5 exception\r\n 6 when value_error then\r\n 7 dbms_output.put_line('Please enter a number rather than a letter');\r\n 8 end;\r\n 9 \/\r\nPlease enter a number rather than a letter\r\n\r\nPL\/SQL procedure successfully completed.\r\nSQL>\r\n<\/pre>\nWe can also create exceptions for the standard exceptions with a predefined pragma in the standard package. For example, we can either:<\/p>\n
\r\nSQL> declare\r\n 2 customer_id number;\r\n 3 begin\r\n 4 select c into customer_id from t0909 where c = 'a';\r\n 5 end;\r\n 6 \/\r\ndeclare\r\n*\r\nERROR at line 1:\r\nORA-01722: invalid number\r\nORA-06512: at line 4\r\n<\/pre>\n…or, in addition to the standard exception pragma, we can provide a more descriptive exception to the calling program, such as java by adding a pragma to a custom package…<\/p>\n
\r\nSQL> create or replace package my_exceptions is\r\n 2 invalid_customer_id exception;\r\n 3 pragma exception_init(invalid_customer_id,-1722);\r\n 4 end;\r\n 5 \/\r\n\r\nPackage created.\r\n<\/pre>\n…and use our custom exception definition from our program by doing something like what is below:<\/p>\n
\r\nSQL> declare\r\n 2 customer_id number;\r\n 3 begin\r\n 4 select c into customer_id from t0909 where c = 'a';\r\n 5 exception\r\n 6 when my_exceptions.invalid_customer_id then\r\n 7 dbms_output.put_line('Please ensure you search using a valid numeric customer_id.');\r\n 8 end;\r\n 9 \/\r\nPlease ensure you search using a valid numeric customer_id.\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Oracle provides several standard exception pragmas (essentially, a compiler directive) for exceptions found on a fairly frequent basis. These are documented in the PL\/SQL User Guide and Reference, but you can also get them at runtime using what is below:…<\/p>\n