Hello Connor, All,
I found this thread following your today Asktom session, which was excellent as always :)
Checking the 12.2 SQL documentation on CHECK constraints, https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/constraint.html#GUID-1055EA97-BA6F-4764-A15F-1024FD5B6DFE
it contains the following sentence:
If the condition of a check constraint depends on NLS parameters, such as
NLS_DATE_FORMAT, Oracle evaluates the condition using the database values of the
parameters, not the session values. You can find the database values of the NLS
parameters in the data dictionary view NLS_DATABASE_PARAMETERS. These values
are associated with a database by the DDL statement CREATE DATABASE and never
If Oracle was indeed following this rule, then isn't this supposed to avoid
the ORA-02436 error ?
Or, alternatively, maybe in a future version Oracle could add an additional argument to the REGEXP functions, for specifying NLS parameter values explicitly, like in other SQL functions ?
Thanks a lot & Best Regards,
April 20, 2018 - 2:55 am UTC
My understanding is that check constraints are "caught in the crossfire".
You *could* use regexp_exp in a function-based index....which creates the problem (index versus table data). The resolution to that issue is to mark regexp_... as non-deterministic.
The check constraint then simply says: "This function is non deterministic"