ORA-21779, the final revenge?

Yes, it's the third time i have to talk about this awful, elusive, erratic, annoying oracle error.

But there are chances that we are finally approaching the end of this nightmare.

I was testing a new, top-secret ;-) function that uses REGEXP_REPLACE as part of his logic and is called several times within a report region of an Oracle Application Express page and almost immediately i began hitting this haunting problem.

ORA-21779: Duration not active

I found myself screaming: "NO! NOT AGAIN!".

And this time it wasn't occurring on my Windows machine but on a full fledged Solaris server.

So, i contacted my friend John Scott who was fairly surprised to hear about that and together we immediately set out in pursuit of a solution.

The worse thing about this error is in that it's an intermittent one.
It may appear three times in a row then it can disappear for two times, then once again and so on, without any meaningful pattern.

At any rate, after a huge amount of try-this and try-that, John had an excellent intuition:

"It could have to do with the session character set".

He based this assumption on the fact that he experienced the problem only when he was looking at the page in the Italian version, but apparently the error didn't occur when the page was in English.

Given the fact that Apex, as part of its globalization logic, alters some sessions parameters depending on the current language setting, whose detection method is defined in the application attributes, it is perfectly possible that, by doing so, it's affecting the way REGEXP_REPLACE works when it is embedded in an Apex report.

With this theory in mind, i initially created two page processes, one occurring Before Regions and the other one occurring After Regions.
The former saves the current NLS_LANGUAGE into an application item and temporarily sets NLS_LANGUAGE='AMERICAN', the latter sets NLS_LANGUAGE back to its previous value.

bug fix part#1 (to be run right before the report):

begin
select value
into :SAVED_NLS_LANGUAGE
from nls_session_parameters
where parameter = 'NLS_LANGUAGE';

execute immediate 'ALTER SESSION SET NLS_LANGUAGE=''AMERICAN''';
end;

bug fix part #2 (to be run right after the report)

begin
execute immediate 'ALTER SESSION SET NLS_LANGUAGE='
|| chr(39) || :SAVED_NLS_LANGUAGE || chr(39);
end;

And you know what?
ORA-21779 is gone!
Since i implemented the ALTER SESSION fix, i haven't seen this error any more.

At this point i started refining the bug fix, because i didn't want to run these PL/SQL snippets before and after each and every region, but just before and after the offending one, so i converted the page processes into two special PL/SQL based page regions, surrounding the main report region.
And the report works fine.

Although we apparently managed to get rid of the problem, we couldn't precisely identify the root cause. The attempts to reproduce the error in a simpler scenario have failed, so it's not just a matter of setting NLS_LANGUAGE='ITALIAN' and call REGEXP_REPLACE, there must be something else going on behind the scene.

I am rather sure that it's a combination of factors:

1. the use of REGEXP_REPLACE;
2. the NLS_LANGUAGE setting;
3. the function call to REGEXP_REPLACE inside a report region, as part of an iterative process where a CLOB object is eventually returned perhaps?
4. some other component we don't even suspect of?
   

The bad aspect with bugs like this is in that it makes really hard to submit a TAR request because of the complex setup required to reproduce the problem and without a minimal set of steps showing the problem, there are few chances to draw the attention of Oracle Support and get it fixed.

See message translations for ORA-21779 and search additional resources.