ORA-00100 oracle error | no data found

Oracle error ORA-00100: no data found occurs when a query does not return any rows from the database. This is usually seen in PL/SQL blocks or when using SQL SELECT statements inside procedures or functions. It essentially means that the query did not match any rows in the database.

Check the Query:

Make sure the WHERE clause in the query is accurate and the data exists for the condition you are searching for.

Use Exception Handling in PL/SQL

In PL/SQL, you can handle this error by using the EXCEPTION block to handle situations where no rows are returned. This way, instead of the program stopping with an error, you can define what happens when no rows are found.

Example of Exception Handling in PL/SQL

BEGIN
   SELECT column_name 
   INTO variable_name
   FROM table_name
   WHERE condition;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      -- Handle the situation, e.g. assign a default value or log an error.
      DBMS_OUTPUT.PUT_LINE('No data found');
END;
/

Using SQL%NOTFOUND

You can also use the SQL%NOTFOUND implicit cursor attribute to detect whether the SELECT statement found any rows.

Example:

BEGIN
   SELECT column_name 
   INTO variable_name
   FROM table_name
   WHERE condition;
   
   IF SQL%NOTFOUND THEN
      DBMS_OUTPUT.PUT_LINE('No data found.');
   END IF;
END;
/

This will allow your code to check if no rows were returned and handle the situation accordingly.