I took an extra moment today to look as the updated assumptions on the PL/SQL Quiz and found the DBMS_SESSION notes really useful to resolve a curious problem we’ve been having ...
The problem – for various reasons – came down to the simple reproducible SQL statement:-
select * from dual where regexp_like('aa', '[ -~]');If you run the following SQL:-
exec DBMS_SESSION.SET_NLS ('NLS_LANGUAGE' ,'DUTCH');then the regexp_like command fails with:
ORA-12728: invalid range in regular expressionOur problem was that, for the DBMS_JOB that uses this regular expression, the database server is running on a Dutch system so that task does not run automatically and has to be done manually. We can now use DBMS_SESSION to set it to American to enable it to run automatically.
Now it would be rather evil to pose a PL/SQL Quiz on this topic ...
I’ve no doubt there’s an explanation for the problem buried somewhere in how Oracle handles ‘~’ as a combining character in a UTF-8 database when the language used for the session is one that has such combined characters ...
Anyway – thought it would provide some amusement and I wanted to say “thanks”.