Home » Server Options » Text & interMedia » ORA-00904: "CONTAINS": invalid identifier (Oracle 10g)
ORA-00904: "CONTAINS": invalid identifier [message #392543] Wed, 18 March 2009 06:17 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I got the below error.
select * from dual where contains(DUMMY,'X')>0
                         *
ERROR at line 1:
ORA-00904: "CONTAINS": invalid identifier



I need to have an alternative for the below sql
Select * from zrepository where fname like  '%.rar' and
updt_tms between to_date('02/21/2009', 'MM/DD/YYYY') and to_date('02/27/2009', 'MM/DD/YYYY')


Thanks,
Oli
Re: ORA-00904: "CONTAINS": invalid identifier [message #392545 is a reply to message #392543] Wed, 18 March 2009 06:29 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

I use Oracle 10g (10.2.0.1). When i try to execute the following query,

SELECT *
  FROM DUAL
 WHERE contains (dummy, 'X') > 0


I got the following error from TOAD,

ORA-20000: Oracle Text error:
DRG-10599: column is not indexed


I used "Contains" keyword for a indexed column,still the same error.

SELECT *
  FROM scott.emp
 WHERE contains (empno, 7) > 0


Regards,
Ashoka BL
Re: ORA-00904: "CONTAINS": invalid identifier [message #392546 is a reply to message #392545] Wed, 18 March 2009 06:32 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
i think this article will help you.....http://dbaforums.org/oracle/index.php?showtopic=6519&pid=23419&mode=threaded&start=#entry23419
Re: ORA-00904: "CONTAINS": invalid identifier [message #392547 is a reply to message #392543] Wed, 18 March 2009 06:38 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@Olivia,

Did some googling and found the following,

CREATE TABLE accumtbl
   (ID NUMBER, text VARCHAR2(4000) );

INSERT INTO accumtbl
     VALUES (1,
             'the little dog played with the big dog 
      while the other dog ate the dog food');
INSERT INTO accumtbl
     VALUES (2, 'the cat played with the dog');

CREATE INDEX
   accumtbl_idx ON accumtbl (text)
   INDEXTYPE IS ctxsys.CONTEXT;


SELECT score (10)
  FROM accumtbl
 WHERE contains (text, 'dog ACCUM cat', 10) > 0;


May be the following links help u,

http://www.dba-oracle.com/t_sql_contains_clause.htm
http://download.oracle.com/docs/cd/A57673_01/DOC/server/doc/CO20APP/ch08.htm#459

Re: ORA-00904: "CONTAINS": invalid identifier [message #392553 is a reply to message #392547] Wed, 18 March 2009 06:56 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Yes, I got the same. Do we need to create INDEX
as ctxsys.CONTEXT for this?
Re: ORA-00904: "CONTAINS": invalid identifier [message #392560 is a reply to message #392543] Wed, 18 March 2009 07:19 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@Olivia,

To be frank, i don't know much about "Contains". Was curious when you posted first, so tried.

I got the error before creating index as ctxsys.context, but after creating i didn't get it.

Regards,
Ashoka BL
Re: ORA-00904: "CONTAINS": invalid identifier [message #392561 is a reply to message #392560] Wed, 18 March 2009 07:37 Go to previous message
trivendra
Messages: 211
Registered: October 2007
Location: Phoenix
Senior Member
This is again a Brilliant example by Barbara Boehmer.

http://www.orafaq.com/forum/?t=msg&goto=386133&0/#msg_386133


Thanks
Trivendra
Previous Topic: MULTI_STOPLIST using WORLD_LEXER
Next Topic: Fuzzy logic data unit tests
Goto Forum:
  


Current Time: Thu Mar 28 20:29:39 CDT 2024