Oracle Text (MOSC)

MOSC Banner

ORA-20000 is no other messages following it

edited Sep 3, 2009 11:52PM in Oracle Text (MOSC) 2 commentsAnswered
 I am running 10.2.0.4 including CPUApr2009 on Solaris 5.10.  A user received an ORA-20000 error message while doing a query that involved a text index and outer joins:

select m.id,m.create_date,t.textcol from texttable t, maintable m where m.id = t.id(+) and contains(t.textcol,'%knn or rgb%')=0 and m.create_date>='2009-08-22';

The user wanted to see all rows created on or after 22Aug09, and if they had a row in the texttable, then also see the contents of that row PROVIDED the textcol did NOT contain the strings specified.  The query returned ORA-20000 with no other explanation.  An explain plan showed that the database was not using the text index on textcol but was doing a full scan on texttable, and then filtering.  If I changed it to an equijoin, then the text index was used.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center