While working with Oracle, I've learned more and more on better ways of handling exceptions. There are some major specifics that need to be taken note of. raise_application_error only works with custom error numbers. You cannot use it to make Oracle throw just any exception. Normally, you would use a predefined name like this:
begin
some select statement...
exception when no_data_found then
--handle exception
end;
Sometimes, the Oracle error you want to handle is just not predefined. So, you'd need a better way of handling errors and throwing some meaningful error messages back.
var b_err_code number;
begin
some select statement...
exception when others then
:b_err_code = SQLCODE;
if :b_err_code = '-00904' then
--handle exception
else
raise_application_error(-20000, 'Some Customized Error Message: ' || :b_err_code || ' Actual SQL Error: ' || SQLERRM);
end if;
end;
Some points of interest here. Using when others then allows you to handle all other cases. Over here, we store the SQL error number to the bind variable b_err_code. In Oracle, SQLCODE returns exactly that and in our example, we are expecting to handle ORA-00904 via the if condition. We also have an else part where we generate our own application error. We also use SQLERRM to return a human readable version of the SQL error message received, that would otherwise had gotten lost in the call stack. Hope this little bit helps other Oracle and SQL developers.
