oracle tutorial webinars

ORA-01403 Error Message: “No Data Found”

Some Oracle errors are a result of coding that does not prove quite as comprehensive as desired. These types of errors involve going back through your code and creating or editing your PL/SQL to compensate for the mistake. The ORA-01403 error (“no data found”) is representative of this type of Oracle problem, and while it involves a bit more coding savvy than other errors, the fix tends to be much more long term.

The ORA-01403 error derives from an SQL query meant to return all data requested, but no data was found. The error is often associated with SELECT INTO clauses, which fetch rows or sets of columns from a database. The data gathered is then inserted into a set of predefined variables. When a SELECT INTO query fails to return a single row, the ORA-01403 error is triggered in the system. The error can also be thrown when you attempt to select data from an uninitialized row in a table, or reading past the end of file with the UTF_FILE package. To initially work around the error, you need to terminate the processing data immediately, but what from there?

To avoid this type of situation from coming about, the best approach is to make sure that your PL/SQL has the proper exceptions coded. Without exceptions, the query will attempt to offer multiple values to the variables at the end of the query. If this continues and the exceptions are left non-existent or incomplete, each time the variable is accessed again the problem will compound.

Create an exception that would allow only one row to be retrieved each time the variable is accessed. Writing an exception that states “when NO_DATA_FOUND then return ‘No data in selected variable'”. By creating the proper exceptions, or at the least returning exceptions back to the proper calling block, the ORA-01403 can largely be skirted.

There are some occasions where NOLOGGING clauses can also trigger an ORA-01403. In some SQL operations, if you encounter a redo log record using a NOLOGGING clause, the record will be skipped and changes will be applied further along. If you attempt to access the updated files at a later date, the ORA-01403 error will occur. To fix this, re-create tables from the initial controlling database.

The good news is that the ORA-01403 error is highly preventable. By creating the proper exceptions, the program should now operate free of the error and remain that way until edited again. If you are unsure of how to write proper exceptions in Oracle, work with your licensed Oracle consultant on making sure that the proper coding infrastructure is in place so that your Oracle database operates smoothly.