Home » Server Options » Text & interMedia » How to use Oracle text (split from hijacked topic "Find Words in a String")
How to use Oracle text (split from hijacked topic "Find Words in a String") [message #618466] Sat, 12 July 2014 02:30 Go to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member


 select *  from emp
    where contains(ename,'MILLER')>0 and rownum < 10;
    
ORA-00904: "CONTAINS": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 2 Column: 11



How to user Oracle text.
Re: Find Words in a String [message #618471 is a reply to message #618466] Sat, 12 July 2014 04:30 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
sss111ind wrote on Sat, 12 July 2014 13:00
How to user Oracle text.


You should create oracle text index before using CONTAINS function.

SQL> CREATE INDEX idxName ON emp(ename) INDEXTYPE IS CTXSYS.CONTEXT
  2  /
Index created

SQL> select *  from emp where contains(ename,'MILLER')>0 and rownum < 2
  2  /

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7934 MILLER     CLERK      7782 1/23/1982     1300.00               10


But, you got an "invalid identifier error", please mention your DB version, hopefully it is something which supports it. AFAIK, it was introduced back in 9i. If your DB version supports and without creating the index you should get following error :

SQL> drop index idxname;
Index dropped

SQL> select *  from emp where contains(ename,'MILLER')>0 and rownum < 2;
select *  from emp where contains(ename,'MILLER')>0 and rownum < 2
*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10599: column is not indexed



Regards,
Lalit

[Updated on: Sat, 12 July 2014 04:42]

Report message to a moderator

Re: Find Words in a String [message #618478 is a reply to message #618466] Sat, 12 July 2014 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Reading the documentation?

Re: Find Words in a String [message #618479 is a reply to message #618478] Sat, 12 July 2014 06:19 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

I go this but not working
http://docs.oracle.com/cd/B19306_01/install.102/e10319/initmedia.htm
Re: Find Words in a String [message #618480 is a reply to message #618479] Sat, 12 July 2014 06:21 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
sss111ind wrote on Sat, 12 July 2014 16:49
I go this but not working


Not working is not an Oracle error.
Re: Find Words in a String [message #618481 is a reply to message #618480] Sat, 12 July 2014 06:27 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member


SQL> @$ORAC1020.ctx.admin.catctx.sql CTXSYS SYSAUX TEMP NOLOCK;
SP2-0310: unable to open file "$ORAC1020.ctx.admin.catctx.sql"
SQL>

Re: Find Words in a String [message #618483 is a reply to message #618481] Sat, 12 July 2014 06:45 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
So you ran catctx.sql in $ORACLE_HOME/ctx/admin directory as SYS?
Re: Find Words in a String [message #618485 is a reply to message #618483] Sat, 12 July 2014 07:15 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Thank you I have installed successfully by executing the catctx.sql script .But facing some problem while using oracle text.
Re: Find Words in a String [message #618487 is a reply to message #618485] Sat, 12 July 2014 07:39 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Ok good.

sss111ind wrote on Sat, 12 July 2014 17:45
But facing some problem while using oracle text.


Again, "Facing some problem" is not an Oracle error. What exactly is the problem?
Re: Find Words in a String [message #618490 is a reply to message #618487] Sat, 12 July 2014 08:05 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member


I am getting this error now.


select *  from DEPT where contains(DNAME,'ACCOUNTING')>0 and rownum < 2;

ORA-20000: Oracle Text error:
DRG-10599: column is not indexed
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.
Re: Find Words in a String [message #618491 is a reply to message #618490] Sat, 12 July 2014 08:12 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
And I already said :

Lalit Kumar B wrote on Sat, 12 July 2014 15:00
sss111ind wrote on Sat, 12 July 2014 13:00
How to user Oracle text.


You should create oracle text index before using CONTAINS function.

SQL> CREATE INDEX idxName ON emp(ename) INDEXTYPE IS CTXSYS.CONTEXT
  2  /
Index created


Re: Find Words in a String [message #618492 is a reply to message #618490] Sat, 12 July 2014 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And did you do that:
Quote:
SQL> CREATE INDEX idxName ON emp(ename) INDEXTYPE IS CTXSYS.CONTEXT
  2  /
Index created

for the field you are using?


Re: Find Words in a String [message #618493 is a reply to message #618490] Sat, 12 July 2014 08:15 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
Which part of column is not indexed you don't understand? Keep in mind, we are talking about Oracle Text indexes.

SY.
Re: Find Words in a String [message #618497 is a reply to message #618493] Sat, 12 July 2014 08:40 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

I am getting this now
     
CREATE INDEX idxNamee ON DEPT(DNAME) INDEXTYPE IS CTXSYS.CONTEXT;

Error starting at line : 73 in command -
CREATE INDEX idxNamee ON DEPT(DNAME) INDEXTYPE IS CTXSYS.CONTEXT
Error at Command Line : 73 Column : 14
Error report -
SQL Error: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364
29855. 00000 -  "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause:    Failed to successfully execute the ODCIIndexCreate routine.
*Action:   Check to see if the routine has been coded correctly.

[Updated on: Sat, 12 July 2014 08:40]

Report message to a moderator

Re: Find Words in a String [message #618498 is a reply to message #618497] Sat, 12 July 2014 08:52 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
Run:

SELECT  PRE_OWNER,
        PRE_NAME
  FROM  CTXSYS.CTX_PREFERENCES
  WHERE PRE_NAME = 'DEFAULT_LEXER';


and post results.

SY.
Re: Find Words in a String [message #618499 is a reply to message #618498] Sat, 12 July 2014 08:56 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

SQL> show user
USER is "SYS"
SQL> SELECT  PRE_OWNER,
        PRE_NAME
  FROM  CTXSYS.CTX_PREFERENCES
  WHERE PRE_NAME = 'DEFAULT_LEXER';  2    3    4

no rows selected

SQL>
Re: Find Words in a String [message #618502 is a reply to message #618499] Sat, 12 July 2014 09:23 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
Then Oracle Text related post-install steps were not run.

SY.
Re: Find Words in a String [message #618503 is a reply to message #618502] Sat, 12 July 2014 09:24 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

could you suggest please what is next script to be run.
Re: Find Words in a String [message #618504 is a reply to message #618503] Sat, 12 July 2014 09:29 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
I just did a quick search with the error codes, and I got many responses in google, most of them are from OTN and OraFAQ. The typical resolutions are to run the post scripts properly.
Re: Find Words in a String [message #618507 is a reply to message #618502] Sat, 12 July 2014 09:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3267
Registered: January 2010
Location: Connecticut, USA
Senior Member
Run the following to find out if Oracle Text is installed:

select  comp_name,
        status,
        version
  from  dba_registry
  where comp_id = 'CONTEXT'
/


SY.
Re: Find Words in a String [message #618518 is a reply to message #618466] Sat, 12 July 2014 12:13 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@Duane,

Please don't get confused by so many replies to your original post, and for that matter I requested Michel to split the other topic of text search, as I believe it to be hijacked from rhe current discussion.
Re: Find Words in a String [message #618519 is a reply to message #618518] Sat, 12 July 2014 12:20 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@Michel,

Sorry, but my most recent post was simultaneously posted after you splitted the hijacked topic. So, some of the previous messages are left here.

I understand the moderator bit stuff is sometimes quite tedious, unfortunately this one being one of them.
Re: Find Words in a String [message #618520 is a reply to message #618518] Sat, 12 July 2014 12:21 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
No problem.
Re: Find Words in a String [message #618521 is a reply to message #618520] Sat, 12 July 2014 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes it is not easy when messages are interleaved, above all when the topic is still going on, I did my best.
What I can advise is to continue to post Duane's original question related answers in the other topic (the original one, and maybe repost some if you think they are important).

Re: Find Words in a String [message #618526 is a reply to message #618521] Sat, 12 July 2014 13:17 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Sure Michel, and yes both the topics are currently going on, unless OP comes with the feedback.

Thank you for your effort.
Re: Find Words in a String [message #618542 is a reply to message #618497] Sat, 12 July 2014 15:32 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
sss111ind wrote on Sat, 12 July 2014 06:40
I am getting this now
     
CREATE INDEX idxNamee ON DEPT(DNAME) INDEXTYPE IS CTXSYS.CONTEXT;

Error starting at line : 73 in command -
CREATE INDEX idxNamee ON DEPT(DNAME) INDEXTYPE IS CTXSYS.CONTEXT
Error at Command Line : 73 Column : 14
Error report -
SQL Error: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364
29855. 00000 -  "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause:    Failed to successfully execute the ODCIIndexCreate routine.
*Action:   Check to see if the routine has been coded correctly.


Apparently, you didn't run step 2 from the instructions in the installation link that you provided, to create the ctxsys.default_lexer:

Quote:

If you are working with US english texts, install appropriate language-specific default preferences:

connect CTXSYS/CTXSYS
@$ORAC1020.ctx.admin.drdefus;

If you are not working with US english texts, open the drdef*.sql script according to the preferred language, set the attribute (refer to Restrictions in the following section), and run the script.

Re: Find Words in a String [message #618585 is a reply to message #618542] Mon, 14 July 2014 02:14 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Thanks to all,It started working now.
Re: Find Words in a String [message #618661 is a reply to message #618585] Mon, 14 July 2014 13:14 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
@sss111ind,

Since this topic was split and it would not be easy for anybody to understand the resolution of your problem, would you mind to post what exactly you did to solve your issue. It would certainly help other folks searching for similar topic.

And of course, not to mention but just a reminder, you can mention the folks who helped you to resolve your issue alongwith the suggestion. It would be very good as forum ethics Smile

I suggest you post it step by step. Just like you would expect a solution for your issue.

Thanks.

Edit : Added a one liner at last to justify why I requested OP to come back with his feedback.

[Updated on: Mon, 14 July 2014 13:19]

Report message to a moderator

Previous Topic: Oracle Text index catsearch order by close match
Next Topic: Is there a way to customize the BASE_LETTER conversions that oracle text does
Goto Forum:
  


Current Time: Tue Mar 19 01:34:38 CDT 2024