Error with when creating index on table in long schema name

User 952e1d9361

02-02-2011 17:16:33

When using the cartridge to create a JChem index on a table in a schema with a long name then an error is generated. The user is created like this (i.e the maximum length) :

create user DB3456789012345678901234567890 identified by DB3456789012345678901234567890 default tablespace users temporary tablespace temp;

grant connect,resource,unlimited tablespace to DB3456789012345678901234567890;

and then when connected as this user :

SQL> create table structures (casnum varchar2(32), common_name varchar2(256), structure clob);

Table created.

SQL> Insert into STRUCTURES (CASNUM,COMMON_NAME,STRUCTURE) values ('58-08-2','Caffeine','CN1C=NC2=C1C(=O)N(C)C(=O)N2C');

1 row created.

SQL> commit;

Commit complete.

SQL> create index struct_jchem_idx on structures (structure)

indextype is jchem.jc_idxtype

parameters('tableType=anyStructures');

create index struct_jchem_idx on structures (structure)

ERROR at line 1:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-29532: Java call terminated by uncaught Java exception:

java.rmi.ServerException: RemoteException occurred in server thread; nested

exception is:

java.rmi.RemoteException: ORA-12899: value too large for column

"DB3456789012345678901234567890"."JC_IDX_PROPERTY"."PROP_VALUE" (actual: 51,

maximum: 50)

ORA-06512: at "JCHEM.JCHEM_CORE_PKG", line 65

ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 17

SQL>

Even if I manually alter the JC_IDX_PROPERTY column width in both the JCHEM schema and the 'long' schema above it still fails. It looks like the JC_IDX_PROPERTY table in the user schema is being re-created each time?

Is there a workaround to this?

We are on JChem 5.2 by the way.

Steve

ChemAxon aa7c50abf8

03-02-2011 15:02:20

Hi Steve,

Thank you for the detailed problem report.

This is a bug and will be fixed in jchem 5.4.2.

Work around:

After you create the schema and before you create the first JChem index, pre-create the jc_idx_property table in the long-named user's schema and insert some garbage into it like this:

create table jc_idx_property (prop_name varchar2(4000), prop_value varchar2(4000), prop_value_ext long raw);


insert into jc_idx_property values('a', 'a', null);


commit;

Peter