Friday, July 13, 2007

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.

8 comments:

Tony said...

Interesting. Very Interesting.
I'm getting it using just Pl/sql though.

1. the use of REGEXP_REPLACE;
Unknown
2. the NLS_LANGUAGE setting;
True.NLS_LANGUAGE=NORWEGIAN
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?
Running string replace operations on a clob.
4. some other component we don't even suspect of?
Probably....

As you say, hard to reproduce with any consistency, and as it might just be occuring where NLS_LANGUAGE!=AMERICAN it might not get much attention.

For any Yanks out there the problem seems to be occuring in Europe, you know, where history comes from.

Byte64 said...

I am pretty sure that if we could nail it down, we could get all the attention we need, even if we live in a province of the empire...

;-)

Thanks for sharing your problem.

Flavio

Anonymous said...

Hi Tony,
i just came across this post when i hit this error.
In my case, a java Program was calling a stored procedure which has utl_http and regexp_replace in it.

The procedure worked without any errors when called from sqlplus or dbms_scheduler.

I could reproduce it though using sqlplus.I got this error only when using regexp_replace in a pl/sql Block.I didn't get the error using regexp_replace in a select statement.

I am in Hamburg Germany, so may be this error is for people in Europe :)

Here are my Tests:

SQL> alter session set nls_language='german';

Session wurde geändert.

SQL> select regexp_replace('msisdn=1234567890','msisdn=(.+)','\1') msisdn from dual;

MSISDN
----------
1234567890

SQL> alter session set nls_language='american';

Session altered.

SQL> select regexp_replace('msisdn=1234567890','msisdn=(.+)','\1') msisdn from dual;

MSISDN
----------
1234567890

SQL> declare
2 msisdn varchar2(20);
3 begin
4 msisdn := regexp_replace('msisdn=1234567890','msisdn=(.+)','\1');
5 dbms_output.put_line('msisdn = '||msisdn);
6 end;
7 /
msisdn = 1234567890

PL/SQL procedure successfully completed.

SQL> alter session set nls_language='german';

Session wurde geändert.

SQL> /

Session wurde geändert.

SQL> declare
2 msisdn varchar2(20);
3 begin
4 msisdn := regexp_replace('msisdn=1234567890','msisdn=(.+)','\1');
5 dbms_output.put_line('msisdn = '||msisdn);
6 end;
7 /
msisdn = 1234567890

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> alter session set nls_language='italian';

Session altered.

SQL>
SQL> declare
2 msisdn varchar2(20);
3 begin
4 msisdn := regexp_replace('msisdn=1234567890','msisdn=(.+)','\1');
5 dbms_output.put_line('msisdn = '||msisdn);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-21779: duration not active


SQL> select regexp_replace('msisdn=1234567890','msisdn=(.+)','\1') msisdn from dual;

MSISDN
----------
1234567890

SQL> select regexp_replace('msisdn=1234567890','msisdn=(.+)','\1') msisdn from dual;

MSISDN
----------
1234567890

SQL> declare
2 msisdn varchar2(20);
3 begin
4 msisdn := regexp_replace('msisdn=1234567890','msisdn=(.+)','\1');
5 dbms_output.put_line('msisdn = '||msisdn);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-21779: duration not active


SQL>

I will ask my DBA to ask Oracle support for this.

Regards,

Pasko

Byte64 said...

Very interesting Pasko!
So, having narrowed down the problem to a well-fenced test-case, it's much easier to submit a TAR.

Please let us know if you succeeded in submitting the problem to Oracle Support.

Thanks,
Flavio

Tony Reed said...

Beautiful Pasko.
Let us know how it goes!

/Tony

APC said...

I have just experienced this problem with the following conditions:
(1) Oracle 9i on Solaris
(2) So obviously no regex involved
(3) NLS settings are AMERICAN
(4) The query does drive from a pipelined function

Cheers, APC

Hazi said...

I have an APEX application in 3 languages (EN, FR, ES), REGEXP_REPLACE was working perfectly in English and I had ORA-21779 in French and Spanish. Your solution of changing the NLS_LANGUAGE has worked without a hitch.

Byte64 said...

great,
that's the way to do it!

Thank you,
Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles