OraFAQ Forum: Text & interMedia » DetailDatastore

Home » Server Options » Text & interMedia » DetailDatastore

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DetailDatastore [message #133028]

Wed, 17 August 2005 06:52

kumarvision25
Messages: 19
Registered: August 2005
Location: a

Junior Member

hi all,

i have created the detailed datastore object i need to know how to call in query.

CREATE TABLE MY_MASTER (ARTICLE_ID NUMBER PRIMARY KEY,AUTHOR VARCHAR2(30),
TITLE VARCHAR2(50),BODY CHAR(1));
/

CREATE TABLE MY_DETAIL (ARTICLE_ID NUMBER,SEQ NUMBER,TEXT CLOB);
/
COMMIT;

ALTER TABLE MY_DETAIL ADD CONSTRAINT MY_DETAIL_FK
FOREIGN KEY (ARTICLE_ID)
REFERENCES MY_MASTER (ARTICLE_ID) ;

BEGIN
CTX_DDL.CREATE_PREFERENCE('MY_DETAIL_PREF', 'DETAIL_DATASTORE');
CTX_DDL.set_attribute('MY_DETAIL_PREF', 'binary', 'true');
CTX_DDL.set_attribute('MY_DETAIL_PREF', 'detail_table', 'MY_DETAIL');
CTX_DDL.set_attribute('MY_DETAIL_PREF', 'detail_key', 'ARTICLE_ID');
CTX_DDL.set_attribute('MY_DETAIL_PREF', 'detail_lineno', 'SEQ');
CTX_DDL.set_attribute('MY_DETAIL_PREF', 'detail_text', 'TEXT');
END;

CREATE INDEX MASTER_DETAIL_INDEX ON MY_MASTER(BODY) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('DATASTORE MY_DETAIL_PREF');

CREATE INDEX MASTER_DETAIL_INDEX
ON MY_MASTER(BODY)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('datastore CTXSYS.MY_DETAIL_PREF')
/

INSERT INTO MY_MASTER VALUES (101,'RISHI','DATASTORE','Y');
INSERT INTO MY_DETAIL VALUES(101,'00002','The direct datastore');
COMMIT;

I need to know how to query the my_detail table.

thanks

shashi

Report message to a moderator

Re: DetailDatastore [message #133130 is a reply to message #133028]

Wed, 17 August 2005 15:33

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

-- setup:

scott@ORA92> CREATE TABLE MY_MASTER
  2    (ARTICLE_ID NUMBER PRIMARY KEY,
  3  	AUTHOR	   VARCHAR2(30),
  4  	TITLE	   VARCHAR2(50),
  5  	BODY	   CHAR(1))
  6  /

Table created.

scott@ORA92> CREATE TABLE MY_DETAIL
  2    (ARTICLE_ID NUMBER,
  3  	SEQ	   NUMBER,
  4  	TEXT	   CLOB)
  5  /

Table created.

scott@ORA92> ALTER TABLE MY_DETAIL
  2  ADD CONSTRAINT MY_DETAIL_FK
  3  FOREIGN KEY (ARTICLE_ID)
  4  REFERENCES MY_MASTER (ARTICLE_ID)
  5  /

Table altered.

scott@ORA92> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('MY_DETAIL_PREF', 'DETAIL_DATASTORE');
  3    CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'binary', 'true');
  4    CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_table', 'MY_DETAIL');
  5    CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_key', 'ARTICLE_ID');
  6    CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_lineno', 'SEQ');
  7    CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_text', 'TEXT');
  8  END;
  9  /

PL/SQL procedure successfully completed.

scott@ORA92> CREATE INDEX MASTER_DETAIL_INDEX
  2  ON MY_MASTER (BODY)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS ('DATASTORE MY_DETAIL_PREF')
  5  /

Index created.

-- insert data:

scott@ORA92> INSERT INTO MY_MASTER
  2  VALUES (101, 'RISHI', 'DATASTORE', 'Y')
  3  /

1 row created.

scott@ORA92> INSERT INTO MY_MASTER
  2  VALUES (102, 'BARBARA', 'TEST', 'Y')
  3  /

1 row created.

scott@ORA92> INSERT INTO MY_DETAIL
  2  VALUES (101, '00002', 'The direct datastore')
  3  /

1 row created.

scott@ORA92> INSERT INTO MY_DETAIL
  2  VALUES (102, '00003', 'Something else ... whatever')
  3  /

1 row created.

scott@ORA92> COMMIT
  2  /

Commit complete.

-- synchronize index:

scott@ORA92> EXEC ctx_ddl.sync_index ('MASTER_DETAIL_INDEX')

PL/SQL procedure successfully completed.

-- queries:

scott@ORA92> SELECT *
  2  FROM   my_master
  3  WHERE  CONTAINS (body, 'direct') > 0
  4  /

ARTICLE_ID AUTHOR                         TITLE                                              B
---------- ------------------------------ -------------------------------------------------- -
       101 RISHI                          DATASTORE                                          Y

scott@ORA92> SELECT *
  2  FROM   my_master
  3  WHERE  CONTAINS (body, 'something') > 0
  4  /

ARTICLE_ID AUTHOR                         TITLE                                              B
---------- ------------------------------ -------------------------------------------------- -
       102 BARBARA                        TEST                                               Y

scott@ORA92>

Report message to a moderator

Re: DetailDatastore [message #133171 is a reply to message #133130]

Wed, 17 August 2005 23:35

kumarvision25
Messages: 19
Registered: August 2005
Location: a

Junior Member

Hi Barbara,

When i execute the select query i am getting no rows selected, but when i give the query as select * from my_master data is coming what is the problem is there any setup is needed.

SQL> SELECT *
2 FROM my_master
3 WHERE CONTAINS (body, 'something') > 0
4 /

no rows selected

SQL> SELECT * FROM MY_MASTER
2 /

ARTICLE_ID AUTHOR
---------- ------------------------------
TITLE B
-------------------------------------------------- -
101 RISHI
DATASTORE Y

102 BARBARA
TEST Y

Tell me any setup is needed.

thanks

shashi

Report message to a moderator

Re: DetailDatastore [message #133196 is a reply to message #133171]

Thu, 18 August 2005 01:01

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Did you synchronize the index, as I previously demonstrated, before selecting?

EXEC ctx_ddl.sync_index ('MASTER_DETAIL_INDEX')

Report message to a moderator

Re: DetailDatastore [message #133197 is a reply to message #133196]

Thu, 18 August 2005 01:05

kumarvision25
Messages: 19
Registered: August 2005
Location: a

Junior Member

Yes I synchronize it after that only i have given the select query

Report message to a moderator

Re: DetailDatastore [message #133201 is a reply to message #133197]

Thu, 18 August 2005 01:16

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

What do you get when you run the following query?

SELECT token_text FROM dr$master_detail_index$i;

Report message to a moderator

Re: DetailDatastore [message #133205 is a reply to message #133201]

Thu, 18 August 2005 01:17

kumarvision25
Messages: 19
Registered: August 2005
Location: a

Junior Member

below is the output

SQL> SELECT token_text FROM dr$master_detail_index$i;

TOKEN_TEXT
----------------------------------------------------------------
Y

Report message to a moderator

Re: DetailDatastore [message #133207 is a reply to message #133205]

Thu, 18 August 2005 01:22

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

You should be getting:

scott@ORA92> SELECT token_text
2 FROM dr$master_detail_index$i
3 /

TOKEN_TEXT
----------------------------------------------------------------
DATASTORE
DIRECT
ELSE
SOMETHING
WHATEVER

So, that means that either the data is not in your my_detail table or there is somethiing wrong with the indexing.

What do you get when you run this?

SELECT * FROM my_detail;

Report message to a moderator

Re: DetailDatastore [message #133209 is a reply to message #133207]

Thu, 18 August 2005 01:24

kumarvision25
Messages: 19
Registered: August 2005
Location: a

Junior Member

SQL> SELECT * FROM my_detail;

ARTICLE_ID SEQ
---------- ----------
TEXT
--------------------------------------------------------------------------------
101 2
The direct datastore

102 3
Something else ... whatever

Report message to a moderator

Re: DetailDatastore [message #133211 is a reply to message #133209]

Thu, 18 August 2005 01:28

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Please copy and paste the following to a .sql file, start that file, then copy and paste the results of the complete run.

drop table my_detail
/
drop table my_master
/
exec CTX_DDL.drop_PREFERENCE ('MY_DETAIL_PREF')
CREATE TABLE MY_MASTER 
  (ARTICLE_ID NUMBER PRIMARY KEY,
   AUTHOR     VARCHAR2(30),
   TITLE      VARCHAR2(50),
   BODY       CHAR(1))
/
CREATE TABLE MY_DETAIL 
  (ARTICLE_ID NUMBER,
   SEQ        NUMBER,
   TEXT       CLOB)
/
ALTER TABLE MY_DETAIL 
ADD CONSTRAINT MY_DETAIL_FK
FOREIGN KEY (ARTICLE_ID)
REFERENCES MY_MASTER (ARTICLE_ID)
/
BEGIN
  CTX_DDL.CREATE_PREFERENCE ('MY_DETAIL_PREF', 'DETAIL_DATASTORE');
  CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'binary', 'true');
  CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_table', 'MY_DETAIL');
  CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_key', 'ARTICLE_ID');
  CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_lineno', 'SEQ');
  CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_text', 'TEXT');
END;
/
CREATE INDEX MASTER_DETAIL_INDEX 
ON MY_MASTER (BODY) 
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('DATASTORE MY_DETAIL_PREF')
/
INSERT INTO MY_MASTER 
VALUES (101, 'RISHI', 'DATASTORE', 'Y')
/
INSERT INTO MY_MASTER 
VALUES (102, 'BARBARA', 'TEST', 'Y')
/
INSERT INTO MY_DETAIL 
VALUES (101, '00002', 'The direct datastore')
/
INSERT INTO MY_DETAIL 
VALUES (102, '00003', 'Something else ... whatever')
/
COMMIT
/
EXEC ctx_ddl.sync_index ('MASTER_DETAIL_INDEX')
SELECT token_text 
FROM   dr$master_detail_index$i
/
SELECT *
FROM   my_master
WHERE  CONTAINS (body, 'direct') > 0
/
SELECT *
FROM   my_master
WHERE  CONTAINS (body, 'something') > 0
/

Report message to a moderator

Re: DetailDatastore [message #133212 is a reply to message #133211]

Thu, 18 August 2005 01:40

kumarvision25
Messages: 19
Registered: August 2005
Location: a

Junior Member

yes i have copied and paste and i have run it but still the same output is coming....

SQL>
1 drop table my_detail
2 /
3 drop table my_master
4 /
5 exec CTX_DDL.drop_PREFERENCE ('MY_DETAIL_PREF')
6 CREATE TABLE MY_MASTER
7 (ARTICLE_ID NUMBER PRIMARY KEY,
8 AUTHOR VARCHAR2(30),
9 TITLE VARCHAR2(50),
10 BODY CHAR(1))
11 /
12 CREATE TABLE MY_DETAIL
13 (ARTICLE_ID NUMBER,
14 SEQ NUMBER,
15 TEXT CLOB)
16 /
17 ALTER TABLE MY_DETAIL
18 ADD CONSTRAINT MY_DETAIL_FK
19 FOREIGN KEY (ARTICLE_ID)
20 REFERENCES MY_MASTER (ARTICLE_ID)
21 /
22 BEGIN
23 CTX_DDL.CREATE_PREFERENCE ('MY_DETAIL_PREF', 'DETAIL_DATASTORE');
24 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'binary', 'true');
25 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_table', 'MY_DETAIL');
26 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_key', 'ARTICLE_ID');
27 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_lineno', 'SEQ');
28 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_text', 'TEXT');
29 END;
30 /
31 CREATE INDEX MASTER_DETAIL_INDEX
32 ON MY_MASTER (BODY)
33 INDEXTYPE IS CTXSYS.CONTEXT
34 PARAMETERS ('DATASTORE MY_DETAIL_PREF')
35 /
36 INSERT INTO MY_MASTER
37 VALUES (101, 'RISHI', 'DATASTORE', 'Y')
38 /
39 INSERT INTO MY_MASTER
40 VALUES (102, 'BARBARA', 'TEST', 'Y')
41 /
42 INSERT INTO MY_DETAIL
43 VALUES (101, '00002', 'The direct datastore')
44 /
45 INSERT INTO MY_DETAIL
46 VALUES (102, '00003', 'Something else ... whatever')
47 /
48 COMMIT
49 /
50 EXEC ctx_ddl.sync_index ('MASTER_DETAIL_INDEX')
51 SELECT token_text
52 FROM dr$master_detail_index$i
53 /
54 SELECT *
55 FROM my_master
56 WHERE CONTAINS (body, 'direct') > 0
57 /
58 SELECT *
59 FROM my_master
60* WHERE CONTAINS (body, 'something') > 0
SQL> SELECT *
2 FROM my_master
3 WHERE CONTAINS (body, 'something') > 0
4 /

no rows selected

SQL> SELECT *
2 FROM my_master;

ARTICLE_ID AUTHOR
---------- ------------------------------
TITLE B
-------------------------------------------------- -
101 RISHI
DATASTORE Y

102 BARBARA
TEST Y

Report message to a moderator

Re: DetailDatastore [message #133213 is a reply to message #133212]

Thu, 18 August 2005 01:46

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Apparently your feedback is off, so it is not possible to tell whether everything executed successfully. Please SET FEEDBACK ON, then run it again and copy and paste the results again.

Report message to a moderator

Re: DetailDatastore [message #133215 is a reply to message #133213]

Thu, 18 August 2005 01:55

kumarvision25
Messages: 19
Registered: August 2005
Location: a

Junior Member

yes i have did it but still it is coming same output only...
i will check this query in home pc and let u know....

Report message to a moderator

Re: DetailDatastore [message #133216 is a reply to message #133215]

Thu, 18 August 2005 01:58

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Please copy and paste the output and post it here. It may look the same to you, but I may see something different. I will be logging out soon and may not be back for 24 hours.

Report message to a moderator

Re: DetailDatastore [message #133217 is a reply to message #133216]

Thu, 18 August 2005 02:00

kumarvision25
Messages: 19
Registered: August 2005
Location: a

Junior Member

SQL> SET FEEDBACK ON
SQL>
1 drop table my_detail
2 /
3 drop table my_master
4 /
5 exec CTX_DDL.drop_PREFERENCE ('MY_DETAIL_PREF')
6 CREATE TABLE MY_MASTER
7 (ARTICLE_ID NUMBER PRIMARY KEY,
8 AUTHOR VARCHAR2(30),
9 TITLE VARCHAR2(50),
10 BODY CHAR(1))
11 /
12 CREATE TABLE MY_DETAIL
13 (ARTICLE_ID NUMBER,
14 SEQ NUMBER,
15 TEXT CLOB)
16 /
17 ALTER TABLE MY_DETAIL
18 ADD CONSTRAINT MY_DETAIL_FK
19 FOREIGN KEY (ARTICLE_ID)
20 REFERENCES MY_MASTER (ARTICLE_ID)
21 /
22 BEGIN
23 CTX_DDL.CREATE_PREFERENCE ('MY_DETAIL_PREF', 'DETAIL_DATASTORE');
24 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'binary', 'true');
25 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_table', 'MY_DETAIL');
26 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_key', 'ARTICLE_ID');
27 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_lineno', 'SEQ');
28 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_text', 'TEXT');
29 END;
30 /
31 CREATE INDEX MASTER_DETAIL_INDEX
32 ON MY_MASTER (BODY)
33 INDEXTYPE IS CTXSYS.CONTEXT
34 PARAMETERS ('DATASTORE MY_DETAIL_PREF')
35 /
36 INSERT INTO MY_MASTER
37 VALUES (101, 'RISHI', 'DATASTORE', 'Y')
38 /
39 INSERT INTO MY_MASTER
40 VALUES (102, 'BARBARA', 'TEST', 'Y')
41 /
42 INSERT INTO MY_DETAIL
43 VALUES (101, '00002', 'The direct datastore')
44 /
45 INSERT INTO MY_DETAIL
46 VALUES (102, '00003', 'Something else ... whatever')
47 /
48 COMMIT
49 /
50 EXEC ctx_ddl.sync_index ('MASTER_DETAIL_INDEX')
51 SELECT token_text
52 FROM dr$master_detail_index$i
53 /
54 SELECT *
55 FROM my_master
56 WHERE CONTAINS (body, 'direct') > 0
57 /
58 SELECT *
59 FROM my_master
60* WHERE CONTAINS (body, 'something') > 0
SQL> SELECT *
2 FROM my_master
3 WHERE CONTAINS (body, 'something') > 0
4 /

no rows selected

Report message to a moderator

Re: DetailDatastore [message #133218 is a reply to message #133217]

Thu, 18 August 2005 02:05

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Something is wrong. Once you have set feedback on, you should be seeing results like this:

scott@ORA92> drop table my_detail
2 /

Table dropped.

where it gives you feedback that tells you if the command was sucessful or not. Are you running this from SQL*Plus or from some tool like Toad?

Report message to a moderator

Re: DetailDatastore [message #133219 is a reply to message #133218]

Thu, 18 August 2005 02:06

kumarvision25
Messages: 19
Registered: August 2005
Location: a

Junior Member

sql plus only

Report message to a moderator

Re: DetailDatastore [message #133221 is a reply to message #133219]

Thu, 18 August 2005 02:12

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

It looks like you just copied and pasted the previous results. There aren't even SQL prompts. Look at the complete run that I originally posted and see the differences. There isn't anything that shows you starting a sql file either. If, for example, you copied the script to a file named test.sql, then set feedback on, and started that file, then your results should start like this:

SQL> set feedback on
SQL> start test.sql
SQL> drop table my_detail
2 /

Table dropped.

Report message to a moderator

Re: DetailDatastore [message #133230 is a reply to message #133221]

Thu, 18 August 2005 03:09

kumarvision25
Messages: 19
Registered: August 2005
Location: a

Junior Member

SQL>
1 drop table my_detail
2 /
3 drop table my_master
4 /
5 exec CTX_DDL.drop_PREFERENCE ('MY_DETAIL_PREF')
6 CREATE TABLE MY_MASTER
7 (ARTICLE_ID NUMBER PRIMARY KEY,
8 AUTHOR VARCHAR2(30),
9 TITLE VARCHAR2(50),
10 BODY CHAR(1))
11 /
12 CREATE TABLE MY_DETAIL
13 (ARTICLE_ID NUMBER,
14 SEQ NUMBER,
15 TEXT CLOB)
16 /
17 ALTER TABLE MY_DETAIL
18 ADD CONSTRAINT MY_DETAIL_FK
19 FOREIGN KEY (ARTICLE_ID)
20 REFERENCES MY_MASTER (ARTICLE_ID)
21 /
22 BEGIN
23 CTX_DDL.CREATE_PREFERENCE ('MY_DETAIL_PREF', 'DETAIL_DATASTORE');
24 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'binary', 'true');
25 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_table', 'MY_DETAI
26 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_key', 'ARTICLE_ID
27 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_lineno', 'SEQ');
28 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_text', 'TEXT');
29 END;
30 /
31 CREATE INDEX M_DETAIL_INDEX
32 ON MY_MASTER (BODY)
33 INDEXTYPE IS CTXSYS.CONTEXT
34 PARAMETERS ('DATASTORE MY_DETAIL_PREF')
35 /
36 INSERT INTO MY_MASTER
37 VALUES (101, 'RISHI', 'DATASTORE', 'Y')
38 /
39 INSERT INTO MY_MASTER
40 VALUES (102, 'BARBARA', 'TEST', 'Y')
41 /
42 INSERT INTO MY_DETAIL
43 VALUES (101, '00002', 'The direct datastore')
44 /
45 INSERT INTO MY_DETAIL
46 VALUES (102, '00003', 'Something else ... whatever')
47 /
48 COMMIT
49 /
50 EXEC ctx_ddl.sync_index ('M_DETAIL_INDEX')
51 SELECT token_text
52 FROM dr$master_detail_index$i
53 /
54 SELECT *
55 FROM my_master
56 WHERE CONTAINS (body, 'direct') > 0
57 /
58 SELECT *
59 FROM my_master
60* WHERE CONTAINS (body, 'something') > 0
SQL> start a.sql

Table dropped.

Table dropped.

PL/SQL procedure successfully completed.

Table created.

Table created.

Table altered.

PL/SQL procedure successfully completed.

Index created.

1 row created.

1 row created.

1 row created.

1 row created.

Commit complete.

PL/SQL procedure successfully completed.

TOKEN_TEXT
----------------------------------------------------------------
Y

1 row selected.

no rows selected

no rows selected

Report message to a moderator

Re: DetailDatastore [message #133372 is a reply to message #133230]

Thu, 18 August 2005 15:05

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

You have changed the name of the index in some places, but not in others. In some places, you have changed it to m_detail_index, but then you are selecting from dr$master_detail_index$i, instead of dr$m_detail_index$i, so that tells me nothing. I also cannot tell whether the script you posted is the script you ran. You need to drop everything that you have previously created, including indexes and preferences, and copy the script that I provided to a.sql, not make any chnges to it, then:

SQL> SET ECHO ON FEEDBACK ON
SQL> START a.sql

then copy and paste the results.

Report message to a moderator

Re: DetailDatastore [message #133406 is a reply to message #133372]

Thu, 18 August 2005 23:14

kumarvision25
Messages: 19
Registered: August 2005
Location: a

Junior Member

hi barbara,

I have dropped the index and preference and i run the script.
i cant able to create index some error is coming.

SQL> SET ECHO ON FEEDBACK ON
SQL> START a.sql
SQL> drop table my_detail
2 /
Table dropped

SQL> drop table my_master
2 /
Table dropped

SQL> CREATE TABLE MY_MASTER
2 (ARTICLE_ID NUMBER PRIMARY KEY,
3 AUTHOR VARCHAR2(30),
4 TITLE VARCHAR2(50),
5 BODY CHAR(1))
6 /

Table created.

SQL> CREATE TABLE MY_DETAIL
2 (ARTICLE_ID NUMBER,
3 SEQ NUMBER,
4 TEXT CLOB)
5 /

Table created.

SQL> ALTER TABLE MY_DETAIL
2 ADD CONSTRAINT MY_DETAIL_FK
3 FOREIGN KEY (ARTICLE_ID)
4 REFERENCES MY_MASTER (ARTICLE_ID)
5 /

Table altered.

SQL> BEGIN
2 CTX_DDL.CREATE_PREFERENCE ('MY_DETAIL_PREF', 'DETAIL_DATASTORE');
3 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'binary', 'true');
4 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_table', 'MY_DETAIL');
5 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_key', 'ARTICLE_ID');
6 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_lineno', 'SEQ');
7 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_text', 'TEXT');
8 END;
9 /

PL/SQL procedure successfully completed.

SQL> CREATE INDEX MASTER_DETAIL_INDEX
2 ON MY_MASTER (BODY)
3 INDEXTYPE IS CTXSYS.CONTEXT
4 PARAMETERS ('DATASTORE MY_DETAIL_PREF')
5 /
CREATE INDEX MASTER_DETAIL_INDEX
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-06509: PL/SQL: ICD vector missing for this package
ORA-06512: at "CTXSYS.UTL_RAW", line 289
ORA-06512: at "CTXSYS.DRIPARSE", line 37
ORA-06512: at "CTXSYS.DRIPARSE", line 1384
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 22

SQL> INSERT INTO MY_MASTER
2 VALUES (101, 'RISHI', 'DATASTORE', 'Y')
3 /
INSERT INTO MY_MASTER
*
ERROR at line 1:
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

SQL> INSERT INTO MY_MASTER
2 VALUES (102, 'BARBARA', 'TEST', 'Y')
3 /
INSERT INTO MY_MASTER
*
ERROR at line 1:
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

SQL> INSERT INTO MY_DETAIL
2 VALUES (101, '00002', 'The direct datastore')
3 /
INSERT INTO MY_DETAIL
*
ERROR at line 1:
ORA-02291: integrity constraint (CTXSYS.MY_DETAIL_FK) violated - parent key not
found

SQL> INSERT INTO MY_DETAIL
2 VALUES (102, '00003', 'Something else ... whatever')
3 /
INSERT INTO MY_DETAIL
*
ERROR at line 1:
ORA-02291: integrity constraint (CTXSYS.MY_DETAIL_FK) violated - parent key not
found

SQL> COMMIT
2 /

Commit complete.

SQL> EXEC ctx_ddl.sync_index ('MASTER_DETAIL_INDEX')
BEGIN ctx_ddl.sync_index ('MASTER_DETAIL_INDEX'); END;

*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-10502: index MASTER_DETAIL_INDEX does not exist
ORA-06512: at "CTXSYS.DRUE", line 157
ORA-06512: at "CTXSYS.CTX_DDL", line 1328
ORA-06512: at line 1

SQL> SELECT token_text
2 FROM dr$master_detail_index$i
3 /

TOKEN_TEXT
----------------------------------------------------------------
Y

1 row selected.

SQL> SELECT *
2 FROM my_master
3 WHERE CONTAINS (body, 'direct') > 0
4 /

no rows selected

SQL> SELECT *
2 FROM my_master
3 WHERE CONTAINS (body, 'something') > 0
4 /

no rows selected

Report message to a moderator

Re: DetailDatastore [message #133422 is a reply to message #133372]

Fri, 19 August 2005 00:30

kumarvision25
Messages: 19
Registered: August 2005
Location: a

Junior Member

hi barbara,

I have drop the old index and created the new index and run the script without error it is working fine but still the select query displays no rows selected

SQL> start c.sql
SQL> drop table my_detail
2 /

Table dropped.

SQL> drop table my_master
2 /

Table dropped.

SQL> CREATE TABLE MY_MASTER
2 (ARTICLE_ID NUMBER PRIMARY KEY,
3 AUTHOR VARCHAR2(30),
4 TITLE VARCHAR2(50),
5 BODY CHAR(1))
6 /

Table created.

SQL> CREATE TABLE MY_DETAIL
2 (ARTICLE_ID NUMBER,
3 SEQ NUMBER,
4 TEXT CLOB)
5 /

Table created.

SQL> ALTER TABLE MY_DETAIL
2 ADD CONSTRAINT MY_DETAIL_FK
3 FOREIGN KEY (ARTICLE_ID)
4 REFERENCES MY_MASTER (ARTICLE_ID)
5 /

Table altered.

SQL>
SQL> BEGIN
2 CTX_DDL.CREATE_PREFERENCE ('MY_DETAIL_PREF', 'DETAIL_DATASTORE');
3 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'binary', 'true');
4 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_table', 'MY_DETAIL');
5 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_key', 'ARTICLE_ID');
6 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_lineno', 'SEQ');
7 CTX_DDL.set_attribute ('MY_DETAIL_PREF', 'detail_text', 'TEXT');
8 END;
9 /

PL/SQL procedure successfully completed.

SQL> CREATE INDEX DETAIL_INDEX
2 ON MY_MASTER (BODY)
3 INDEXTYPE IS CTXSYS.CONTEXT
4 PARAMETERS ('DATASTORE MY_DETAIL_PREF')
5 /

Index created.

SQL> INSERT INTO MY_MASTER
2 VALUES (101, 'RISHI', 'DATASTORE', 'Y')
3 /

1 row created.

SQL> INSERT INTO MY_MASTER
2 VALUES (102, 'BARBARA', 'TEST', 'Y')
3 /

1 row created.

SQL> INSERT INTO MY_DETAIL
2 VALUES (101, '00002', 'The direct datastore')
3 /

1 row created.

SQL> INSERT INTO MY_DETAIL
2 VALUES (102, '00003', 'Something else ... whatever')
3 /

1 row created.

SQL> COMMIT
2 /

Commit complete.

SQL> EXEC ctx_ddl.sync_index ('DETAIL_INDEX')

PL/SQL procedure successfully completed.

SQL> SELECT token_text
2 FROM dr$DETAIL_INDEX$i
3 /

TOKEN_TEXT
----------------------------------------------------------------
Y

1 row selected.

SQL> SELECT *
2 FROM my_master
3 WHERE CONTAINS (body, 'direct') > 0
4 /

no rows selected

SQL> SELECT *
2 FROM my_master
3 WHERE CONTAINS (body, 'something') > 0
4 /

no rows selected

Report message to a moderator

Re: DetailDatastore [message #133439 is a reply to message #133422]

Fri, 19 August 2005 01:55

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

I need some more information to try to figure this out.

What is your Oracle version?

What schema are you running the script from?

Has the user that you are running it as been granted the ctxapp role?

Has the user that you are running it as been granted execute on ctx_ddl explicitly?

What are the results of the following queries?

SELECT OWNER FROM ALL_OBJECTS WHERE OBJECT_NAME = 'CTX_DDL';

SELECT ai.owner, ai.table_name, ai.index_name, ai.index_type,
aic.column_name
FROM all_ind_columns aic, all_indexes ai
WHERE ai.table_name IN ('MY_MASTER', 'MY_DETAIL')
AND ai.table_name = aic.table_name
/

Report message to a moderator

Re: DetailDatastore [message #133445 is a reply to message #133422]

Fri, 19 August 2005 02:02

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

One more thing: Does it work for you if you use varchar2 instead of clob?

Report message to a moderator

Re: DetailDatastore [message #133479 is a reply to message #133445]

Fri, 19 August 2005 05:21

kumarvision25
Messages: 19
Registered: August 2005
Location: a

Junior Member

hi barbara,

I have used varchar2 still it is coming the same output.

1) oracle version - oracle 9i
2) Schema - itpuser
3) Has the user that you are running it as been granted the ctxapp role?
Dont know, how to give permession
4) Has the user that you are running it as been granted execute on ctx_ddl explicitly?
No
5) SQL> SELECT OWNER FROM ALL_OBJECTS WHERE OBJECT_NAME = 'CTX_DDL';

OWNER
------------------------------
PUBLIC
CTXSYS
CTXSYS

6) SQL> SELECT ai.owner, ai.table_name, ai.index_name, ai.index_type,
2 aic.column_name
3 FROM all_ind_columns aic, all_indexes ai
4 WHERE ai.table_name IN ('MY_MASTER', 'MY_DETAIL')
5 AND ai.table_name = aic.table_name
6 /

OWNER TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ITPUSER MY_MASTER
SYS_C0057441 NORMAL
ARTICLE_ID

ITPUSER MY_MASTER
SYS_C0057441 NORMAL
BODY

OWNER TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

ITPUSER MY_MASTER
SYS_C0057441 NORMAL
ARTICLE_ID

ITPUSER MY_MASTER
SYS_C0057441 NORMAL

OWNER TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
BODY

ITPUSER MY_MASTER
MASTER_DETAIL_INDEX DOMAIN
ARTICLE_ID

ITPUSER MY_MASTER

OWNER TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
MASTER_DETAIL_INDEX DOMAIN
BODY

ITPUSER MY_MASTER
MASTER_DETAIL_INDEX DOMAIN
ARTICLE_ID

OWNER TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ITPUSER MY_MASTER
MASTER_DETAIL_INDEX DOMAIN
BODY

CTXSYS MY_MASTER
SYS_C0057568 NORMAL
ARTICLE_ID

OWNER TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

CTXSYS MY_MASTER
SYS_C0057568 NORMAL
BODY

CTXSYS MY_MASTER
SYS_C0057568 NORMAL

OWNER TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
ARTICLE_ID

CTXSYS MY_MASTER
SYS_C0057568 NORMAL
BODY

CTXSYS MY_MASTER

OWNER TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
DETAIL_INDEX DOMAIN
ARTICLE_ID

CTXSYS MY_MASTER
DETAIL_INDEX DOMAIN
BODY

OWNER TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
CTXSYS MY_MASTER
DETAIL_INDEX DOMAIN
ARTICLE_ID

CTXSYS MY_MASTER
DETAIL_INDEX DOMAIN
BODY

OWNER TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

thanks
shashi

Report message to a moderator

Re: DetailDatastore [message #133592 is a reply to message #133479]

Fri, 19 August 2005 18:37

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

It looks like you have created some stuff in ctxsys that may be in conflict with your other stuff. Try running the commands below, then re-running the whole other script and see what happens. You may need to connect as ctxsys and run the commands below, but do not run the other script while connected as ctxsys.

drop index master_detail_index
/
drop index ctxsys.detail_idex
/
exec ctx_ddl.drop_preference ('CTXSYS.MY_DETAIL_PREF')
drop table ctxsys.my_detail
/
drop table ctxsys.my_master
/

If that doesn't help, then try connecting as ctxsys and:

GRANT ctxapp TO itpuser;
GRANT EXECUTE ON ctx_ddl TO itpuser;

then reconnect as itpuser and test the other script again.

If all that doesn't help, then I hope someone else has some ideas, because I am starting to run out of things to check for. The code runs fine on my system, so the problem is not the code. It must be due to something that is different on your system, such as different privileges or conflicts with previously created stuff. The results that you have posted show that there is an index in the ctxsys schema that should not be there and that the Y of the clob column is the only thing tokenized, rather than the items in the detail table. It looks like when you run the query, it is using that old index. So, I think that the key to getting it to work is thoroughly removing all of the previously created conflicting stuff.

Report message to a moderator

Re: DetailDatastore [message #133727 is a reply to message #133592]

Sun, 21 August 2005 23:26

kumarvision25
Messages: 19
Registered: August 2005
Location: a

Junior Member

HI barbara,

know it is working fine, i have re-installed the oracle 9i.

thanks
shashi

Report message to a moderator

Previous Topic:	Query on Index does not return any results.
Next Topic:	end-of-file on communication channel while executing the query with CONTAINS

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 17 22:12:34 CST 2024