Duplicate error code throws while using sqlerm and exception in oracle apex

Question

I'm creating a process in Oracle Apex 5.0. Please help me to get rid some duplicate error codes.

I've multiple conditions where error needs to be thrown. But at the end of the block when I write Exception block with sqlerrm it throws error code two times.

begin
    -----------------------------
    -----some code statements----
    -----------------------------

    if (<condition 1>) then
        -----------------
        ----some code----
        -----------------

    elsif(<condition 2>) then
        raise_application_error(-20001, '----statement1----');

    elseif(<condition 3>) then
        raise_application_error(-20002), '----statement2----');

    end if;

exception
    when others then
        raise_application_error(-20003, SQLERM);
end;

Now, if any error occurs because of those IF statements then result will look like

ORA-20001: ORA-20003: ----statement1----.

But I need to show like, for example

ORA-20001: ----statement1----

How can I get it? Please help me to find what should I do here.

Answer 1

RAISE_APPLICATION_ERROR throws a bespoke error. But like any other exception that our application might hurl it is trapped by the local exception handler.

Your exception handler is raising a new bespoke exception for any and all errors. Don't do this. You can easily fix your problem by removing the exception block. All exceptions will be propagated up the calling stack without any modification.

begin
    -----------------------------
    -----some code statements----
    -----------------------------

    if (<condition 1>) then
        -----------------
        ----some code----
        -----------------

    elsif(<condition 2>) then
        raise_application_error(-20001, '----statement1----');

    elseif(<condition 3>) then
        raise_application_error(-20002), '----statement2----');

    end if;

end;

Alternatively, you could employ user-defined exceptions.

declare
    x_condition2 exception;
    x_condition3 exception;
begin
    -----------------------------
    -----some code statements----
    -----------------------------

    if (<condition 1>) then
        -----------------
        ----some code----
        -----------------

    elsif(<condition 2>) then
        raise x_condition2;

    elseif(<condition 3>) then
        raise x_condition3;

    end if;

exception
    when x_condition2 then
        raise_application_error(-20001, '----statement1----');
    when x_condition3 then
        raise_application_error(-20002, '----statement2----');

end;

You still needn't bother with WHEN OTHERS unless you have some specific processing you want to apply to all errors (such as logging them). There is no value in just re-raising an exception in the handler section because propagation happens by default.

---

@Deep asked

cant we make both exceptions?

Yes we can do this:

declare
    x_condition2 exception; 
    PRAGMA EXCEPTION_INIT(x_condition2,-20001); 
    x_condition3 exception; 
    PRAGMA EXCEPTION_INIT(x_condition3,-20002);
….

Declaring an exception creates a bespoke exception. PRAGMA EXCEPTION_INIT associates a bespoke error number with that exception. I wouldn't bother doing this in an anonymous block, because we don't get much value from it. We would still need to execute raise_application_error to return our tailored error messages.

Where pragma exception_init becomes really useful is when we are passing exceptions between program units. Exceptions which need to be handled by calling programs should be declared in package specs. Using pragma exception_init means that we can identify errors using sqlcode which can be helpful for tasks such as looking up standard error messages from a table, providing user help text, etc.