Reputation: 163
I have the table interventi
which has this structure
describe interventi;
Name Null Type
------------------------------ -------- -----------------------
DATAORA NOT NULL TIMESTAMP(6)
PARLAMENTARE NOT NULL VARCHAR2(16)
TESTO NOT NULL CLOB()
where the filed dataora is the primary key. I filled this table with a sigle row
DATAORA PARLAMENTARE TESTO
------------------------------- ---------------- -------------------------------
05-JUL-18 12.00.00.000000000 AM MRTMRZ (CLOB) PIPPO PLUTO PAPERINO
1 rows selected
Now, I want create an index on the field testo
create index idx_testo_interventi
on interventi(testo) indextype is
ctxsys.context;
but
Error starting at line 1 in command:
create index idx_testo_interventi
on interventi(testo) indextype is
ctxsys.context
Error at Command Line:1 Column:13
Error report:
SQL Error: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10528: primary keys of type TIMESTAMP(6) are not allowed
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366
29855. 00000 - "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause: Failed to successfully execute the ODCIIndexCreate routine.
*Action: Check to see if the routine has been coded correctly.
How can I create an index?
Upvotes: 3
Views: 13237
Reputation: 142968
A little tweak might help.
Here's what doesn't work:
SQL> create table interventi
2 (dataora timestamp(6) primary key,
3 parlamentare varchar2(16),
4 testo clob);
Table created.
SQL> create index idx_testo_interventi on interventi (testo)
2 indextype is ctxsys.context;
create index idx_testo_interventi on interventi (testo)
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10528: primary keys of type TIMESTAMP(6) are not allowed
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 366
And here's what you could do:
.
SQL> drop table interventi;
Table dropped.
SQL> create table interventi
2 (id number primary key,
3 dataora timestamp(6) unique not null,
4 parlamentare varchar2(16),
5 testo clob);
Table created.
SQL> create sequence seqa;
Sequence created.
SQL> create or replace trigger trg_bi_inter
2 before insert on interventi
3 for each row
4 begin
5 :new.id := seqa.nextval;
6 end;
7 /
Trigger created.
SQL> create index idx_testo_interventi on interventi (testo)
2 indextype is ctxsys.context;
Index created.
SQL>
[EDIT: how to run CTX_DDL]
You'll have to acquire privileges to run it. Here's how: connect as a privileged user (SYS
being one of them, if you didn't create another) and GRANT EXECUTE
on that package to user who is going to use it.
Have a look at the example: at first, it won't work (as you've already noticed):
SQL> exec ctx_ddl.sync_index('idx_testo_interventi');
BEGIN ctx_ddl.sync_index('idx_testo_interventi'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'CTX_DDL' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Connect as SYS
(or another privileged user, if you have it):
SQL> connect sys@xe as sysdba
Enter password:
Connected.
SQL> grant execute on ctx_ddl to scott;
Grant succeeded.
Back to the owner of the interventi
table (and the index):
SQL> connect scott@xe
Enter password:
Connected.
SQL> exec ctx_ddl.sync_index('idx_testo_interventi');
PL/SQL procedure successfully completed.
SQL>
Seems to be OK.
Upvotes: 2