OIC - ERP - Resolving ORA-01403: No Data Found During ERP Import Costs ESS Job

Overview

While executing the Import Costs ESS job in Oracle ERP, the process failed with the following error:

Error:

ORA-01403: no data found

This error can be misleading because it doesn't always indicate missing transactional data. In our case, the issue was caused by incorrect parameter values being passed to the ESS job from Oracle Integration Cloud (OIC).

Error Scenario

The Import Costs ESS job completed with Error status, and the completion text displayed:

ORA-01403: no data found

As shown below:

Root Cause

The integration was retrieving ESS job parameters from a lookup table. During troubleshooting, we found that one or more parameter values stored in the lookup were incorrect.

Since the ESS job received invalid parameters, it couldn't find the expected data and returned the ORA-01403: no data found error.

Resolution

The issue was resolved by following these steps:

1. Reviewed the parameters passed to the Import Costs ESS job.
2. Compared them with the expected ERP values.
3. Identified incorrect values in the OIC lookup.
4. Updated the lookup with the correct parameter values.
5. Re-ran the integration.
6. After updating the lookup, the ESS job completed successfully without any errors.

Key Learning

When an ESS job fails with ORA-01403: no data found, don't assume the source data is missing. Also verify:

• The parameters passed to the ESS job.
• Lookup values used by the integration.
• Parameter mapping in OIC.
• Whether the parameter values match the ERP configuration.

Troubleshooting Checklist

• ✅ Verify all ESS job parameters.
• ✅ Check OIC lookup values.
• ✅ Validate parameter mapping before submitting the ESS job.
• ✅ Compare successful and failed job parameters.
• ✅ Re-run the ESS job after correcting the parameters.

Conclusion

In this case, the root cause was incorrect parameter values stored in the OIC lookup, not missing ERP data. Updating the lookup with the correct values resolved the ORA-01403: no data found error, and the Import Costs ESS job executed successfully.

Tip: Whenever an ERP ESS job throws ORA-01403, reviewing the submitted parameters should be one of the first troubleshooting steps, especially if those parameters are dynamically fetched from an OIC lookup or configuration table.