SQL Statement Executed From PLSQL Unit May Raise ORA-00979 When NLS_COMP='LINGUISTIC' and NLS_SORT='BINARY_CI' Are Set (Doc ID 2971678.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

Execution of PLSQL code having a SQL statement with GROUP BY clause may fail with error "ORA-00979: not a GROUP BY expression" only when NLS_COMP is set to 'LINGUISTIC' and NLS_SORT is set to 'BINARY_CI'. The issue can be demonstrated using a simple testcase mentioned below.

The same SQL statement works fine when executed outside the PLSQL code.

Changes

The procedure is executed for the first time after setting NLS_COMP and NLS_SORT to 'LINGUISTIC' and 'BINARY_CI', respectively.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.