Home » Server Options » Text & interMedia » What am I missing in CTX Setup?
What am I missing in CTX Setup? [message #161876] Tue, 07 March 2006 11:30 Go to next message
robajohnson
Messages: 9
Registered: February 2006
Location: Dallas Texas area
Junior Member

When I started on this project I inherited a dev database.

On that database all of the following DDL worked just fine:

CREATE INDEX WL_BIO_DESC_CTX
ON WL_BIO(TEXT)
INDEXTYPE IS ctxsys.context parameters ('sync (on commit)')
;

CREATE INDEX WL_CRITIC_REVIEW_CTX
ON WL_CRITIC_REVIEW(REVIEW_TEXT)
INDEXTYPE IS ctxsys.context parameters ('sync (on commit)')
;

CREATE INDEX WL_USER_REVIEW_CTX
ON WL_USER_REVIEW(REVIEW_TEXT)
INDEXTYPE IS ctxsys.context parameters ('sync (on commit)')
;

CREATE INDEX WL_NEWS_CTX
ON WL_NEWS(FULL_TEXT)
INDEXTYPE IS ctxsys.context parameters ('sync (on commit)')
;

So I created a new staging database and ran the CTX Create script- ?/ctx/admin/catctx.sql and granted ctxapp role to the users who would create and use domain indexes.

However, the Create index SQL above does NOT work in the new database! I get a preferences not set error.

I used data pump to generate the SQL from the schema where the SQL above worked and it generated the following long proc (down below) that does work. The generated SQL after the proc is nearly identical to the original SQL we ran the first time. The Data pump DDL also runs that same proc once for each index with only the parms in the call ctxsys.driimp.create_index changed.

Since I did not have to run that long proc in the orginal database to get the indexes to build I am guessing there must be someway to set those preferences globally? Right?

How do I do that?

----------------------------------------------------
--
-- Data Pump generated DDL
--
BEGIN
ctxsys.driimp.create_index('WL_NEWS_CTX','WL_NEWS',USER,
'NEWS_ID','1','FULL_TEXT','8',
'0','0','8130','1',
NULL, NULL, NULL, 0,NULL, 'ON COMMIT','12582912',NULL, NULL );


ctxsys.driimp.set_object('DATASTORE','DIRECT_DATASTORE',0);

ctxsys.driimp.set_object('DATATYPE','LONG_DATATYPE',0);

ctxsys.driimp.set_object('DATAX','SYNCH_DATAX',0);

ctxsys.driimp.set_object('FILTER','NULL_FILTER',0);

ctxsys.driimp.set_object('SECTION_GROUP','NULL_SECTION_GROUP',0);

ctxsys.driimp.set_object('LEXER','BASIC_LEXER',0);

ctxsys.driimp.set_object('WORDLIST','BASIC_WORDLIST',2);
ctxsys.driimp.set_value('STEMMER','1');
ctxsys.driimp.set_value('FUZZY_MATCH','1');

ctxsys.driimp.set_object('STOPLIST','BASIC_STOPLIST',114);
ctxsys.driimp.set_value('STOP_WORD','Mr');
ctxsys.driimp.set_value('STOP_WORD','Mrs');
ctxsys.driimp.set_value('STOP_WORD','Ms');
ctxsys.driimp.set_value('STOP_WORD','a');
ctxsys.driimp.set_value('STOP_WORD','all');
ctxsys.driimp.set_value('STOP_WORD','almost');
ctxsys.driimp.set_value('STOP_WORD','also');
ctxsys.driimp.set_value('STOP_WORD','although');
ctxsys.driimp.set_value('STOP_WORD','an');
ctxsys.driimp.set_value('STOP_WORD','and');
ctxsys.driimp.set_value('STOP_WORD','any');
ctxsys.driimp.set_value('STOP_WORD','are');
ctxsys.driimp.set_value('STOP_WORD','as');
ctxsys.driimp.set_value('STOP_WORD','at');
ctxsys.driimp.set_value('STOP_WORD','be');
ctxsys.driimp.set_value('STOP_WORD','because');
ctxsys.driimp.set_value('STOP_WORD','been');
ctxsys.driimp.set_value('STOP_WORD','both');
ctxsys.driimp.set_value('STOP_WORD','but');
ctxsys.driimp.set_value('STOP_WORD','by');
ctxsys.driimp.set_value('STOP_WORD','can');
ctxsys.driimp.set_value('STOP_WORD','could');
ctxsys.driimp.set_value('STOP_WORD','d');
ctxsys.driimp.set_value('STOP_WORD','did');
ctxsys.driimp.set_value('STOP_WORD','do');
ctxsys.driimp.set_value('STOP_WORD','does');
ctxsys.driimp.set_value('STOP_WORD','either');
ctxsys.driimp.set_value('STOP_WORD','for');
ctxsys.driimp.set_value('STOP_WORD','from');
ctxsys.driimp.set_value('STOP_WORD','had');
ctxsys.driimp.set_value('STOP_WORD','has');
ctxsys.driimp.set_value('STOP_WORD','have');
ctxsys.driimp.set_value('STOP_WORD','having');
ctxsys.driimp.set_value('STOP_WORD','he');
ctxsys.driimp.set_value('STOP_WORD','her');
ctxsys.driimp.set_value('STOP_WORD','here');
ctxsys.driimp.set_value('STOP_WORD','hers');
ctxsys.driimp.set_value('STOP_WORD','him');
ctxsys.driimp.set_value('STOP_WORD','his');
ctxsys.driimp.set_value('STOP_WORD','how');
ctxsys.driimp.set_value('STOP_WORD','however');
ctxsys.driimp.set_value('STOP_WORD','i');
ctxsys.driimp.set_value('STOP_WORD','if');
ctxsys.driimp.set_value('STOP_WORD','in');
ctxsys.driimp.set_value('STOP_WORD','into');
ctxsys.driimp.set_value('STOP_WORD','is');
ctxsys.driimp.set_value('STOP_WORD','it');
ctxsys.driimp.set_value('STOP_WORD','its');
ctxsys.driimp.set_value('STOP_WORD','just');
ctxsys.driimp.set_value('STOP_WORD','ll');
ctxsys.driimp.set_value('STOP_WORD','me');
ctxsys.driimp.set_value('STOP_WORD','might');
ctxsys.driimp.set_value('STOP_WORD','my');
ctxsys.driimp.set_value('STOP_WORD','no');
ctxsys.driimp.set_value('STOP_WORD','non');
ctxsys.driimp.set_value('STOP_WORD','nor');
ctxsys.driimp.set_value('STOP_WORD','not');
ctxsys.driimp.set_value('STOP_WORD','of');
ctxsys.driimp.set_value('STOP_WORD','on');
ctxsys.driimp.set_value('STOP_WORD','one');
ctxsys.driimp.set_value('STOP_WORD','only');
ctxsys.driimp.set_value('STOP_WORD','onto');
ctxsys.driimp.set_value('STOP_WORD','or');
ctxsys.driimp.set_value('STOP_WORD','our');

ctxsys.driimp.set_value('STOP_WORD','ours');
ctxsys.driimp.set_value('STOP_WORD','s');
ctxsys.driimp.set_value('STOP_WORD','shall');
ctxsys.driimp.set_value('STOP_WORD','she');
ctxsys.driimp.set_value('STOP_WORD','should');
ctxsys.driimp.set_value('STOP_WORD','since');
ctxsys.driimp.set_value('STOP_WORD','so');
ctxsys.driimp.set_value('STOP_WORD','some');
ctxsys.driimp.set_value('STOP_WORD','still');
ctxsys.driimp.set_value('STOP_WORD','such');
ctxsys.driimp.set_value('STOP_WORD','t');
ctxsys.driimp.set_value('STOP_WORD','than');
ctxsys.driimp.set_value('STOP_WORD','that');
ctxsys.driimp.set_value('STOP_WORD','the');
ctxsys.driimp.set_value('STOP_WORD','their');
ctxsys.driimp.set_value('STOP_WORD','them');
ctxsys.driimp.set_value('STOP_WORD','then');
ctxsys.driimp.set_value('STOP_WORD','there');
ctxsys.driimp.set_value('STOP_WORD','therefore');
ctxsys.driimp.set_value('STOP_WORD','these');
ctxsys.driimp.set_value('STOP_WORD','they');
ctxsys.driimp.set_value('STOP_WORD','this');
ctxsys.driimp.set_value('STOP_WORD','those');
ctxsys.driimp.set_value('STOP_WORD','though');
ctxsys.driimp.set_value('STOP_WORD','through');
ctxsys.driimp.set_value('STOP_WORD','thus');
ctxsys.driimp.set_value('STOP_WORD','to');
ctxsys.driimp.set_value('STOP_WORD','too');
ctxsys.driimp.set_value('STOP_WORD','until');
ctxsys.driimp.set_value('STOP_WORD','ve');
ctxsys.driimp.set_value('STOP_WORD','very');
ctxsys.driimp.set_value('STOP_WORD','was');
ctxsys.driimp.set_value('STOP_WORD','we');
ctxsys.driimp.set_value('STOP_WORD','were');
ctxsys.driimp.set_value('STOP_WORD','what');
ctxsys.driimp.set_value('STOP_WORD','when');
ctxsys.driimp.set_value('STOP_WORD','where');
ctxsys.driimp.set_value('STOP_WORD','whether');
ctxsys.driimp.set_value('STOP_WORD','which');
ctxsys.driimp.set_value('STOP_WORD','while');
ctxsys.driimp.set_value('STOP_WORD','who');
ctxsys.driimp.set_value('STOP_WORD','whose');
ctxsys.driimp.set_value('STOP_WORD','why');
ctxsys.driimp.set_value('STOP_WORD','will');
ctxsys.driimp.set_value('STOP_WORD','with');
ctxsys.driimp.set_value('STOP_WORD','would');
ctxsys.driimp.set_value('STOP_WORD','yet');
ctxsys.driimp.set_value('STOP_WORD','you');
ctxsys.driimp.set_value('STOP_WORD','your');
ctxsys.driimp.set_value('STOP_WORD','yours');

ctxsys.driimp.set_object('STORAGE','BASIC_STORAGE',2);
ctxsys.driimp.set_value('R_TABLE_CLAUSE','lob (data) store as (cache)');
ctxsys.driimp.set_value('I_INDEX_CLAUSE','compress 2');

commit;
COMMIT;
END;
/
CREATE INDEX "WLMMCE"."WL_NEWS_CTX" ON "WLMMCE"."WL_NEWS" ("FULL_TEXT")
INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('sync (on commit)')PARALLEL 1 ;

ALTER INDEX "WLMMCE"."WL_NEWS_CTX" NOPARALLEL;

[Updated on: Tue, 07 March 2006 11:31]

Report message to a moderator

Re: What am I missing in CTX Setup? [message #162783 is a reply to message #161876] Mon, 13 March 2006 11:31 Go to previous messageGo to next message
robajohnson
Messages: 9
Registered: February 2006
Location: Dallas Texas area
Junior Member

Well, either my question was so stupid and simple minded you all just rolled your eyes in disgust and did not even bother to answer OR I totally stumped all the bulging brains on this forum!

I prefer to believe the last one. Cool
Re: What am I missing in CTX Setup? [message #162810 is a reply to message #162783] Mon, 13 March 2006 13:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
You haven't said what version of Oracle you are using. Installations are different for each version. Apparently just running the script that you ran is insufficient. The best method of ensuring that all the right scripts are run from the right schemas with the right privileges, so you get all the pieces of any component properly installed, is to use the Oracle Universal Installer. Without knowing your version and what schema you ran it from with what privileges, we would only be guessing as to what might be missing, or how to fix it. You may find that other pieces are missing. I would start over and re-install using the Oracle Universal Installer.

Re: What am I missing in CTX Setup? [message #162816 is a reply to message #162810] Mon, 13 March 2006 14:06 Go to previous messageGo to next message
robajohnson
Messages: 9
Registered: February 2006
Location: Dallas Texas area
Junior Member

Oracle 10.2.0.1.0.

After database creation, per the docs, I ran the following script as sys:

$ORACLE_HOME/ctx/admin/catctx.sql

to create the CTXSYS user and it's objects supplying the four parms needed. The scripts ran clean.

>>I would start over and re-install using the Oracle Universal Installer.<<

So this strikes you as an executable installation error more than a simple setup error eh?

Rob
Re: What am I missing in CTX Setup? [message #162835 is a reply to message #162816] Mon, 13 March 2006 15:23 Go to previous message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
I am using 9i, so things are different and I cannot test index creation with 10g sync (on commit) syntax. However, I can create context indexes without receiving any errors about preferences. A standard installation sets up the default preferences automatically. Perhaps 10g is different; I don't know. I have seen several people complaining of problems after 10g text installation. All of the people complaining of problems did their installation by running scripts directly. I have not heard of problems when the Universal Installer was used.

ctxsys@ORA92> CREATE USER test IDENTIFIED BY test
  2  /

User created.

ctxsys@ORA92> GRANT CONNECT, RESOURCE, CTXAPP TO test
  2  /

Grant succeeded.

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

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
test@ORA92

test@ORA92> CREATE TABLE wl_bio (text VARCHAR2(60))
  2  /

Table created.

test@ORA92> CREATE INDEX WL_BIO_DESC_CTX
  2  ON WL_BIO(TEXT)
  3  INDEXTYPE IS ctxsys.context
  4  ;

Index created.

test@ORA92>

Previous Topic: To Retrive The image from oracle 9i
Next Topic: Contains function with CLOB not working anyone know why?
Goto Forum:
  


Current Time: Wed Sep 17 16:50:06 CDT 2014

Total time taken to generate the page: 0.09959 seconds