Help with JC_CONTAINS and JC_MOLCONVERT

Hi Peter,

A couple of quick questions:

I set up a basic table called sampledata_jchem with the structure stored as a blob.
I created a jchem index on the structure field.

The following query works:

SELECT corpid FROM sampledata_jchem WHERE jc_compare(structure, 'Nc1ccccc1N', 't:s')=1

This query does not work:

SELECT corpid FROM sampledata_jchem WHERE jc_contains(structure, 'Nc1ccccc1N')=1;

Here is the error:
ORA-29900: operator binding does not exist
ORA-06553: PLS-306: wrong number or types of arguments in call to 'JC_CONTAINS'
29900. 00000 -  "operator binding does not exist"
*Cause:    There is no binding for the current usage of the operator.
*Action:   Change the operator arguments to match any of the existing
           bindings or add a new binding to the operator.
Error at Line: 34 Column: 42

This query also does not work:
select corpid, jc_molconvert(structure, 'smiles') from sampledata_jchem where corpid = 'DS-000124';

ORA-29900: operator binding does not exist
ORA-06553: PLS-306: wrong number or types of arguments in call to 'JC_MOLCONVERT'
29900. 00000 -  "operator binding does not exist"
*Cause:    There is no binding for the current usage of the operator.
*Action:   Change the operator arguments to match any of the existing
           bindings or add a new binding to the operator.
Error at Line: 36 Column: 15


What is the correct syntax?


Thanks for the help.

Guy

Info:

select jchem_core_pkg.getenvironment() from dual;

Oracle environment:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0    Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

JChem Server environment:
Java VM vendor: Sun Microsystems Inc.
Java version: 1.6.0_18
Java VM version: 16.0-b13
JChem version: 5.3.8
JChem Index version: 5030300
JDBC driver version: 11.1.0.7.0-Production

Hi Guy,

You can use the following anonymous block to find out what bindings an operator is available with:

        declare

            current_binding number := 0;

            buf varchar2(250);

        begin

            for r in (select binding#, argument_type

                      from all_oparguments

                      where owner = upper('pkovacs_trunk')

                        and operator_name = upper('jc_compare') order by binding#, position)

            loop

                if r.binding# = current_binding then

                    buf := buf || ', ' || r.argument_type;

                else

                    if buf is not null then

                        dbms_output.put_line(buf);

                    end if;

                    buf := r.argument_type;

                    current_binding := r.binding#;

                end if;

            end loop;

            dbms_output.put_line(buf);

        end;

        /

You have to replace

1. 'pkovacs_trunk' with the name of schema where JChem Cartridge is installed and


2. 'jc_compare' with the operator you are interested in.

This block will generate an output similar to what you can find for the documentation of the jc_compare operator: 

    target_s    query_s     option_list

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

    VARCHAR2,   VARCHAR2,   VARCHAR2

    CLOB,       CLOB,       VARCHAR2

    CLOB,       VARCHAR2,   VARCHAR2

    CLOB,       BLOB,       VARCHAR2

    BLOB,       BLOB,       VARCHAR2

 

Let me know if this doesn't help.

Peter

In the case of jc_molconvert, I suggest to use jcf.molconvertb. (I.e. the molconvert function in the jcf package.)

Peter

Guy,

I realized that the anonymous block above for finding out operator bindings was not complete. The print out of the "trailing" binding was missing from after the loop:

end loop;
dbms_output.put_line(buf);

I have made the correction above so the block is now (hopefully) complete.

Sorry,

Peter