ORA-12899: value too large for column CD_FORMULA

Hi,

We use JChem 5.6.0.2 on Oracle Database 10.2.0.5 x86_64.

When I've inserted attached mol I've got the error:

ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
ORA-29532: Java call terminated by uncaught Java exception: oracle.jdbc.driver.OracleSQLException: ORA-12899: value too large for column "TESTER"."MD_U_STRUCTS_I_J_JCX"."CD_FORMULA" (actual: 200, maximum: 100)
ORA-06512: at "JCHEM.JCHEM_CLOB_PKG", line 112
ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 843

Hi,

Starting with JChem version 5.8, the width of the formula column in question will be increased to 4000 characters in the index table.

In the meantime, please, increase the column width manually:

alter table "TESTER"."MD_U_STRUCTS_I_J_JCX" modify (cd_formula varchar2(4000));

Peter

Ok, I've altered column already.

Thanks, Peter.

... but I have the problem when I copy schema in another place:

 In source schema I altered column to increase its size, than I've imported all my data which required big cd_formula column.

Than I've imported data into the new schema with all datum. Than I've tried to create index and I've got the ORA-12899, because table already contains data which require big cd_formula column.

How I can continue building index in this case?

I can alter table for domain index to increase column size, but what I need to execute to resume index build?

Thanks.

Pavel

Hi Pavel,

I am afraid INDEX REBUILD will try to enforce the smaller column size. It looks like the work-around suggested above is rather fragile... At this point, I don't see a way to rebuild the index without the fix, I am afraid. I'll see if I can fit the fix into 5.7.

Peter

pkovacs wrote:

Hi Pavel, I am afraid INDEX REBUILD will try to enforce the smaller column size. It looks like the work-around suggested above is rather fragile... At this point, I don't see a way to rebuild the index without the fix, I am afraid. I'll see if I can fit the fix into 5.7. Peter

I see :(

When JChem 5.7 will be release?

Pavel

Pavel,

5.7 will be out perhaps as early as next week. It's been actually code-freezed for quite some time... I can't guarantee that the fix will make it into 5.7, but I'll try my best.

Peter

Thanks, Peter and Good Luck!

Pavel,

Since I couldn't get the related changes into 5.7.0, they will be included in 5.8. A preview release (based on production JChem version 5.7.0) with the JChem Cartridge fixes and improvements planned for 5.8.0 is available here: https://shared.chemaxon.com/projects/jcc/jchem-jcc-5.8preview-on-5.7.zip . Please, have a look when/if you have time.

Thanks

Peter

Sorry, Peter, I didn't test 5.8 preview for this issue (I have no time these days).

But I found some kind of workaround:

Initial state: we have table TTT with some structures which require big CD_FORMULA column.

1. Create JChem index TTT_IJ with haltOnError=n option (we will see error messages as in top of the post). The result of this trick will be: we still have our "big cd_formula" rows in TTT, but we can't find them by TTT.rowid -> TTT_IJ_JCX.rid reference in domain index table TTT_IJ_JCX.

2. execute ALTER TABLE to modify CD_FORMULA (see top of the post)

3. save "big cd_formula" rows in other table TTT_BAK:

create table TTT_BAK as
select * from TTT
where rowid in (select rowid from TTT
                minus
                select rid from TTT_IJ_JCX);

4. delete these "big cd_formula" rows from TTT:

delete from TTT
where rowid in (select rowid from TTT
                minus
                select rid from TTT_IJ_JCX);
commit;

5. reinsert it into modified table TTT:

insert into TTT select * from TTT_BAK;


commit;

6. check result and drop TTT_BAK table.

 

That's all - we have all "big cd_formula" rows in TTT again.

Pavel

Hi Pavel,

Nice work-around! Thank you for sharing it with this forum!

Peter

Pavel,

JChem Cartridge 5.8.0 has been released with significantly increased room for molecular formulae associated with regular structure tables.

Regards,

Peter

Hi, Peter.

I'm reading email about it just now :))

Cool!

Thanks