We help IT Professionals succeed at work.
Get Started
Troubleshooting Question

Finding special characters for Printjoin in Oracle  (DRG-51030)

Julie Kurpa
Julie Kurpa asked
Last Modified: 2021-03-11
I have recently learned about Oracle Text and have implemented it to help a developer with his wildcard queries.  
It's working very well and the queries are super fast.

However, it seems there are failures for some of the queries.  The developer can see the errors in the apache logs.  One user was helpful enough to call and tell us exactly what they were searching for and now we can produce the error ourselves.  

Here is the full error:

select parid, owner, addrfull from schema.search_rp where contains(TEXT_MULTI_COL_IDX, '%2 MYSTREET%') > 0 offset 0 rows fetch
 next 25 rows only
ERROR at line 1:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many terms

I see that one solution is to increase the "WILDCARD_MAXTERMS" from the default which is 20000.  
Per an Oracle document, I increased it to 50000 which was bad and caused all our server resources to max out.  So I put it back to 20000.

So I'm thinking that perhaps I need to see if I should modify the Printjoin to identify more special characters.  Perhaps the data has some characters that is causing it to split the data too much.  

Here is my printjoin which should not split on  @\&.-,()#"_'

   ctx_ddl.create_preference('WEBDATA_LEX', 'BASIC_LEXER');

I'm not confident it's working properly and not confident that I've identified all the non-alphabetic and non-numeric values.

Perhaps this is not even my issue and it's simply the spaces in the data (which I need).

Can someone assist me in 2 ways:
1. Query all columns of the table for anything that is not a space, Alpha or Numeric character?
2. Make sure my printjoin is correct to account for all those special characters?

I realize it's possible there may be "unprintable" characters in the data that may need to be fixed.  Maybe a user pasted something into a field.  If I could identify those also, it would be great!

Watch Question
Most Valuable Expert 2012
Distinguished Expert 2020
This problem has been solved!
Unlock 1 Answer and 35 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE