• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1665
  • Last Modified:

Cant create an Index set using toad for a Cat Search

Hi posters,

I want to create a script to create an Index set and then create an Index to use the CatSearch. However I cant get it to run using my plsql. I can create the index using the script but cant create the index set as it cant find the procedure. However I,m using toad and when I type in CTXSYS and a dot it gives me up a list of the procedures. I select the Create_Index_Set but when I run it it can find it. Do I need roles or rights to see this....or what am I doing wrong.  thanks Mick
Heres my script if you could look at it....
 
BEGIN 
EXECUTE IMMEDIATE 'CTXSYS.CREATE_INDEX_SET ''IDXSET_RECKEYWDDATEDESC'')';
COMMIT; 
 
Ive also tried CTXSYS.CTX_DLL.Create_Index_set and CTX_DLL.Create_Index_set inside the execute but cant get it to run.
 
I can create the index alright but not the index -set.  I,m using Toad again. I,ve also tried using quotes around the statement and without them. Any ideas
 
Heres how I create the index
Begin
Execute immediate ('CREATE INDEX IDX_REC_KEYWD_DATE_DESC ON MyTable (Keywords) INDEXTYPE IS CTXSYS.CTXCAT');
End;

Open in new window

0
MickyMc
Asked:
MickyMc
  • 7
  • 4
  • 2
1 Solution
 
ajexpertCommented:
0
 
schwertnerCommented:
Begin
Execute immediate ('CREATE INDEX IDX_REC_KEYWD_DATE_DESC ON MyTable (Keywords)');
End;
0
 
MickyMcAuthor Commented:
thanks lads buts its creating an index set thats the issue. I can create the index alright but before I do that I need to create an index set.

Something like
BEGIN
EXECUTE IMMEDIATE 'CTXSYS.CREATE_INDEX_SET ''IDXSET_RECKEYWDDATEDESC'')';
COMMIT;
thks -Mick
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
schwertnerCommented:
Creates an index set for CTXCAT index types. index set name is unique within an owner. Only CTXAPP users and CTXSYS can create an index set.     ctx_ddl.create_index_set(set_name IN VARCHAR2);

BEGIN
EXECUTE IMMEDIATE 'ctx_ddl.CREATE_INDEX_SET (IDXSET_RECKEYWDDATEDESC)';
COMMIT;

You have to make sure the component is installed.
Read the docs.
0
 
MickyMcAuthor Commented:
thanks schwertner... I managed to get it working and heres what I have

drop index RECORD.IDX_RECKEYWDDATEDESC;

begin

ctxsys.ctx_ddl.drop_index_set('IDXSET_RECKEYWDDATEDESC');

ctxsys.ctx_ddl.create_index_set('IDXSET_RECKEYWDDATEDESC');

ctxsys.ctx_ddl.add_index ('IDXSET_RECKEYWDDATEDESC','RECORDDATE');

ctxsys.ctx_ddl.add_index ('IDXSET_RECKEYWDDATEDESC','REFERENCE');

execute immediate 'CREATE INDEX IDX_RECKEYWDDATEDESC ON RECORD (Keywords) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS (''INDEX SET IDXSET_RECKEYWDDATEDESC'')';


end;
 
OK... so near so far. This works if I take out the Index Set for the Reference field as the field is 100 chars long. It seems that Oracle doesnt like having it more than 30.... aahhh whats the point. Is there workaround to this. I dont want to use a context search as the indexes arnt dynamic and given the size of my table...will be huge.

All I want to do is check a column for a few keywords and also check the reference and the record date. Oh initally I was doing something like... Select myfields from my table where catsearch('Keywords,'Mick Joe, Null) > 0   AND RecordDate >= '01/01/08' and ShortDesc like ('hello%')

...excuse the typos in the syntax but it seems that Oracle doesnt like it this way and the best way is to use index searchs but then they dont handle fields with more than 30 bytes...aahhhh please help.... thanks Mick
0
 
ajexpertCommented:
Oracle has a limit of 30 characters on identifiers.  There is no workaround in this.
So, Creating new object like, table, index, procedure, function, should be less or equal to 30 characters.

Hope this helps
0
 
MickyMcAuthor Commented:
sorry Aj but its the field length and not the identifer. I thought the same but the identifier is only 24 chars long.  Heres the error message that I get.

begin
   ctxsys.ctx_ddl.drop_index_set('IDXSET_RECKEYWDDATEDESC');  
   ctxsys.ctx_ddl.create_index_set('IDXSET_RECKEYWDDATEDESC');
 
   ctxsys.ctx_ddl.add_index ('IDXSET_RECKEYWDDATEDESC','RECORDDATE');
   ctxsys.ctx_ddl.add_index ('IDXSET_RECKEYWDDATEDESC','REFERENCE');
 
    execute immediate 'CREATE INDEX IDX_RECKEYWDDATEDESC ON RECORD (Keywords) INDEXTYPE IS CTXSYS.CTXCAT PARAMETERS (''INDEX SET IDXSET_RECKEYWDDATEDESC'')';  
   
end;
Error at line 1
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-12306: column REFERENCE is too long for index set columns
ORA-06512: at "CTXSYS.DRUE", line 157
ORA-06512: at "CTXSYS.CATINDEXMETHODS", line 100
ORA-06512: at line 8
0
 
schwertnerCommented:
Oracle Error :: DRG-12306
column string is too long for index set columns

Cause
You specified a column in the column list of an index of an index set which is too long

Action
CHAR and VARCHAR2 columns must be 30 bytes or less max length

0
 
MickyMcAuthor Commented:
thanks schwertner...and sorry for going on but this is driving me nuts. All I want to do is search 3 fields.

Find all records
 where the Record date is between 2 dates (from and to)

0
 
MickyMcAuthor Commented:
thanks schwertner...and sorry for going on but this is driving me nuts. All I want to do is search 3 fields.

Find all records
 where the Record date is between 2 dates (from and to)
 where the reference is like Reference field
where keywords in Keywords field

I have nearly went full circle. I dont want to use Context text search as this will get huge. I tried the Catsearch but can do 'Ands in the where clause as it doesnt support functional innovaction and I couldnt get the hints to work. So I done it using the Index sets and now I cant use columns more than 30 bytes.

Please tell me schwertner how do people do this? I have a web app and simply want to search on these 3 fields and it seems a nightmare in the worlds biggest database! I read I can use 2 Catsearchs on Keywords and reference and do and intersection but this might be costly...

your help is great appreciated...thanks mick
0
 
schwertnerCommented:
0
 
MickyMcAuthor Commented:
Sorry schwertner but thats for 10g but it is very similar to one that I read for 9i. It doesnt tell you about the size of index set columns being only 30 bytes or how to get around the function invocation error.

I'll keep this opened for another while.

regards Mick

ps... with the amount of web sites built on 9i, I'd love to know how others done it!
0
 
MickyMcAuthor Commented:
thks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now