I was having trouble with one of my SPs and I needed to handle it correctly. As a result I needed to “throw” (raise) the exception. In that case we have three oppurtunities. SQLCODE and SQLERRM can be used to output the message.
- Throw one of the default exceptions
These exceptions are defined by the database itself. Like ZERO_DIVIDE NO_DATA_FOUND exceptions. OTHERS will catch everything other that you have specified. To have the behaviour of try catch statements, it’s a good practice to write your exception handler in begin and blocks.
1 2 3 4 5 6 7 8 begin --DO SOMETHING EXCEPTION WHEN ZERO_DIVIDE THEN --UPDATE SOMETHING WHEN OTHERS THEN P_CODE:= SQLCODE; P_MSG := SQLERRM; end;
- Throw user defined exceptions
We are not limited to oracle’s internal exception types we can declare our exception variable and throw (raise) it.
1 2 3 4 5 6 7 8 9 10 11 12 userisnotonline EXCEPTION; begin --DO SOMETHING if x>5 then raise userisnotonline; END IF; COMMIT; WHEN userisnotonline then ROLLBACK; P_message := 'user is not online'; raise userisnotonline; end;
- Raise an application error
If you want to provide a customized output for your error messages or want to make something more complex than oracle’s exceptions you can write your own application errors. User defined exception can not contain any information, you can not specify the output or code of the error. raise_application_error command can be utilized for sending your custom error messages.
The first parameter is the error number. We have -20000 to -20999 numbers that we can use.
1 2 3 4 -- insert something, get the primary key to a variable. if p_key >3 then raise_application_error(-20001, 'This field cant be lower than 3'); end if;