Home » Server Options » Text & interMedia » Searching using CATSEARCH and OR (10.2.0.4)
Searching using CATSEARCH and OR [message #478978] Wed, 13 October 2010 18:26 Go to next message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
I am getting function invocation error with CATSEARCH




create table mkt_track
shrt_nm varchar2(20),
accnt_id varchar2(20),
brnch_tx varchar2(3));
/

insert into mkt_track('ROYAL BANK','50057012','005');
/

insert into mkt_track('SPENCER','50057019','500');
/

insert into mkt_track('BATS','50057020','500');
/

commit;

CREATE INDEX INDX_MKT_TRACK_1
     ON MKT_TRCK(SHRT_NM)
        INDEXTYPE IS CTXSYS.CTXCAT PARALLEL;

/
CREATE INDEX INDX_MKT_TRACK_2
     ON MKT_TRCK(BRNCH_TX)
        INDEXTYPE IS CTXSYS.CTXCAT PARALLEL;
/


select * from bs_orgnl_sbmsn
 WHERE (catsearch(SHRT_NM,'50057012',' ' )>0) OR (CATSEARCH(BRNCH_TX,'005',' ')>0);

/
ORA-20000: Oracle Text error:
DRG-10849: catsearch does not support functional invocation
DRG-10599: column is not indexed




Is there any way I can combine two CATSEARCH in a single query?
Re: Searching using CATSEARCH and OR [message #478980 is a reply to message #478978] Wed, 13 October 2010 20:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
You have put a semicolon ; and a slash / after every command which causes every command to run twice. You posted a create table statement for mkt_track, created your indexes on mkt_trck, and queried bs_orgnl_sbmsn. Your table creation is missing a left parentheses. Your insert statements are missing the values keyword.

There should not be a space between the single quotes around the third parameter of the catsearch operator. It should be a null value, so either use two single quotes without a space or the word null.

There is also a problem with ctxcat indexes and catsearch where the optimizer may choose functional invocation at any time, which will always produce an error without returning any rows. This is why I always recommend context indexes and contains.

If you are querying two columns, then it is better to use a context index with a multi_column_datastore and use one contains clause. If you want to search within specific columns, then you can add a section group with field sections and use within clauses within one contains clause. It is generally a bad practice to use multiple catsearch or contains clauses in one query.

If your second column is purely structured data like numbers or dates, then you can create a ctxcat index with a sub-index, but this still does not solve the functional invocation problem.

I have corrected your code below, then offered a better method below that.

-- corrected code:
SCOTT@orcl_11gR2> create table mkt_track (
  2  shrt_nm varchar2(20),
  3  accnt_id varchar2(20),
  4  brnch_tx varchar2(3))
  5  /

Table created.

SCOTT@orcl_11gR2> insert into mkt_track VALUES ('ROYAL BANK','50057012','005')
  2  /

1 row created.

SCOTT@orcl_11gR2> insert into mkt_track VALUES ('SPENCER','50057019','500')
  2  /

1 row created.

SCOTT@orcl_11gR2> insert into mkt_track VALUES ('BATS','50057020','500')
  2  /

1 row created.

SCOTT@orcl_11gR2> commit
  2  /

Commit complete.

SCOTT@orcl_11gR2> CREATE INDEX INDX_MKT_TRACK_1
  2  	  ON MKT_TRACK (SHRT_NM)
  3  	     INDEXTYPE IS CTXSYS.CTXCAT PARALLEL
  4  /

Index created.

SCOTT@orcl_11gR2> CREATE INDEX INDX_MKT_TRACK_2
  2  	  ON MKT_TRACK(BRNCH_TX)
  3  	     INDEXTYPE IS CTXSYS.CTXCAT PARALLEL
  4  /

Index created.

SCOTT@orcl_11gR2> select * from mkt_track
  2  WHERE  (catsearch (SHRT_NM,'50057012', NULL ) > 0)
  3  OR     (CATSEARCH (BRNCH_TX,'005', NULL) > 0)
  4  /

SHRT_NM              ACCNT_ID             BRN
-------------------- -------------------- ---
ROYAL BANK           50057012             005

1 row selected.

SCOTT@orcl_11gR2>


-- better method:
SCOTT@orcl_11gR2> create table mkt_track (
  2  shrt_nm varchar2(20),
  3  accnt_id varchar2(20),
  4  brnch_tx varchar2(3),
  5  search_cols varchar2(1))
  6  /

Table created.

SCOTT@orcl_11gR2> begin
  2    insert into mkt_track VALUES ('ROYAL BANK','50057012','005', null);
  3    insert into mkt_track VALUES ('SPENCER','50057019','500', null);
  4    insert into mkt_track VALUES ('BATS','50057020','500', null);
  5  end;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('your_datastore', 'MULTI_COLUMN_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('your_datastore', 'COLUMNS', 'shrt_nm, brnch_tx');
  4    CTX_DDL.CREATE_SECTION_GROUP ('your_sec', 'BASIC_SECTION_GROUP');
  5    CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'shrt_nm', 'shrt_nm', TRUE);
  6    CTX_DDL.ADD_FIELD_SECTION ('your_sec', 'brnch_tx', 'brnch_tx', TRUE);
  7  END;
  8  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> CREATE INDEX context_idx ON MKT_TRACK (search_cols)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    ('datastore	your_datastore
  5  	 section group	your_sec')
  6  /

Index created.

SCOTT@orcl_11gR2> select * from mkt_track
  2  WHERE  CONTAINS
  3  	      (search_cols,
  4  	       '50057012 OR 005') > 0
  5  /

SHRT_NM              ACCNT_ID             BRN S
-------------------- -------------------- --- -
ROYAL BANK           50057012             005

1 row selected.

SCOTT@orcl_11gR2> select * from mkt_track
  2  WHERE  CONTAINS
  3  	      (search_cols,
  4  	       '50057012 WITHIN shrt_nm OR 005 WITHIN brnch_tx') > 0
  5  /

SHRT_NM              ACCNT_ID             BRN S
-------------------- -------------------- --- -
ROYAL BANK           50057012             005

1 row selected.

SCOTT@orcl_11gR2>

Re: Searching using CATSEARCH and OR [message #478981 is a reply to message #478978] Wed, 13 October 2010 20:21 Go to previous message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
Thanks Barbara. We were thinking of creating CTXSYS.CONTEXT indexes, but then found out that it is consuming almost double the space compared to CTXSYS.CTXCAT indexes.

Previous Topic: Special characters issues with CATSEARCH query
Next Topic: Not wanted HTML tags in snippet searching multiple columns of a table
Goto Forum:
  


Current Time: Fri Mar 29 01:10:55 CDT 2024