jc_insert problem when using other parameters

Greetings "Peter ;-)",

so I was trying some jc_insert calls using sql*plus/sql developer when I found out that my default request (1) works without problems while when using other parameters (2) I'm having the following error (3), thank you for checking the code bellow:

(1):

declare

cd_id cd_id_array;

begin

cd_id :=jchem_table_pkg.jc_insert('qq1

  Marvin  03140315382D

--

 13 14  0  0  0  0  0  0  0  0999 V2000

    2.2043    0.1758    0.0000 O   0  0  0  0  0  0  0  0  0  0  0  0

    1.4898    0.5883    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    1.4898    1.4133    0.0000 O   0  0  0  0  0  0  0  0  0  0  0  0

    0.7754    0.1758    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    0.0609    0.5883    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -0.6536    0.1758    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -1.3680    0.5883    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -2.0825    0.1758    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -2.0825   -0.6492    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -1.3680   -1.0617    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -0.6536   -0.6492    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    0.0609   -1.0617    0.0000 O   0  0  0  0  0  0  0  0  0  0  0  0

    0.7754   -0.6492    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

  1  2  1  0  0  0  0

  2  3  2  0  0  0  0

  2  4  1  0  0  0  0

  4  5  2  0  0  0  0

  5  6  1  0  0  0  0

  6  7  2  0  0  0  0

  7  8  1  0  0  0  0

  8  9  2  0  0  0  0

  9 10  1  0  0  0  0

 10 11  2  0  0  0  0

  6 11  1  0  0  0  0

 11 12  1  0  0  0  0

 12 13  1  0  0  0  0

  4 13  1  0  0  0  0

M  END

','sub_structure',null,'true','true');

end;





(3)////////////////////////////////////////////////////////////////////////////////



Erreur commençant à la ligne 1 de la commande :

declare

cd_id cd_id_array;

begin

cd_id :=jchem_table_pkg.jc_insert('qq1

  Marvin  03140315382D

--

 13 14  0  0  0  0  0  0  0  0999 V2000

    2.2043    0.1758    0.0000 O   0  0  0  0  0  0  0  0  0  0  0  0

    1.4898    0.5883    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    1.4898    1.4133    0.0000 O   0  0  0  0  0  0  0  0  0  0  0  0

    0.7754    0.1758    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    0.0609    0.5883    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -0.6536    0.1758    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -1.3680    0.5883    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -2.0825    0.1758    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -2.0825   -0.6492    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -1.3680   -1.0617    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -0.6536   -0.6492    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    0.0609   -1.0617    0.0000 O   0  0  0  0  0  0  0  0  0  0  0  0

    0.7754   -0.6492    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

  1  2  1  0  0  0  0

  2  3  2  0  0  0  0

  2  4  1  0  0  0  0

  4  5  2  0  0  0  0

  5  6  1  0  0  0  0

  6  7  2  0  0  0  0

  7  8  1  0  0  0  0

  8  9  2  0  0  0  0

  9 10  1  0  0  0  0

 10 11  2  0  0  0  0

  6 11  1  0  0  0  0

 11 12  1  0  0  0  0

 12 13  1  0  0  0  0

  4 13  1  0  0  0  0

M  END

','sub_structure',null,'true','true');

end;

Rapport d'erreur :

ORA-29532: appel Java arrêté par une exception Java non interceptée : java.lang.RuntimeException: Error while getting the next hits: ORA-00600: code d'erreur interne, arguments : [kollasg-2], [], [], [], [], [], [], [], [], [], [], []

ORA-06512: à "KIET.JCHEM_TABLE_PKG", ligne 30

ORA-06512: à "KIET.JCHEM_TABLE_PKG", ligne 20

ORA-06512: à ligne 4

29532. 00000 -  "Java call terminated by uncaught Java exception: %s"

*Cause:    A Java exception or error was signaled and could not be

           resolved by the Java code.

*Action:   Modify Java code, if this behavior is not intended.



(2)///////////////////////////////////////////////////:

declare

cd_id cd_id_array;

begin

cd_id :=jchem_table_pkg.jc_insert('qq1

  Marvin  03140315382D

--

 13 14  0  0  0  0  0  0  0  0999 V2000

    2.2043    0.1758    0.0000 O   0  0  0  0  0  0  0  0  0  0  0  0

    1.4898    0.5883    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    1.4898    1.4133    0.0000 O   0  0  0  0  0  0  0  0  0  0  0  0

    0.7754    0.1758    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    0.0609    0.5883    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -0.6536    0.1758    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -1.3680    0.5883    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -2.0825    0.1758    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -2.0825   -0.6492    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -1.3680   -1.0617    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

   -0.6536   -0.6492    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

    0.0609   -1.0617    0.0000 O   0  0  0  0  0  0  0  0  0  0  0  0

    0.7754   -0.6492    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0

  1  2  1  0  0  0  0

  2  3  2  0  0  0  0

  2  4  1  0  0  0  0

  4  5  2  0  0  0  0

  5  6  1  0  0  0  0

  6  7  2  0  0  0  0

  7  8  1  0  0  0  0

  8  9  2  0  0  0  0

  9 10  1  0  0  0  0

 10 11  2  0  0  0  0

  6 11  1  0  0  0  0

 11 12  1  0  0  0  0

 12 13  1  0  0  0  0

  4 13  1  0  0  0  0

M  END

','sub_structure');

end;

Hi Idris,

How easy is this problem to reproduce on your end?

1. I have started executing the problematic JC_INSERT call with JChem version 5.12.3 without problem.

2. Then I dropped the JCC index (which was on cd_structure) and created a JCC index on cd_smiles.

3. With the new index, I started to get ORA-22275 , which appears to be a more civilized version of the error you get (see document ID 1418135.1 on metalink)

4. I then went on to test the issue in the development trunk, but I could reproduce it with neither the cd_structure nor cd_smiles column indexed.

5. Then I went back to 5.12.3 and couldn't find a way to reproduce the problem any more.

I hope the above summary will give you some inspiration, because I am kind of at my wits' end. (Based on what I know so far this looks pretty much an Oracle bug...I am not sure how much of a consolation this would to you, though...)

Peter

pkovacs wrote:

Hi Idris, How easy is this problem to reproduce on your end? 1. I have started executing the problematic JC_INSERT call with JChem version 5.12.3 without problem. 2. Then I dropped the JCC index (which was on cd_structure) and created a JCC index on cd_smiles. 3. With the new index, I started to get ORA-22275 , which appears to be a more civilized version of the error you get (see document ID 1418135.1 on metalink) 4. I then went on to test the issue in the development trunk, but I could reproduce it with neither the cd_structure nor cd_smiles column indexed. 5. Then I went back to 5.12.3 and couldn't find a way to reproduce the problem any more. I hope the above summary will give you some inspiration, because I am kind of at my wits' end. (Based on what I know so far this looks pretty much an Oracle bug...I am not sure how much of a consolation this would to you, though...) Peter

Actually it doesn't work with haltOnDuplicate set to true, elsewhere it is working fine.

Which column do you have the JCC-index on? On CD_STRUCTURE or on CD_SMILES? (Normally, we recommend to put the index on CD_STRUCTURE, but, in one of your earlier posts, I saw you search against the CD_SMILES column with JC_COMPARE.)

Peter

on both ^_^

You greedy! :-) I suggest to drop the one on CD_SMILES and see if the problem persists.

Thanks,

Peter

you know failed in love with indexes :-P , I did delete it before but still getting an error, but you know what I don't need to set the second one to true, one true is enough for me now ;-)
Thanks Peter, cheers