HR_OPERATING_UNITS Returns No Data in SQL Developer — The Hidden NLS Language Issue in Oracle Apps

HR_OPERATING_UNITS Returns No Data in SQL Developer — The Hidden NLS Language Issue in Oracle Apps

Recently, while working with a custom user/schema in Oracle E-Business Suite (EBS), I hit an unexpected issue. I executed the following simple queries:

select count(*) from apps.hr_operating_units;

select count(*) from hr_operating_units;

To my surprise, both queries returned zero rows.

I knew very well that the HR Operating Units were already defined in the system, and other users could see the data. So why was my SQL Developer session not returning anything?

After a bit of investigation, I discovered the real culprit — NLS Language settings in SQL Developer.

🧩 Understanding the Problem

1. HR_OPERATING_UNITS is not a simple table

This object is a view owned by the APPS schema. It pulls data from multiple HR and FND tables:

HR_ALL_ORGANIZATION_UNITS

HR_OPERATING_UNITS

FND_LOOKUP_VALUES

and other HR-related lookup tables

These underlying tables often use lookup codes and meanings that are language dependent in EBS.

2. SQL Developer session language matters

When you connect using SQL Developer, it sets certain environment parameters automatically, including:

NLS_LANGUAGE

NLS_TERRITORY

NLS_DATE_FORMAT

If NLS_LANGUAGE does not match the base language installed in Oracle EBS (typically AMERICAN), then any HR or FND lookup join that depends on translated values may fail to match — which results in no rows returned.

This is exactly what happened with the HR_OPERATING_UNITS query.

The Fix: Set NLS_LANGUAGE to AMERICAN

Once I changed the NLS settings in SQL Developer, everything started working correctly.

Steps to Fix in SQL Developer

Go to

Tools → Settings → Database → NLS

Set the following:

NLS_LANGUAGE = AMERICAN

NLS_TERRITORY = AMERICA

Reconnect your database session.

Rerun the query:

SELECT COUNT(*) FROM apps.hr_operating_units;

Most Oracle EBS installations use AMERICAN as the base language.The HR and FND lookup tables store the translations for AMERICAN only. So when SQL Developer requested data using another language like:

ENGLISH

INDIA

FRENCH

GERMAN

or any other locale

The view internally filtered out rows because there were no lookup translations for that language.

Switching to AMERICAN ensured lookup matches were found.

This prevents issues with:

- Value sets not showing correct meanings

- HR views returning no rows

- Date conversion errors

- Responsibility-based security failing

- Incorrect FND lookup behavior

Thanks & Regards,

Chandan Tanwani