keiichi
keiichi

Reputation: 163

Create an index on a CLOB column

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

Answers (1)

Littlefoot
Littlefoot

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:

  • include additional column (ID in my example) which will be used as a primary key
  • populate it with a trigger, using a sequence
  • column you used as a primary key until now set to be unique not null (which will act as if it was a primary key - no duplicates, no NULL values)

.

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

Related Questions