PL/SQL wrapper for chemaxon.formats.MdlCompressor

Hi all,





I'm trying to create a PL/SQL wrapper for java class chemaxon.formats.MdlCompressor loaded in JCHEM database scheme.





I have the following piece of code:





function convert (mol in VARCHAR2, flags in pls_integer) return VARCHAR2 as language java


name 'chemaxon.formats.MdlCompressor(java.lang.String, java.lang.int) return java.lang.String';





When I call this function I always get the error:





ORA-29540 class chemaxon/formats does not exist





The PL/SQL procedure is created in the same jchem owner scheme, so it shouldn't return that error.





The class exists and is correctly loaded into the scheme.





I think it may have something to do with the way loadjava is executed during the process of installing the cartridge.





I need this wrapper. What should I do?





Thanks a lot

If I query USER_OBJECTS view the class chemaxon/formats/MdlCompressor has INVALID status. But I don't think that's the problem.

The status was not the problem, I compiled the class and I'm still getting the same error.





Please help !!!

Try this:

Code:

function convert (mol in VARCHAR2, flags in pls_integer) return VARCHAR2 as language java      name 'chemaxon.formats.MdlCompressor.convert(java.lang.String, int) return java.lang.String';

Thanks a lot !!!!





And sorry for making you lose your time. I forgot the member function name in the declaration.





Thanks again.

The following problem is not closely related to this topic, but you might run into it. JChem classes sometime use the Java reflection API. Using such Java classes "cross-schema" (when they are loaded in a different schema than the current user's schema) requires some extra settings in Oracle which are not obvious to me (EXECUTE privilege on the class is not enough). Please, let me know if you find out what those extra settings are.





Thanks


Peter

If you use the Cartridge, you can also use the SQL operator "jc_molconvert" for converting structures into compressed or uncompressed mol formats. (And many more.)





http://www.jchem.com/doc/guide/cartridge/cartapi.html#jc_molconvert





All the best,


Szabolcs

Thank you very much Szabolcs !!!





I was about to ask what you just posted.





Thank you very much for your help.

I have a question regarding your last post.





I need to use the conversion function to allow pl/sql and sql to query structures table and return mol file o sdf representation of column cd_structure with no compression.





I'd like to use something like this:





select cd_id, jcf_molconvertb(cd_structure,'sdf') from jchem.structures where rownum <= 100;





But Oracle won't allow me to use a LONG RAW column (CD_STRUCTURE), it comes up with the error:





ORA-00997: illegal use of LONG TYPE.





In future releases of JChem I'd recommend you to make CD_STRUCTURE column of structures table a BLOB column instead of a LONG RAW which is deprecated.





I really need this functionality. Please help.





Thanks in advance.

The best way to achieve what you described in your posting is probably to implement a table function similar to the following example:

Code:

drop type mytypeset; drop type mytype; create type mytype as object(     cdid numeric(10,0),     sdf  varchar2(32000)     ); / show errors; create type mytypeset as table of mytype; / show errors; create or replace function sdf(sqlstm varchar2) return mytypeset pipelined is     TYPE cur_typ IS REF CURSOR;     c cur_typ;     out_rec mytype := mytype(null, null);     cd_id numeric(10,0);     cd_struct long raw; begin     open c for sqlstm;     loop         fetch c into cd_id, cd_struct;         exit when c%notfound;         out_rec.cdid := cd_id;         out_rec.sdf := jcf_molconvert(jchem_misc_pkg.rawtochar(cd_struct), 'sdf');         pipe row(out_rec);     end loop;     return; end; / show errors;

Please, note that instead of the jc_molconvert operator its functional equivalent (jcf_molconvert) is used. You can call the sdf table function like:

Code:

select * from table(sdf('select cd_id, cd_structure from jchem.structures where cd_id <= 100'));

Support for BLOBs in JChem Cartridge is planned for the next major version (JChem 3.1).

Thanks,





but what happens with structures larger than 32K (which is the limit of varchar).





We have lots of them larger than 32767 characters.





Can I create "mytype" using long raw instead of varchar2 and then call molconvertb. Will it work?





Thanks again.

Quote:

Can I create "mytype" using long raw instead of varchar2 and then call molconvertb. Will it work?

I guess it will not work:

1. you cannot use long raw in type definitions


2. molconvertb also takes the structure as a varchar2

For the moment, it seems that the only way I can serve you in this problem is to speed up implementation of blob support.





Actually, we have to distinguish between two kinds of supports for blobs in JChem Cartridge:

1. support for blob columns in JChem tables as opposed to the default long raw columns


2. support for blob types in functions and operators

I see.





Please, take into consideration to implement this functionality with BLOBs.





We need this functionality ASAP.





We're about to migrate or backend systems to JChem and we need this functionality. We have almost finished development.





Thanks a lot.