Calling user defined exception but instead when others than exception is raising

Question

I have a anonymous block like below

DECLARE
exc1 EXCEPTION;
i integer:='1';
BEGIN
 BEGIN
    IF i = 1 THEN
        RAISE exc1;
    END IF;

 EXCEPTION
    WHEN OTHERS THEN
        raise_application_error(-20481,
                    'Error while inserting/update into Table- ' ||
                    SQLERRM);
 END;
EXCEPTION
WHEN exc1 THEN
    raise_application_error(-20001, 'test123');

END;

I just want to raise the exc1 exception. But here instead when others then exception is raising. I specifically raise the exception exc1 in if condition block so it has to call right?

The reason your OTHERS exception handler is being executed is because the exc1 handler is not in scope. when your if block executes its true code. — Jeff Holt
– Jeff Holt, Commented Aug 12, 2020 at 1:46

Sayan Malakshinov · Accepted Answer · 2020-08-12 01:40:41Z

The best option here is to have just one exception block:

DECLARE
exc1 EXCEPTION;
i integer:='1';
BEGIN
    IF i = 1 THEN
        RAISE exc1;
    END IF;
EXCEPTION
    WHEN exc1 THEN
        raise_application_error(-20001, 'test123');

    WHEN OTHERS THEN
        raise_application_error(-20481,
                    'Error while inserting/update into Table- ' ||
                    SQLERRM);
END;
/

But if you really need nested anonymous block with own exceptions blocks, you need to add one more exception handler:

DECLARE
exc1 EXCEPTION;
i integer:='1';
BEGIN
     BEGIN
        IF i = 1 THEN
            RAISE exc1;
        END IF;

     EXCEPTION
        WHEN exc1 THEN
            raise;
        WHEN OTHERS THEN
            raise_application_error(-20481,
                        'Error while inserting/update into Table- ' ||
                        SQLERRM);
     END;
EXCEPTION
    WHEN exc1 THEN
        raise_application_error(-20001, 'test123');
END;
/

PS. I'd suggest you do not use when others then without adding original exception into the error stack, ie 3rd parameter of raise_application_error() should be true:

DECLARE
exc1 EXCEPTION;
i integer:='1';
BEGIN
    IF i = 1 THEN
        RAISE exc1;
    END IF;
EXCEPTION
    WHEN exc1 THEN
        raise_application_error(-20001, 'test123', true);

    WHEN OTHERS THEN
        raise_application_error(-20481,
                    'Error while inserting/update into Table- ' ||
                    SQLERRM
                    , true);
END;
/

Collectives™ on Stack Overflow

Calling user defined exception but instead when others than exception is raising

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related