Oracle CONTAINS Giving Invalid Identifier ORA-00904

Kurt Wagner picture Kurt Wagner · Sep 12, 2013 · Viewed 9.2k times · Source

So I have a webapp that takes in a string where ID elements are broken up by '-' (e.g. someid1-someid2). I then convert the string to something like someid1 OR someid2. This in theory according to Oracle's doc's should allow me to then do something like

SELECT somecol1, somecol2 FROM sometable WHERE CONTAINS (somecol1, 'someid1 OR someid2') > 0;

However I'm getting the following error when running the sample query in SQL Dev:

ORA-00904: "CONTAINS": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:

The version of Oracle is Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production of which the documentation is for, so I don't see why CONTAINS would be considered an invalid identifier. Adding the other optional parameter

SELECT somecol1, somecol2 FROM sometable WHERE CONTAINS (somecol1, 'someid1 OR someid2', 1) > 0;

also gives the same error. What am I/it doing wrong here?

EDIT:
'Working Code' as per request although arguably it isn't working

SELECT tech, product FROM tech_det_view WHERE CONTAINS (tech, 'test OR qual') > 0;

Answer

Robert picture Robert · Sep 12, 2013

You should create oracle text index before using CONTAINS function

CREATE INDEX idxName ON tableName(columnName) INDEXTYPE IS CTXSYS.CONTEXT;

Here you can find more information about Oracle Text.