NLS_SORT behaviour changed between Oracle 12.1 and Oracle 19 ?

User_RVFU1

Hi,

after a migration of the oracle database from 12.1 to 19.5 we are facing an issue with regexp in PL/SQL (we are running the DB on Linux ). In Oracle 12.1 we see that the following regexp returns NULL - which is expected:

select regexp_substr('bbbb', '[A-Z0-9]{4}',1,1,'c') from dual;

This works for sessions using NLS_SORT = 'BINARY', and for sessions using NLS_SORT = 'GERMAN' - we have both types.

However in Oracle 19c the result depends on the setting of NLS_SORT. If NLS_SORT is set to BINARY, we also get NULL. But if NLS_SORT is set to GERMAN, we get

SQL> alter session set NLS_SORT='GERMAN';

Session altered.

SQL> SELECT PARAMETER ,VALUE FROM nls_session_parameters WHERE parameter = 'NLS_SORT';

PARAMETER           VALUE

------------------------------ ----------------------------------------------------------------

NLS_SORT            GERMAN

SQL> select regexp_substr('bbbb', '[A-Z0-9]{4}',1,1,'c') from dual;

REGEXP_SUBSTR('BBBB','[A-Z0-9]{4

--------------------------------

bbbb

Note that this result is different for other lowercase strings, e.g. 'aaaa' as the first parameter returns NULL. And no - I cannot easily force all sessions to use NLS_SORT = 'BINARY' - at least I have no idea how to do that.

My questions are:

1. Why does that happen ?
2. Do we have any chance to get the 12.1 behaviour without changing the PL/SQL code, i.e. by means of a central config parameter ?

Thanks & best regards

Reinhard