ORA-01403: no data found
SELECT INTO
Ensure that the criteria specified in your query are correct and that there is data in the database that matches these criteria.
Incorporate exception handling in your PL/SQL block to manage the situation when no data is found.
BEGIN SELECT column_name INTO variable_name FROM table_name WHERE condition; EXCEPTION WHEN NO_DATA_FOUND THEN -- Handle the exception DBMS_OUTPUT.PUT_LINE('No data found.'); END;
Ensure that the data in your database is consistent and that there are no missing or unexpected values.
Modify the query to include conditions that are more likely to return results. For example, if you are searching for a specific record, ensure that the criteria match existing records.
Consider the following SQL query:
BEGIN SELECT first_name INTO v_first_name FROM employees WHERE employee_id = 9999; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employee found with the given ID.'); END;
In this example, if there is no employee with ID 9999, the NO_DATA_FOUND exception is raised, and a message is printed.
NO_DATA_FOUND
BEGIN SELECT first_name INTO v_first_name FROM employees WHERE first_name = 'John' AND last_name = 'Doe'; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employee found with the given name.'); END;
If there is no employee with the first name 'John' and last name 'Doe', the NO_DATA_FOUND exception is raised, and a message is printed.
Consider the following scenario where the data in the database has changed:
BEGIN SELECT first_name INTO v_first_name FROM employees WHERE employee_id = 100; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No employee found with the given ID.'); END;
If the employee with ID 100 has been deleted or the ID has been changed, the NO_DATA_FOUND exception is raised, and a message is printed.
ORA-01403: No Data Found