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:
Why does that happen ?
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