Oracle insufficient privilege error when inserting records

I have a stored procedure which insert records based on substructure match to another table, following illustrates what it does basically, and where i am getting Oralce insufficient privilege error:

 

execute immediate 'insert into structure_alert_result (smiles,pn);

select smiles, pn

from update_staging_tmp where jc_compare(smiles,''c1ccccc1'',''t:s'')=1'

 

 

 

but following plain SQL works just fine

insert into structure_alert_result(smiles,pn)

select smiles, pn from update_staging_tmp where jc_compare(smiles,'c1ccccc1','t:s')=1

We are upgraded Oracle to 11.2.0.3 recently the procedure worked fine in Oralce 11.1.

How do i fix this issue, it must related to cartrige  ....

Thanks

Dong

Dear Dong,

To use the JChem Cartridge functionalities in PL/SQL methods, privileges must be given directly to users , it isn't enough to give the privileges via roles. This behavior is not JChem Cartridge-specific, Oracle databases behave this way, so we can't make it easier. About giving the missing privileges, see the Admin guide in the documentation.

Regards,

Tamas

Hi Tamas,

You are right we forgot to execute what is need for list-sqls-for-jcc-user-privs when upgrading Oracle, though DBA did :call dbms_java.grant_permission('IP', 'SYS:java.net.SocketPermission', 'localhost:1099', 'resolve,connect'); but jchem did not do:

call jchem.privman_pkg.grants_on_jcobjs('jchem', 'ip');

just did this as jchem, the stored procedure works fine. It is always the little thing gets you.

Thanks

Dong