Hi folks,
I posted this question a few days ago: https://community.oracle.com/inbox?objectType=2&objectID=12723427&customTheme=otn
This is a follow on from that. I'm trying to use raise_application_error to propagate detailed error messages up the stack to the calling application layer, so the user gets a meaningful error message describing what has gone wrong. I'm fairly stuck on this, so I wanted to give a short example and ask for advice on what to change. I've deliberately created a much shorter example than what I'd really be dealing with.
In my example, a Java program calls calculate_bill, which calls calculate_commission, which calls update_record. In the example below, if a user entered a number which corresponded to a record which couldn't be updated because it didn't exist, I'd like them to get the message: "Error calculating bill. Error calculating commission. No record exists to be updated" or something to that effect.
So in short, two questions:
1. What is the best practice, most efficient, most tidy way to pass error messages up the stack for the end user in the application layer?
2. Is my method of concatenating the error messages in the "when ... then" exceptions in the calling procedures appropriate? (For example, see bold, underlined sentence in example)
3. Does anyone have any suggestions on how to present tidier output from the code. I want to give the user a detailed, meaningful error message, i.e. something more than "Error calculating commission". I'd like to say *WHY* it has encountered an error.
4. Is the logging satisfactory where is it (all in top layer, as all errors get propagated up stack to there).
Example:
(Errors used in code):
-20000 : Error in top level procedure
-20001 : Error in middle level procedure
-20002 : Error in bottom level procedure
Java code:
try {
// call calculate_bill
exception (SQLException ex)
// output oracle code and relevant message.
Oracle code:
create or replace procedure calculate_bill(in_num NUMBER)
is
error_calculating_commission EXCEPTION;
error_updating_record EXCEPTION;
PRAGMA EXCEPTION_INIT (error_calculating_commission, -20001);
PRAGMA EXCEPTION_INIT (error_updating_record , -20002);
begin
if in_num > 2 then
calculate_commission(in_num);
else
//log error
raise_application_error(-20000, 'Error calculating bill. ' || 'Record number doesn''t exist.', false);
end if;
exception
when error_calculating_commission then
//log error
raise_application_error(SQLCODE, 'Error calculating bill. ' || SQLERRM, false);
when error_updating_record then
//log error
raise_application_error(SQLCODE, 'Error calculating bill. ' || SQLERRM, false);
when others then
//log error
raise_application_error(-20000, 'Unknown error encountered calculating bill.', false);
end;
create or replace procedure calculate_commission(in_num NUMBER)
is
begin
if in_num < 30 then
//log error
raise_application_error(-20001, 'Number too small to calculate commission.', false);
elsif in_num >= 30 and < 40 then
declare
error_storing_record EXCEPTION;
PRAGMA EXCEPTION_INIT (error_storing_record , -20002);
begin
update_record(in_num);
exception
when error_storing_record then
raise_application_error(SQLCODE, 'Error calculating commission. ' || SQLERRM, false);
when others then
raise_application_error(-20001, 'Unknown error encountered calculating commission.', false);
else
raise_application_error(-20001, 'Number too large to calculate commission', false);
end if;
end;
create or replace procedure update_record(in_num NUMBER)
is
begin
//some SQL query with a where clause, where in_num equals something
exception
when no_data_found then
raise_application_error(-20002, 'No record exists to be updated', false);
when others then
raise_application_error(-20002, 'Unknown error encountered updating record.', false);
end;