9. Send app-specific error message with RAISE_APPLICATION_ERROR.
If you execute a SELECT-INTO that does not identify any rows, the PL/SQL runtime engine raises: ORA-01403 and the error message (retrieved via SQLERRM or DBMS_UTILITY.FORMAT_ERROR_STACK) is simply "No data found".
That may be exactly what you want your users to see. But there is a very good chance you'd like to offer something more informative, such as "An employee with that ID is not in the system."
In this case, you can use RAISE_APPLICATION_ERROR, as in:
CREATE OR REPLACE PACKAGE BODY employees_mgr
IS
FUNCTION onerow (employee_id_in IN hr.employees.employee_id%TYPE)
RETURN hr.employees%ROWTYPE
RESULT_CACHE
IS
l_employee hr.employees%ROWTYPE;
BEGIN
SELECT *
INTO l_employee
FROM hr.employees
WHERE employee_id = employee_id_in;
RETURN l_employee;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
raise_application_error (
-20000,
'An employee with that ID is not in the system.');
END;
END;
Related blog post: http://stevenfeuersteinonplsql.blogsp...
========================================
Practically Perfect PL/SQL with Steven Feuerstein
Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
Watch video 9. Send app-specific error message with RAISE_APPLICATION_ERROR. online without registration, duration hours minute second in high quality. This video was added by user Practically Perfect PL/SQL with Steven Feuerstein 29 March 2016, don't forget to share it with your friends and acquaintances, it has been viewed on our site 6,940 once and liked it 36 people.