The strange case of oracle text query rewrite templates: ORA-29902

I don't really expect that you have ever used an oracle text query rewrite template during your life as a developer.
The more i delve into this matter, the more i feel like a missionaire.

I post questions in the forums and i get virtually no answers.
I search for solutions on metalink and i can't find anything significant.

Am i really the only dude who ever attempted to use query templates most advanced features since they exist?

End of rant.

And now for the freshly baked quirk of the day.

I created a query rewrite template in order to expand a text search to include stemming and prefixes. I understand that at this point i've started talking to a few fellows, so i won't get upset if you leave the discussion now ;-)

You can see my original postings on Oracle Text Forum.
After a while i realized that my original xml query template was flawed by a syntax error, there was a missing < sign before the second closing SEQ element.
It wasn't just a typo, i cut and pasted the original query snippet taken from my SQL Command window, so i had actually executed that piece of xml.

Or at least i thought.

I suddenly felt excited by this finding because i expected that some unexpected failures that i was experiencing when adding more rewrite rules could be caused by the invalid syntax, so i set out and repeated all my previous attempts after correcting the problem.

But i got puzzling results. Sometimes i got syntax errors like:

ORA-29902: error in executing ODCIIndexStart() routine ORA-20000: Oracle Text error:

Sometimes not.
Sometimes i got less rows than expected.

I soon realized that the position of the invalid SEQ block is important.
If the offending SEQ block is in the last position inside the PROGRESSION block, then an error is raised. If it is followed by a valid SEQ block, not only the syntax error is not raised, but the rewrite rule is partially executed, that is the first TRANSFORM rule is applied, but the following one is ignored. If a third one is present, it will be executed as well.
And don't think it was easy to spot this.

So, in the end, i don't know how Oracle Text is crunching this xml fragment. Probably it's just giving a small bite, spitting what tastes bad.
Initially i thought it was parsing the string using the internal XML parser, but now i think it is not, otherwise the invalid syntax should be detected at all times.

Perhaps oracle text is parsing this string with some other homebrew routine that accepts a fake XML fragment for performance reasons. In the end there would be some overhead if the full fledged XML parser were invoked just for parsing a short xml fragment.

But i don't really expect anyone to confirm or deny this.

Happy parsing.

---

ORA-29902: errore durante l'esecuzione della routine ODCIIndexStart()
ORA-29902: error al ejecutar la rutina ODCIIndexStart()
ORA-29902: error en executar la rutina ODCIIndexStart()
ORA-29902: erreur d'exécution de la routine ODCIIndexStart()
ORA-29902: Fehler bei der Ausführung von Routine ODCIIndexStart()
ORA-29902: σφάλμα στην εκτέλεση της ρουτίνας ODCIIndexStart()
ORA-29902: fejl under udførelse af ODCIIndexStart()-rutine
ORA-29902: ett fel inträffade när rutinen ODCIIndexStart() kördes
ORA-29902: feil ved utføring av ODCIIndexStart()-rutine
ORA-29902: virhe suoritettaessa ODCIIndexStart()-rutiinia
ORA-29902: hiba történt az ODCIIndexStart() rutin végrehajtása során
ORA-29902: eroare la execuţia rutinei ODCIIndexStart()
ORA-29902: Fout bij uitvoering van ODCIIndexStart()-routine.
ORA-29902: erro na execução da rotina ODCIIndexStart()
ORA-29902: erro ao executar a rotina ODCIIndexStart()
ORA-29902: ошибка в выполнении программы ODCIIndexStart()
ORA-29902: při vykonávání rutiny ODCIIndexStart() se vyskytla chyba
ORA-29902: chyba pri vykonávaní rutiny ODCIIndexStart()
ORA-29902: błąd podczas wykonywania podprogramu ODCIIndexStart()
ORA-29902: ODCIIndexStart() programını yürütme hatası

Highlighting woes

If you are hitting your head against the wall, as i did, wondering why you can't get a word highlighted in a report, then, you may have come to the bottom line of your problem.
Or so i hope for you.

So, if you already excluded the following issues...

1. case sensitivity problems
2. missing template definitions
3. multiple word highlighting
4. column defined as a link

Then you can probably check out if you put anything in the usually empty "HTML Expression" attribute.

This attribute allows you to greatly customize the content of the "cell", however, if you do so, you completely loose the possibility of using word highlighting.

As it was in my case.

It's not really a quirk of Oracle Application Express, but it's missing information in the online help, because it would have helped greatly if they just put a note saying "does not work when HTML Expression is not empty".

So, if you still want to highlight your stuff, you must do it the hard way:
By replacing or regexp_replacing in case you want it to work for multiple words:

select regexp_replace('oracle application express', '(oracle|express)', '<b>\1</b>') newstr from dual

<b>oracle</b> application <b>express</b>

I hope you can figure out easily how to convert this example into a working parameterized solution.

Happy highlighting



Updated April 10:
you can now use Yocoya's on-line Regular Expression Workbench for Oracle for developing and testing regular expression patterns.

In praise of Monty Python

If you like Monty Python's sense of humour, you'll easily catch some famous quotes i deliberately scattered throughout this posting.

Today i am not here to register a complaint.

I just want to write down a couple of frequently used (and frequently forgotten...) tricks so that i know where to find them the next time i need them.

Number one is the 10g useful feature for delimiting long literal strings:

instead of the annoying double tick mark escaping technique that can easily become a mess when you have large and intricated strings:

sql := 'select ''this is a string'' from dual';

in 10g you can write:

sql := q'!select 'this is a string' from dual!';

No more escaping!
Please note the norwegian blue i used to highlight the relevant text.

Note that you can use any other character of your choosing in place of "!", should it conflict with the string content.
And here is the relevant oracle document snippet for the skeptical visitor.

I don't know about you, but i always manage to forget where the starting and ending exclamation points go and i can't figure it out without resorting to the manual.


And now for something completely different.


Perform a submit when clicking on a checkbox in Oracle Application Express.

Suppose you have an item defined as a checkbox, tipically some sort of flag that allows you to show some extra information when it's true or the standard stuff when it's false, and you want to automatically reload the page when the user clicks on it.

Open its definition and locate the attribute named HTML Form Element Attributes.
Put the following stuff in it:

onChange="javascript:doSubmit('RELOAD');"

be sure to have defined a branch handling either a RELOAD request or an unconditional branch, otherwise you'll get a run time error.

EDITED on 25/5/2007
It turns out that with Internet Explorer 7.0 there is a little problem with onChange.
Read more about it.


Nudge, nudge, wink, wink, say no more!

That's all for today.