Cartridge Permissions....

I'm having some problems getting a non-jchem user to be able to use the jc_insert functionality.  I've opened all the permissions I can think of in a few different ways but have found little success.  Here is the jchem user insert (which works):

D:\ChemAxon\JChem\cartridge>sqlplus  jchem/jchem@xxxx 
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 21 12:25:29 2009 
Copyright (c) 1982, 2005, Oracle.  All rights reserved. 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options 
SQL> set serveroutput on
SQL> declare
  2     v_mol BLOB;
  3     v2                                      cd_id_array := cd_id_array(NULL);
  4  begin
  5     select jc_molconvertb('c1ccccc1', 'sdf:-a -2') into v_mol from dual;
  6     v2 := jc_insertb(v_mol, 'TEST_TABLE', 'JCHEMPROPERTIES', 'true', 'false');
  7     commit;
  8  end;
  9  / 
PL/SQL procedure successfully completed. 
SQL> 

Now I've followed the permissions suggestions here (http://www.chemaxon.com/jchem/doc/admin/cartridge.html#users).  I've also attempted to grant any permissions I can think of but I continually get this message:

D:\ChemAxon\JChem\cartridge>sqlplus  xxxx/xxxx@xxxx 
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 21 12:23:56 2009 
Copyright (c) 1982, 2005, Oracle.  All rights reserved. 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options 
SQL> set serveroutput on
SQL> declare
  2     v_mol CLOB;
  3             v2                                      cd_id_array := cd_id_array(NULL);
  4  begin
  5     select molfile into v_mol from moltable where moltable_id = 1;
  6     v2 := jc_insert(v_mol, 'TEST_TABLE', 'JCHEMPROPERTIES', 'true', 'false');
  7     commit;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.RuntimeException: Index 'null.null' contains obsolete data.Please,
drop the index and use JChemManager to upgrade the table
(http://www.chemaxon.com/jchem/doc/admin/#regener) before recreating the
jc_idxtype type.
ORA-06512: at "JCHEM.JCHEM_TABLE_PKG", line 30
ORA-06512: at "JCHEM.JC_INSERT", line 8
ORA-06512: at line 6 

Now the error message is telling me that I need to upgrade the table but I think it's actually mis-labeled error.  There is no data in the table.  The table was created by jcman on the newly installed cartridge.

Here are the installation details.

SQL> select jchem_core_pkg.getenvironment() from dual; 
JCHEM_CORE_PKG.GETENVIRONMENT()
--------------------------------------------------------------------------------
Oracle environment:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 64-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production 
JChem Server environment:
Java VM vendor: Sun Microsystems Inc.
Java version: 1.6.0_16
Java VM version: 14.2-b01 
JCHEM_CORE_PKG.GETENVIRONMENT()
--------------------------------------------------------------------------------
JChem version: 5.2.5
JChem Index version: 5020400
JDBC driver version: 11.1.0.7.0-Production 

Is there any way to verify the correct permissions?  Can someone look into the error message and let me know if there is a chance it's actually another problem?

Thanks in advance,

Jim

Jim,

It is not clear from your post whether the jchem user and the non-jchem user share TEST_TABLE and the JCHEMPROPERTIES table or not. Are they sharing both tables? Only one of them? None of them?

I have tested the non-jchem user operating on his own test_tabel and jchemproperties table and that scenario worked for me.

I assume that JChem Cartridge is installed the jchem user's schema. Is this correct?

Peter

Peter,

Sorry for the confusion.  Both users share the same structure and property table (which is in the JCHEM schema).  Essentially I created the JCHEM schema, installed the cartridge, created the tables, and tested the functionality.  Everything worked perfectly.  I then created the non-jchem user and am attempting to have it access the JCHEM schema table and cartridge functionality.

I was actually hoping that line 8 of JC_INSERT would give you some clue as to what permission or problem I'm running into.  Everything seems to be working in the JCHEM schema.  Is there some underlying tables or java classes that an external user needs access to in order to complete the jc_insert call?

-Jim

Jim,

A blind shot in the dark: did you create synonyms for both jchem.test_tables and jchem.jchemproperties tables so the non-jchem user can refer to them as shown in your code without schema prefix?

Peter