DRG-50504: invalid column datatype for document table (Doc ID 2296399.1)

Last updated on AUGUST 11, 2017

Applies to:

Oracle Text - Version 12.2 BETA1 and later
Information in this document applies to any platform.

Goal

Getting error at ctx_cls.clustering on a 12.2 database:

DRG-50504: invalid column datatype for document table

SQL> desc content
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ELEMENT_ID NOT NULL VARCHAR2(144)
EXTENSION VARCHAR2(9)
CONTENT BLOB
CONTENT_SIZE NOT NULL NUMBER(10)
CREATE_DATE TIMESTAMP(6)
REFCOUNT NUMBER(10)
SCF_DATA RAW(32)

SQL>
SQL> -- "CONTENT" column contains PDF
SQL>
SQL> select count(*) from content;

COUNT(*)
----------
  263118

1 row selected.

SQL>
SQL> drop index CTX_CONTENT;

Index dropped.

SQL> CREATE INDEX CTX_CONTENT ON OSNL003.CONTENT(CONTENT) INDEXTYPE IS CTXSYS.CONTEXT parameters('nopopulate');

Index created.

SQL>
SQL> SELECT * FROM ctx_user_index_errors;

no rows selected

SQL>
SQL> SELECT idx_name,idx_table_owner,idx_table,idx_status,idx_type
2 FROM ctx_user_indexes
3 order by 1;

IDX_NAME IDX_TABLE_OWNER IDX_TABLE
------------------------------ ------------------------------ ------------------------------
IDX_STATUS IDX_TYP
------------ -------
COLLECTIONX OSNL003 COLLECTION
INDEXED CONTEXT

CTX_CONTENT OSNL003 CONTENT
INDEXED CONTEXT


2 rows selected.

SQL>
SQL> exec dbms_stats.gather_table_stats('OSNL003','CONTENT')

PL/SQL procedure successfully completed.

SQL>
SQL> drop table clusters purge;

Table dropped.

SQL> create table clusters (
2 clusterid NUMBER,
3 descript varchar2(4000),
4 label varchar2(200),
5 sze number,
6 quality_score number,
7 parent number);

Table created.

SQL>
SQL> /* set the preference */
SQL> exec ctx_ddl.drop_preference('my_cluster');

PL/SQL procedure successfully completed.

SQL> exec ctx_ddl.create_preference('my_cluster','KMEAN_CLUSTERING');

PL/SQL procedure successfully completed.

SQL> exec ctx_ddl.set_attribute('my_cluster','CLUSTER_NUM','3');

PL/SQL procedure successfully completed.

SQL>
SQL> /* do the clustering */
SQL> exec ctx_output.start_log('my_log');

PL/SQL procedure successfully completed.

SQL> exec ctx_cls.clustering('ctx_content','element_id','restab','clusters','my_cluster');
BEGIN ctx_cls.clustering('ctx_content','element_id','restab','clusters','my_cluster'); END;

*
ERROR at line 1:
ORA-20000: Oracle Text error:
DRG-50504: invalid column datatype for document table
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_CLS", line 415
ORA-06512: at line 1


SQL> exec ctx_output.end_log;

PL/SQL procedure successfully completed.

SQL>
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms