Exception is not handled in PL/SQL function Oracle

Question

I have written the following PL/SQL function which returns how long the procedure takes. The function compiled successfully and updates the salary for an employee as expected.

I also wanted to handle an exception if wrong arguments are provided to the function. However if I enter non-existing employee id the exception is not handled the way I tried to do that in the function. And I'm getting a standard Error report.

Another problem is that when the function updates salary successfully I don't see the returned value for INTERVAL DAY TO SECOND.

What can I do to see my custom error message with 'An ERROR Was encountered' string and the returned value?

CREATE OR REPLACE function myfunction1(e_empno_in IN NUMBER, e_sal_in IN NUMBER)

RETURN INTERVAL DAY TO SECOND
IS 
e_empno emp.empno%TYPE := e_empno_in;
e_sal emp.sal%TYPE := e_sal_in;

thestart TIMESTAMP;
stopwatch INTERVAL DAY TO SECOND;

BEGIN
    SELECT current_timestamp INTO thestart FROM dual;
    UPDATE emp SET sal = e_sal WHERE empno = e_empno;
    SELECT current_timestamp-thestart INTO stopwatch FROM dual;
RETURN stopwatch;

EXCEPTION
WHEN OTHERS 
THEN
dbms_output.put_line('An ERROR Was encountered' || DBMS_UTILITY.format_error_stack());
RAISE; 
END;

BEGIN
dbms_output.put_line(myfunction1(7654, 2559));
END;

Allan · Accepted Answer · 2017-10-24 13:29:18Z

3

NO_DATA_FOUND is only ever raised when a select in an implicit cursor fails to return any rows. when update, delete, or insert fail to find a row, Oracle does not treat that as an error.

To make your code perform as desired, you'll need to test sql%rowcount after the update, then raise an error yourself.

BEGIN
    SELECT CURRENT_TIMESTAMP INTO thestart FROM DUAL;

    UPDATE emp
    SET    sal = e_sal
    WHERE  empno = e_empno;

    IF SQL%ROWCOUNT = 0 THEN
        raise_application_error (-20001, 'No row found for id ' || e_empno);
    END IF;

    stopwatch := CURRENT_TIMESTAMP - thestart;

    RETURN stopwatch;
END;

Other Notes

You're typically much better off just letting your custom error propagate, rather than sending it to DBMS_OUTPUT. An unhandled error is harder to ignore.
Since you've designed this as a function, the right way to get the stopwatch value you're returning is to handle it in whatever is calling the function.

edited Oct 24, 2017 at 13:29

answered Oct 23, 2017 at 22:19

Allan

17.5k5 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ed Heal Over a year ago

Why not thestart := SYSTIMESTAMP; etc?

samba Over a year ago

Thank you it helped. But I had to change the error number since I got Error report - ORA-21000: "error number argument to raise_application_error of -10001 is out of range". And I stil do not understand how to get the returned stopwatch to be printed out?

Barbaros Özhan Over a year ago

@samba yes, exchange -10001 with -20101 as an example. Since userly defined error numbers range -20000 and -20999.

Collectives™ on Stack Overflow

Exception is not handled in PL/SQL function Oracle

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related