Amongst my favourite oracle 10G enhancements there is the support for regular expressions.
There is a whole bunch of functions to deal with strings using these powerful pattern matching descriptors so i decided to use them for implementing a procedure that unscrambles search engine URLs in pursuit of the search keywords that led a user to my site.
So, i set out and wrote this wonderful web search URL decoder for the most popular engines like Google and Yahoo and installed it on my development box, just to find out minutes later that, at times, i was getting a strange error called ORA-21779: Duration not active.
The short error description wasn't very helpful, what kind of duration is it talking about?!?
Don't panic, let's see first what the Error Messages manual for version 10.1 says about it.
Cause: User is trying to use a duration that has been terminated
Action: User should avoid performing such operation.
I don't know the rest of you guys, but i'd love to know the "backstage" of the birth of this message. I get this fancy picture in my mind of two tired oracle techies working late in an office where everyone else is at home and sleeping and one screams at the colleague sitting at the other end of a big open space room:
- JOE! What was that error you got this morning?
- ...ion not active! (very weak)
- What?
- ...ion NOT ACTIVE! (somewhat louder)
- HUH?!?
- &$%!!
- (long silence)... and how do you fix it?
- Just don't do it!
- (long silence)
At any rate, back to my problem, it took me a while to figure out that the error was raised by function regexp_replace, for two reasons:
#1 the error was not returned every time i executed the query even against the same inputs.
#2 the function is contained in a so-called "process" inside a page definition of Oracle Apex (Application Express 2.0) , so, at first i thought it was returned by the underlying query of the report being displayed, not by the after submit process.
So, in the end, i commented out the function call and the error stopped occurring.
Said that, i turned up to oracle metalink in search of more descriptive explanations of the problem but i found just one note mentioning regexp functions called inside "pipelined functions".
I also found another note (or was it the same?) suggesting that patch 10.1.0.5 could fix the problem, so i installed the patch too, with little success regarding the point, but introducing the ORA-12640 problem i related in a previous post.
It was not my lucky day, definitely.
After several attempts to isolate the real cause of the problem however, i found out that the error can happen even without nesting regexp_replace inside pipeline functions, but still i cannot reproduce this behaviour sistematically, there is some erraticism in it.
To add more to the picture, this seems to be a Windows only problem.
I could not manage to reproduce it on Solaris, which is good on the other hand because it's my target production environment.
I also left a message in the metalink forum, but nobody has replied up to date.
So, at the end of the day, the function in my development box is still haunted by these infrequent ORA-21779 errors and i don't dare to install the new functionality in a production environment even if, so far, all the tests have returned positive results.
Any suggestions?
PS: UPDATED July 13, 2007
if you are getting this error in an Oracle Application Express page, try out this workaround.
5 comments:
Happens for me on Windows and Linux. NOT on Mac OS X 10.4.10
We had the same problem.
Trouble is IMHO exception handling in Oracle Pl/Sql
-- what we did and what caused trouble:
BEGIN
SELECT titel
INTO this_titel
FROM lib_titel
WHERE titel_nosymbol = p_nosym;
EXCEPTION
WHEN no_data_found
THEN
RETURN trim(initcap(regexp_replace(regexp_replace(p_string, '\.', '. '), '( ){2,}', ' ') ) );
END;
-- what we do now and what works:
BEGIN
d_found := 0;
SELECT titel
INTO this_titel
FROM lib_titel
WHERE titel_nosymbol = p_nosym;
d_found := 1;
EXCEPTION
WHEN no_data_found
THEN
NULL;
END;
IF d_found = 0
THEN
RETURN trim(initcap(regexp_replace(regexp_replace(p_string, '\.', '. '), '( ){2,}', ' ') ) );
END IF;
I get the sodding thing while using utl_smtp. May well be that a regexp_replace is buried in there. Code is wrapped though so I don't know.
Seems that if for some reason the previous connection to the mail server without closing_data and connection the next connection gets the duration not active error.
What really Bugs me is that I can't seem to:
a. trap the error. even When others fails miserably
b. preemptivly close any existing connection.
Only workaround I have found that consistently works is sending email by using a Dbms_job... Not exactly optimum, I would like to notify the user that an email has actually been sent!
I don't know if ORA-21779 is bound to a single circumstance, i mean, it could well be a generic "hat" for a variety of buffer/memory related issues.
I mean, if you take ORA-01428, it's a generic error message for an invalid value, but is not specific to any function, so, who knows if ORA-21779 belongs to this category.
Thank you,
Flavio
I have it in 9.2.0.6.0 in the context of pipeline functions ...
Post a Comment