Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

JOINs and CATSEARCH

833964Jan 25 2011 — edited Jan 27 2011
I have 3 tables

CONTRACTOR
- ID
- NAME
- ADDRESS

CONTRACTOR_DOMESTIC_CODE
- ID
- CODE

CONTRACTOR_INTERNATIONAL_CODE
- ID
- CODE

The codes consist of multiple word and there is a UI where a user can enter any one word and search. I need to create a CTXCAT index over the codes to return the results. The user has an option of selecting Domestic, International, Both while performing his search.

For domestic search I tried the following query:

SELECT con.id
FROM contractor con
LEFT OUTER JOIN contractor_domestic_code dom
ON con.id = dom.id
WHERE con.status_delete != 'Y'
AND CATSEARCH(dom.code, 'de*', '') > 0

It throws the following exception

ORA-20000: Oracle Text error:
DRG-10849: catsearch does not support functional invocation
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.

I haven't been able to get around this problem. IN clause works but is very slow. I will also need to add contractor_international_code in the query when required.
This post has been answered by Barbara Boehmer on Jan 25 2011
Jump to Answer

Comments

Jeff Martin

Your query might win the award for the "shortest question with the longest answer", but I'll try for a short answer. For the first part, you need to fetch data from the database. For this you could use anything from raw JDBC to an Object Relational Mapping (ORM) tool, like Hibernate. For the second part, I would recommend a reporting tool, like Jasper Reports, BIRT or ReportMill. Currently, only ReportMill has generated reports in JavaFX. But they all generate PDF - and that would be my suggestion: generate PDF and use the platform PDF viewer to display and print reports. You could spend years trying to duplicate all the nice display and print functionality found in Acrobat Reader and Mac OS X Preview.

As it happens, ReportMill (disclosure: me) is working on a tool to do exactly what your question asks for:

http://www.reportmill.com/javi/gallery/AddressBook

There is also a very cool database tool that has JavaFX support called XDEV. We'll both be at JavaOne.

1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 24 2011
Added on Jan 25 2011
#database-key-features, #text
7 comments
4,020 views