Oracle has a number of predefined server exceptions. Each of them has a name. However there are very few of them that are generic. You can catch and handle these exceptions in your PL/SQL code.You can also generate your own exceptions. For this you use the RAISE_APPLICATION_ERROR function. You pass it an error number. The range of user defined error numbers is -20,000 to -20,999.
You can catch specific exceptions in your exception handler section. I already knew that you could also catch all types of exceptions using the WHEN OTHERS clause. However I also found out that this cause must be last in the list of exceptions that are handled.
SQLERRM is a function that returns the message of an exception that got raised. Similarly SQLCODE is a function which returns the error number of an exception. Both of these cannot be used in a SQL statement. You need to assign the return value of these functions to a variable before using the values.
Sor far I have only covered the information I learned in the first two days of instructor led training. In a future post I will go into writing your own procedures and functions in PL/SQL.
