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

Fixing OCI Load Balancer Error: “Invalid SSL Configuration – Certificate Alias Required”

Fixing OCI Load Balancer Error: “Invalid SSL Configuration – Certificate Alias Required”

Recently, while creating a Load Balancer in OCI using the new UI, I ran into a strange SSL error that took me a good amount of time to troubleshoot. Since I didn’t find anything useful on Google or Oracle MOS, I am documenting my experience here so it can help others facing the same issue.

This article explains the issue, what causes it, and the exact workaround that finally fixed it.

💥 The Error Message

During Load Balancer creation, at the Configure Listener step, I selected:

• Load Balancer Managed Certificate
• Uploaded SSL Certificate
• Uploaded CA Certificate
• Uploaded Private Key

As soon as I clicked Submit, OCI returned this error:

1 problem occurred:

Load balancer

Invalid SSL configuration: [Constraint listener:listener_lb_2025-1202-1553 violation: Invalid listener ssl configuration.

* Constraint sslConfiguration violation: Certificate alias or certificateIds / trustedCertificateAuthorityIds required ]

This was confusing because all required certificate components were uploaded correctly.

🔍 Root Cause (New OCI UI Issue)

I spent a lot of time searching for the root cause:

No information on Google

No Oracle blogs

No Oracle MOS note

Nothing in developer forums

This pointed to one thing — a UI-level issue in the new OCI Console (2025 release).

Although the SSL certificate was uploaded, the new interface fails to map the certificate to the listener being created. OCI internally expects:

certificateAlias

OR

certificateIds

OR

trustedCertificateAuthorityIds

Because the new UI does not pass these values correctly, the listener creation fails.

🛠️ Working Solution (Workaround)

The fix is very simple:

➡️ Do NOT upload an SSL certificate during Load Balancer creation.

Instead:

Create the Load Balancer without SSL

After creation, manually upload your certificate

Once you follow this sequence, everything works perfectly.

📌 After Load Balancer Creation — Two Ways to Add Certificates

OCI gives you two options to add SSL certificates after LB creation:

1️⃣ Certificate Service Managed Certificates (Recommended)

Steps to Upload Certificate into OCI Certificate Service

• Click on the three horizontal lines (☰) in the top-left corner
• Navigate to Identity & Security
• Under Certificates, click Certificates
• Choose the correct compartment
• Click the Add Certificate button
• Upload your certificate details
• Save

Once uploaded:

• Go to your Load Balancer
• Open Certificate & Ciphers
• Under Certificate service managed certificates, click Assign Certificate
• Select the certificate you uploaded
• Update the listener

2️⃣ Load Balancer Managed Certificates (Manual Upload)

Use this if you want to upload certificates directly to the Load Balancer.

Steps:

Open your Load Balancer

• Go to Certificate & Ciphers
• Scroll to Load balancer managed certificates
• Click Add Certificate
• Upload:

SSL Certificate

CA Certificate

Private Key

• Save

Update the Listener to use this certificate

This approach is straightforward and works perfectly with the new UI.

Thanks & Regards,

Chandan Tanwani