The requirement is to display ONLY the user defined error message ,but the RAISE APPLICATION ERROR displays additional trace of the ORA-60512 error displaying schema name and SP name with line number..
I wanted to directly show the SP custom error msg in application.
IS THIS POSSIBLE to display only custom message without any addidtional trace.
SP Test code:
CREATE OR REPLACE PROCEDURE get_employee_name(
p_emp_id IN employee.emp_id%TYPE,
p_emp_name OUT employee.emp_name%TYPE
) AS
-- Define a user-defined exception for "Employee not found" scenario
employee_not_found EXCEPTION;
PRAGMA EXCEPTION_INIT(employee_not_found, -20201); -- Assign a custom error code
-- Declare a variable to store the count of matching rows
v_count NUMBER;
BEGIN
-- Check if the employee exists
SELECT COUNT(*)
INTO v_count
FROM employee
WHERE emp_id = p_emp_id;
-- If employee not found, raise the custom exception
IF v_count = 0 THEN
RAISE employee_not_found;
END IF;
-- Retrieve the employee name
SELECT emp_name INTO p_emp_name
FROM employee
WHERE emp_id = p_emp_id;
EXCEPTION
-- Catch other exceptions
when employee_not_found then
RAISE_APPLICATION_ERROR(-20201, 'Employee with ID ' || p_emp_id || ' not found.');
WHEN OTHERS THEN
-- Handle other exceptions
RAISE_APPLICATION_ERROR(-20002,SQLERRM);--DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
Error Display Message:
Error report -
ORA-20201: Employee with ID 100 not found.
ORA-06512: at "C##GATEWAYT.GET_EMPLOYEE_NAME", line 30
ORA-06512: at line 4
Expected error Display Message:
ORA-20201: Employee with ID 100 not found.
CALL STMT:
DECLARE
v_emp_name employee.emp_name%TYPE;
BEGIN
get_employee_name(100, v_emp_name);
END;