Java Stored Procedure trouble

Hi,

I am not sure if this is the correct chemaxon forum for this question.

 

Our db environment is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0.

I'm trying to make a Java Stored Procedure that makes use of the API.  The below example is simplified relative to what we ultimately need, but it reproduces the problem.

Starting materials:

AKChemTest.java -- this contains the Java Stored Procedure code (file is attached)

mb5413/ -- local directory that contains all MarvinBeans*jar files taken from marvinbeans-5.4.1.3.zip (the highest version compatible with Java 1.5 (used internally by Oracle 11g))

Creation of Java Stored Procedure:

/usr/1r43_java/bin/javac -cp mb5413/*:.:/usr/1r43_tomcat/1r43reg/WEB-INF/lib/ojdbc6.jar -target 1.5 -source 1.5 akpkg2/AKChemTest.java

/usr/1r43_java/bin/jar -cvf akpck2.jar akpkg2/*class

/home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/loadjava -user registry1r43/XXXXX@reg1r43p -resolve -grant JCC_BASIC_ROLE -v -genmissing akpck2.jar mb5413/*jar

sqlplus registry1r43/XXXXX@reg1r43p

CREATE OR REPLACE FUNCTION ak_getfullstructuretest (lots_id IN NUMBER)

RETURN CLOB AS LANGUAGE JAVA

NAME 'akpkg2.AKChemTest.GetFullStructureTest (int) return oracle.sql.CLOB';

.

/

The problem:

If I start calling the function a large number of times in a row, it is stable for a while (~2600 structures), but then I start to get these errors:

ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException (DBD ERROR: OCIStmtFetch) [for Statement "select ak_getfullstructuretest(1629206) from dual"] 

(I'm calling the stored procedure from a perl script)

Once I start getting these errors, then I cannot successfully call the function on parameter id's that were previously successful, even directly through sqlplus:

select ak_getfullstructuretest(1620700) from dual;   (<-- previously 1620700 gave a successful response)

ERROR:

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

java.lang.NullPointerException

 

However, if I dropjava/loadjava and then remake the function, then I can once again have "select ak_getfullstructuretest(1620700) from dual;" work OK:

/home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/dropjava -user registry1r43/XXXXX@reg1r43p akpck2.jar mb5413/*jar

/home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/loadjava -user registry1r43/XXXXX@reg1r43p -resolve -grant JCC_BASIC_ROLE -v -genmissing akpck2.jar mb5413/*jar

sqlplus registry1r43/XXXXX@reg1r43p

CREATE OR REPLACE FUNCTION ak_getfullstructuretest (lots_id IN NUMBER)

RETURN CLOB AS LANGUAGE JAVA

NAME 'akpkg2.AKChemTest.GetFullStructureTest (int) return oracle.sql.CLOB';

.

/

 

SQL> select ak_getfullstructuretest(1620700) from dual;

AK_GETFULLSTRUCTURETEST(1620700)

--------------------------------------------------------------------------------

  Mrv0541 12191211202D

 35 39  0  0  0  0            999 V2000

   -0

 

Can you provide any guidance on how to avoid these NullPointerExceptions?  Please let me know if any other information would be useful.

Thanks very much in advance.

Best,

Andrew

 

Hi,

 

Could you please locate the corresponding full java stack trace in the Oracle trace file and paste it here ?

 

Best regards,

Szilard 

Hi,

 

Here you go!  Thanks for the help

 

Exception in thread "Root Thread" java.lang.NullPointerException

        at chemaxon.marvin.modules.Parity.determinantToParity(Unknown Source)

        at chemaxon.marvin.modules.Parity.stereoCalculationTH(Unknown Source)

        at chemaxon.marvin.modules.Parity.localParityTH(Unknown Source)

        at chemaxon.marvin.modules.Parity.modfunc(Unknown Source)

        at chemaxon.struc.MoleculeGraph.getLocalParity(Unknown Source)

        at chemaxon.marvin.io.formats.mdl.MolExport.appendCtabV2(Unknown Source)

        at chemaxon.marvin.io.formats.mdl.MolExport.appendCtab(Unknown Source)

        at chemaxon.marvin.io.formats.mdl.MolExport.convert(Unknown Source)

        at chemaxon.struc.Molecule.exportToObject(Unknown Source)

        at chemaxon.struc.Molecule.exportToObject(Unknown Source)

        at chemaxon.struc.Molecule.exportToFormat(Unknown Source)

        at chemaxon.struc.Molecule.toFormat(Unknown Source)

        at akpkg2.AKChemTest.GetFullStructureTest(AKChemTest.java)

It seems that the exception is coming during the conversion to mol format.

Can you attach the string representation of the problematic molecule?

As far as I see it should be the "t1" string (which is imported at line 36 and exported again at line 44).

 

Hi,

I modified the Java code to be the attached.

If I compare the returned value for t1:

1) when the code is running stably

2) for the same molecule after the run/function has de-stabilized

.. they are exactly the same (only different by "CAUGHT\n").

 

(I'll need to get clearance to post the actual mol file .. let me know if you are interested in that .. or just in knowing there is no difference in the recovered string for the stable and unstable run)

 

Thanks,

Andrew

Hi,

We did not assume that the input string changes.

Please send the confidential strucute to our support e-mail address.

support _at_ chemaxon.com  

Best regards,

Szilard

 

Just sent.  Thanks!

I have tried to reproduce the exception using java API without database connection in  marvinbeans-5.4.1.3.zip, but I could not get the exception. Could you reproduce the exception using plain java API? Or is the database layer needed? Should be able to reproduce the exception using only this molecule?

We need help from you to be able to reproduce the problem. An exampe to reproduce the exception would be just great.

Hi,

Unfortunately it seems to require database layer, and even then it is not straight-forward (see original post -- function works fine for many structures, and then doesn't work, even for the same structures).

Perhaps it would be better to avoid Java Stored Procedures if possible?  Do jc_* or jcf.* methods exist for?:

     Molecule.fuse()

     findfrags()/arrangeMolecules()

     setAbsStereo()

If we can use these functions in Oracle, but not Java Stored Procedure, then that would be an OK solution.

Thanks for the help.

Best, Andrew

Hi Andrew,

In general we do not recommend the use of the Oracle-side Java with our libraries if possible to avoid this.

(does not support 1.6, so only very old versions can be used, not the standard Java we develop and test with)

In general, with the use of the User Defined Functions you can basically execute any custum code on th JChem Server side (standard hotspot Java), and call it from Oracle:

http://www.chemaxon.com/jchem/doc/dev/cartridge/index.html#userdef

 

Some but not all things you have mentionad are possible

Chemical Terms (jc_evaluate):

http://www.chemaxon.com/marvin/help/chemicalterms/EvaluatorFunctions.html#fragmentsex

Standardizer (jc_standardize):

https://www.chemaxon.com/jchem/doc/user/StandardizerConfiguration.html#actionstring

"absolutestereo:clear" (clear absolute stereo flag)
"absolutestereo:set" (set absolute stereo flag)

Standardizer also offers 2D cleaning, though that also means recalclationg all coordinates, not just arranging the fragments.

For the rest you can use the user defined functions.

I hope this helps, please let us know if you have any questions.

Best regards,

Szilard

Hi,

Thanks .. the "user defined function" is exactly what I'm looking for!

Can you provide some guidance for returning a CLOB from my_op/my_func?  What Object must my doFunc() return?  oracle.sql.CLOB?  Just a String?

Thanks again .. I think this is almost solved.

Best,

Andrew

Hi,

I'm glad that this solution seems to be feasible for you.

Most of the experts are on holiday, I try to give a partial answer.

Since this code runs on the JChem Server side, I would assume there should be less problems trying with a String first (not sure how a CLOB would behave trough RMI).

Also easier to try first, as you can follow the molconvert example:

http://www.chemaxon.com/jchem/examples/cartridge/user_def_func/molconvert/index.html

I think the String chould be good inside PL/SQL for up to 32k characters at least, I guess you may want to convert there to CLOB if the return value will be used in DML statement.

Please take this with a grain of salt, the real experts may have more to add.

Best regards,

 

Szilard

Thanks .. I think I have it working .. I'll re-post if the CLOBs become a problem.

Fyi, the on-line documentation looks to be out-of-date, referring to setting environmental variable JCART_XCLASSPATH instead of adding xclasspath to jcart.properties.

Thanks again .. happy holidays!

Thank you, we will update the documentation in our further versions!

Tamas