CODEWITHSUNDEEP
sqloracle-databaseplsql
samba
samba

Reputation: 3091

Exception is not handled in PL/SQL function Oracle

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;

Upvotes: 1

Views: 830

Answers (1)

Allan
Allan

Reputation: 17429

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.

Upvotes: 3

Related Questions