Home » Server Options » Text & interMedia » grouping in Oracle Text and CTX_THES (merged)
grouping in Oracle Text and CTX_THES (merged) [message #140878] Thu, 06 October 2005 00:24 Go to next message
vdharanv
Messages: 2
Registered: October 2005
Location: bangalore
Junior Member
how to implement the CTX_THES package in oracle text and give me the example. i want to know how to search the Thesaurus words using oracle text queries example.

[Updated on: Thu, 06 October 2005 04:15]

Report message to a moderator

oracle Text [message #141758 is a reply to message #140878] Tue, 11 October 2005 23:57 Go to previous messageGo to next message
shanraja
Messages: 1
Registered: October 2005
Location: bangalore
Junior Member
Hi All,

How can i use oracle text in grouping search concept.

For Example,

Grouping - find words belonging to the keyword category. eg transportation returns results with car, bus, taxi etc.

Thanks,

Report message to a moderator

Re: oracle Text [message #141783 is a reply to message #141758] Wed, 12 October 2005 02:39 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
For 9i:

Check the manuals:
http://download-west.oracle.com/docs/cd/B10501_01/text.920/a96518/cqoper.htm#18330

You need to create a thesaurus (CTX_THES package) and us the BT (broader term) operator.

What version are you using?

[EDIT]I've merged the topic, since it came from the same location. Question two is also answered through the same link. Wink

MHE

[Updated on: Wed, 12 October 2005 02:43]

Report message to a moderator

Re: oracle Text [message #142277 is a reply to message #141783] Fri, 14 October 2005 01:16 Go to previous messageGo to next message
vdharanv
Messages: 2
Registered: October 2005
Location: bangalore
Junior Member
Hi all,

i already created thesaurus and inserted some values.
i need to know how to query this thesaurus. for example "SELECT ....".

Report message to a moderator

Re: oracle Text [message #142325 is a reply to message #142277] Fri, 14 October 2005 02:44 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
For example: look at the link I provided: use the BT operator.

from the link

If machine is a broader term for crane (building equipment) and bird is a broader term for crane (waterfowl) and no qualifier is specified for a broader term query, the query
BT(crane)

expands to:
'{crane} or {machine} or {bird}'

If waterfowl is specified as a qualifier for crane in a broader term query, the query
BT(crane{(waterfowl)})

expands to the query:
'{crane} or {bird}'



You use this within the CONTAINS operator, as the title of the page suggests.

MHE

Report message to a moderator

Re: oracle Text [message #142497 is a reply to message #142325] Fri, 14 October 2005 18:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
 
ctxsys@ORA92> BEGIN
  2    ctx_thes.create_thesaurus ('DEFAULT');
  3    ctx_thes.create_relation
  4  	 ('DEFAULT', 'transportation', 'NT', 'car');
  5    ctx_thes.create_relation
  6  	 ('DEFAULT', 'transportation', 'NT', 'bus');
  7    ctx_thes.create_relation
  8  	 ('DEFAULT', 'taxi', 'BT', 'transportation');
  9  END;
 10  /

PL/SQL procedure successfully completed.

ctxsys@ORA92> CONNECT scott/tiger
Connected.
ctxsys@ORA92> @ LOGIN
ctxsys@ORA92> SET ECHO OFF

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
scott@ORA92

scott@ORA92> CREATE TABLE your_table
  2    (your_column CLOB)
  3  /

Table created.

scott@ORA92> INSERT ALL
  2  INTO your_table (your_column) VALUES ('bus')
  3  INTO your_table (your_column) VALUES ('car')
  4  INTO your_table (your_column) VALUES ('taxi')
  5  INTO your_table (your_column) VALUES ('transportation')
  6  SELECT * FROM DUAL
  7  /

4 rows created.

scott@ORA92> CREATE INDEX your_table_idx
  2  ON your_table (your_column)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  /

Index created.

scott@ORA92> SELECT *
  2  FROM   your_table
  3  WHERE  CONTAINS (your_column, 'NT (transportation)') > 0
  4  /

YOUR_COLUMN
--------------------------------------------------------------------------------
transportation
taxi
car
bus

scott@ORA92> SELECT *
  2  FROM   your_table
  3  WHERE  CONTAINS (your_column, 'BT (bus)') > 0
  4  /

YOUR_COLUMN
--------------------------------------------------------------------------------
transportation
bus

scott@ORA92> SELECT *
  2  FROM   your_table
  3  WHERE  CONTAINS (your_column, 'BT (car)') > 0
  4  /

YOUR_COLUMN
--------------------------------------------------------------------------------
transportation
car

scott@ORA92> SELECT *
  2  FROM   your_table
  3  WHERE  CONTAINS (your_column, 'BT (taxi)') > 0
  4  /

YOUR_COLUMN
--------------------------------------------------------------------------------
transportation
taxi

scott@ORA92>

Report message to a moderator

Re: oracle Text [message #142498 is a reply to message #142325] Fri, 14 October 2005 19:07 Go to previous message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
Here are some more examples, using a thesaurus other than a default thesaurus, which requires specification of the thesaurus name and number of levels to expand. 

scott@ORA92> BEGIN
  2    ctx_thes.create_thesaurus ('your_thesaurus');
  3    ctx_thes.create_relation
  4  	 ('your_thesaurus', 'transportation', 'NT', 'car');
  5    ctx_thes.create_relation
  6  	 ('your_thesaurus', 'transportation', 'NT', 'bus');
  7    ctx_thes.create_relation
  8  	 ('your_thesaurus', 'taxi', 'BT', 'transportation');
  9    ctx_thes.create_relation
 10  	 ('your_thesaurus', 'car', 'NT', 'Saturn');
 11  END;
 12  /

PL/SQL procedure successfully completed.

scott@ORA92> CREATE TABLE your_table
  2    (your_column CLOB)
  3  /

Table created.

scott@ORA92> INSERT ALL
  2  INTO your_table (your_column) VALUES ('bus')
  3  INTO your_table (your_column) VALUES ('car')
  4  INTO your_table (your_column) VALUES ('taxi')
  5  INTO your_table (your_column) VALUES ('transportation')
  6  INTO your_table (your_column) VALUES ('Saturn')
  7  SELECT * FROM DUAL
  8  /

5 rows created.

scott@ORA92> CREATE INDEX your_table_idx
  2  ON your_table (your_column)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  /

Index created.

scott@ORA92> SELECT *
  2  FROM   your_table
  3  WHERE  CONTAINS (your_column, 'NT (transportation, 1, your_thesaurus)') > 0
  4  /

YOUR_COLUMN
--------------------------------------------------------------------------------
transportation
taxi
car
bus

scott@ORA92> SELECT *
  2  FROM   your_table
  3  WHERE  CONTAINS (your_column, 'NT (transportation, 2, your_thesaurus)') > 0
  4  /

YOUR_COLUMN
--------------------------------------------------------------------------------
Saturn
transportation
taxi
car
bus

scott@ORA92> SELECT *
  2  FROM   your_table
  3  WHERE  CONTAINS (your_column, 'BT (bus, 1, your_thesaurus)') > 0
  4  /

YOUR_COLUMN
--------------------------------------------------------------------------------
transportation
bus

scott@ORA92> SELECT *
  2  FROM   your_table
  3  WHERE  CONTAINS (your_column, 'BT (car, 1, your_thesaurus)') > 0
  4  /

YOUR_COLUMN
--------------------------------------------------------------------------------
transportation
car

scott@ORA92> SELECT *
  2  FROM   your_table
  3  WHERE  CONTAINS (your_column, 'BT (taxi, 1, your_thesaurus)') > 0
  4  /

YOUR_COLUMN
--------------------------------------------------------------------------------
transportation
taxi

scott@ORA92> SELECT *
  2  FROM   your_table
  3  WHERE  CONTAINS (your_column, 'BT (Saturn, 1, your_thesaurus)') > 0
  4  /

YOUR_COLUMN
--------------------------------------------------------------------------------
Saturn
car

scott@ORA92> SELECT *
  2  FROM   your_table
  3  WHERE  CONTAINS (your_column, 'BT (Saturn, 2, your_thesaurus)') > 0
  4  /

YOUR_COLUMN
--------------------------------------------------------------------------------
Saturn
transportation
car

scott@ORA92>

Report message to a moderator

Previous Topic: Indexing Problem
Next Topic: how to insert image into table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 17 20:17:36 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.