Hi,
we're trying to use oracle text to search on multiple columns from different tables.
To test this functionality, I followed the example at orafaq.com/forum/mv/msg/201983/655836/#msg_num_2 (i'm not allowed to post links yet), which worked great, but doesn't seem to fully apply to our data structure.
-- Simplified tables and data. We're looking to expand the search to more tables in the future, ideally being able to search through all our data.
Table streets:
STREET_ID STREET_NAME
------------ --------------
1 Sesame Street 1
2 Sunset Boulevard 5
3 Hollywood Plaza 3
Table address:
CUSTOMER_ID ADDRESS_ID NAME IS_PERSON Street_ID
-------------- ------------ --------------- ------------ ------------
1000 111111 John Smith Ltd. 0 1
2000 222222 James 1 2
1000 333333 Mary 1 3
Table phone_numbers
CUSTOMER_ID ADDRESS_ID_OWNER RELATION ADDRESS_ID_USER PHONENUMBER
-------------- ------------------- ----------- ------------------ --------------
1000 111111 0 111111 123456 -- Phone number of John Smith Ltd. (e. g. reception of the business)
1000 111111 1 333333 123457 -- Phone number of Mary as contact for John Smith Ltd. (Relation 1 to ADDRESS_ID 111111)
1000 333333 0 333333 555555 -- Phone number of Mary (private)
The stored procedure should produce something to the following extend:
<ADDRESS><ADDRESS_ID>111111</ADDRESS_ID><NAME>John Smith Ltd.</NAME><STREET>Sesame Street 1</STREET></ADDRESS><PHONE_NUMBERS><PHONENUMBER>123456</PHONENUMBER><PHONENUMBER>123457</PHONENUMBER></PHONE_NUMBERS>
<ADDRESS><ADDRESS_ID>222222</ADDRESS_ID><NAME>James</NAME><STREET>Sunset Boulevard 5</STREET></ADDRESS><PHONE_NUMBERS><PHONENUMBER></PHONENUMBER></PHONE_NUMBERS>
<ADDRESS><ADDRESS_ID>333333</ADDRESS_ID><NAME>Mary</NAME><STREET>Hollywood Plaza 3</STREET></ADDRESS><PHONE_NUMBERS><PHONENNUMBER>555555</PHONENUMBER></PHONE_NUMBERS>
My problem is that I don't know where and how to create the index. The index should be updated when any of the values change. For example the sync should occur when I change the street name in the streets table or the name in the address table or the phone number in the phone_numbers table, etc.
How is this possible with oracle text?
Thank you very much in advance!
] 
Blog
Search
Help
Members
Register
Login
Home
