Create_jctable

Using Version TEST_2006_06_06





call jchem_table_pkg.create_jctable('DeleteMe', 'JChemproperties', 0.5, 2, 6, null, null, 0);





Get an ORA-06553: PLS-306: wrong number or types of arguments in call to 'CREATE_JCTABLE' in SQLPLUS.





I cannot work out which parameter is wrong. Can you spot anything?





Cheers





Paul

The last parameter of this proc is declared to be a boolean. If I am not mistaken, you have to call this proc from a PL/SQL block.





P.

Also, I am not sure whether the 0.5 value for the "numberOfInts" parameter makes sense or not. (I would think it does not makes sense.)





P.

Thanks Peter, I'll try from a PL/SQL block.





As for the value of number of ints, I mapped the values of the parameters to the GUI


as for the API CreateStructureTable which I got from this forum


(http://www.chemaxon.com/forum/ftopic1396.html)





I assumed the mappings were the same?





Paul

Paul,





The numberOfInts parameter means: the number of integers making up the fingerprint to be generated for the structures. More specifically the length of the fingerprint will be numberOfInts * 32 bits. If you specify 0.5, the length of the fingerprint will be 16 bits. (I obviously have to improve the documentation for the final release). 16 bits would be way too small for a typical scenario. The default is 16 integers = 512 bits.





Please see:


http://www.chemaxon.com/jchem/doc/user/fingerprint.html





P.

Hi Peter,





Thanks for your help so far.





I think I have uncovered a bug in the Create_JCTable stored proc. I am unable to get this to work. I initially thought it was because the database driver I was using doesn't support the Boolean datatype. So I created a wrapper stored proc which took a number rather than a boolean. However, this didn't work either. So I removed all the parameters and hardcoded them into the call to create_jctable (code below as copied from Oracle Enterprise Manager Console).





************** Start of Stored Proc Code ****************


authid current_user as


begin


jchem_table_pkg.create_jctable('DeleteMe',


'JChemProperties',


16,


2,


6,


'RECNO NUMBER',


'aromatize',


true);


end;


************** End of Stored Proc Code ****************





The error I get is


ORA-00932: inconsistent datatypes: expected Invalid SQL type.





I do not think it is an issue with the Oracle .NET data provider as no parameters are being passed to the wrapper (I also get the same error from SQLPLUS).





Cheers





Paul

Hi Paul,





The coldefs parameter follows the syntax of the --coldefs parameter of the command line jcman (http://www.chemaxon.com/jchem/doc/admin/#install subsection Command line usage):

Code:

Examples: $ jcman c strdata --coldefs ", name CHAR(200), stock INTEGER"\    --fplength 16 -l joe --driver org.gjt.mm.mysql.Driver\    --dburl jdbc:mysql://localhost/mydb -s

Notice the comma at the start of the value to --coldefs.





Accordingly, the following works for me:

Code:

create procedure oki as begin jchem_table_pkg.create_jctable('DeleteMe',  'JChemProperties', 16, 2, 6, ', RECNO NUMBER', 'aromatize', true); end; call oki();

Sorry for not having this in the docs. A proper doc on this feature will be prepared for the final release.





Cheers,





Peter





PS:


The missing comma in the VARCHAR2 parameter would not justify the Oracle error message:

Quote:

ORA-00932: inconsistent datatypes: expected Invalid SQL type.

Still, I suggest trying first what works for me.

Hi Peter,





Thanks for your speedy response. I modified my wrapper stored procedure as you indicated and I still get the same error (both via VB.NET and SQLPLUS).





Could it be down to the version of the JRE I'm using? I'm using 1.5.0_07.





Cheers





Paul

Hi Paul

Quote:

Could it be down to the version of the JRE I'm using? I'm using 1.5.0_07.

Do you mean the Java version used to run Tomcat?





Please, could you post the entire error message you get in SQLPLUS?





Thanks


Peter

The full error message from SQLPLUS is

Code:

SQL> call create_jctable_wrapper(); call create_jctable_wrapper()      * ERROR at line 1: ORA-00932: inconsistent datatypes: expected Invalid SQL type

Quote:

Do you mean the Java version used to run Tomcat?

Yes. That is the only Java on path.





JAVA_HOME = 'C:\Progra~1\Java\jre1.5.0_07'


'C:\Program Files\Java\jre1.5.0_07\bin' is on path.





I remember when I installed the JChem cartridge initially that the oracle JRE conflicted with 1.5.0_07, and to get it all to work I had to remove all other java versions from the path. Not sure if this is significant (it should all be backwards compatible anyway).

I could reproduce the problem on Oracle 9i. I am going to check where it comes from.





Cheers,





Peter

Apologies, I should have said I was on 9i





Paul

Hi,





Oracle 9i does not allow the boolean type between pl/sql and java. I have to change the boolean type to number (with 0 meaning false, and everything else true).





The fix will be in the next main branch release (either in 3.2 or a test release whichever comes first).





In case you are eager to go ahead with your tests, you can apply the patch as follows:





Assume that JChem Cartridge is installed in the "jchem" schema.





1.) Decompress the attached JcTableFunctions.class.gz files


2.) Load the resulting Java class file into Oracle by executing on the command line:

Code:

loadjava -user jchem/<pwd>@<oracle-dbname> JcTableFunctions.class

If you are on Windows you need loadjava.bat instead of loadjava.


2.) Drop the package jchem.jchem_table_pkg


4.) Recreate the modified version of the jchem.jchem_table_pkg attached (jc.sql)





Cheers,





Peter

Peter,





Thanks for that fix. I have installed it and verified that it works OK.





Cheers





Paul