PL/SQL Specific error messages

[hemanr]

Hi folks,

Please look at the following code:

Exception
when others then
sOraError := to_char(sqlcode);
if sOraError = '-1407' then
-- Trap Not Null error
p_sMessage := 'Clean Vendor could not be saved as Clean
Vendor Name is empty';
elsif sOraError = '-1' then
-- Trap Unique Key Constraint Error
p_sMessage := 'Clean Vendor ' || p_sName || ' could not be
saved as Clean Vendor Name is not unique';
else
p_sMessage := 'Clean Vendor ' || p_sName || ' could not be
saved due to Oracle Error: ' || to_char(sqlcode) || ', ' || sqlerrm;
end if


The issue with this is, in case there are two unique key columns and if any
of the colmn fails validation, we get OraError as -1.
With this we will not be able to give specific messages.
'Clean Vendor ' || p_sName || ' could not be saved as Clean Vendor
Name is not unique';

OR
'Clean Vendor ' || p_sName || ' could not be saved as Clean Vendor ID
is not unique';

So what do you suggest to handle this scenario? Giving oracle error
description might not make sense to the end user....

Thanks!

[jmodic]

If I understand correctly you have two UK constraints (or one PK and one UK) and in case of the DUP_VAL_ON_INDEX exception you want to indicate to the user which one of the two constrained columns is violating the uniqueness constraint. You can do it like this:

...
exception
  when dup_val_on_index then
    begin
      select null into v_dummy from vendors where vendor_id = p_id;
      -- this p_id is allready in the database!
      p_sMessage := 'Clean Vendor ' || p_sName ||
                    ' could not be saved as Clean Vendor ID ' ||
                    'is not unique';
    exception
      when no_data_found then
        -- This p_id is not yet in the database, so VendorName
        -- must be violating the unique constraint
        p_sMessage := 'Clean Vendor ' || p_sName ||
                      ' could not be saved as Clean Vendor Name ' ||
                      'is not unique';
    end;
....
end;

[hemanr]

Thanks jmodic!!!