Home
Products
Documentation
Download
Forum
About us
Contact us

Cartridge API

 

jc_contains

jc_contains (target_structure IN VARCHAR2, query_structure IN VARCHAR2) = 1/0;

Performs substructure search on molecules or reactions.

SMILES strings given in query_structure are imported as SMARTS.

Returns 1 if the query matches the target as a substructure, otherwise it returns 0.

Examples:

select cd_id from jchemtable where jc_contains(cd_smiles, 'CN1C=NC2=C1C(=O)N(C)C(=O)N2C') = 1;
select id from plaintable where jc_contains(structure, 'CN1C=NC2=C1C(=O)N(C)C(=O)N2C') = 1;
 

jc_matchcount

jc_matchcount( target_structure IN VARCHAR2, query_structure IN VARCHAR2) = NUMBER;

Performs substructure search on molecules or reactions.

SMILES strings given in query_structure are imported as SMARTS

Returns the number of occurrences of the query structure in the target structure.

 

jc_equals

jc_equals (target_structure IN VARCHAR2, query_structure IN VARCHAR2) = 1/0;

Compares molecules or reactions and reports a match only if the compared structures are perfectly identical as they are represented in the respective arguments: the attributes of the compared structures will be compared without the query features (if any is found in the query) being evaluated. This is what is called in JChem terminology a perfect match as opposed to the exact match, which, in JChem's terminology, means a comparison where query features (if available in the query) are evaluated. ("exact" search means "exact size substructure search" -- see exact search with jc_compare.) Note that other products may use the terms perfect or exact match with meanings different from JChem's.

SMILES strings given in query_structure are imported as SMILES.

Returns 1 if there is a perfect match between the query and the target, otherwise it returns 0.

 

jc_compare

jc_compare (target_structure IN VARCHAR2, query_structure IN VARCHAR2, options IN VARCHAR2) = 1/0;

Compares molecules or reactions and reports a match by returning 1 or an unmatch by returning 0. The way the comparison is performed can be configured by setting various attributes to this operator.

The third argument is an (option list). The following options can be specified. (Detailed information about the semantics of these options can be found in the concepts section.)

Returns 1 if there is a match between the query and the target, otherwise it returns 0.

Usage Notes:

This operator is primarily intended to be used as the sole condition in the where clause. If you use it with other conditions, the Oracle optimizer may opt for a query plan where this operator is not evaluated as part of a domain index scan. In such cases, some of the options to this operator are not available, because their application would be counter-productive in terms of performance and/or a semantic non-sense.

Using the jc_compare as the sole condition in the WHERE clause is greatly facilitated by the availability of the filterQuery option: the other conditions needed for the main query may be packaged into the filter query.

Where not explicitly specified otherwise, the options can be combined arbitrarily:

  1. The following SQL statement performs a search on a subset of the structures stored in the plain table nci_10m and returns the number of structures in the subset which contain aromatic ring and have a logP greater than 9. The subset considered for the search is specified to belong to project #502:
    SELECT count(*) FROM nci_10m WHERE jc_compare(structure, 'c1ccccc1', 'sep=! t:s!ctFilter:logp()>9!filterQuery:select rowid from nci_10m where projid = 502') = 1
  2. The following SQL statement performs a search on a subset of the structures stored in the plain table t and returns the ids of structures in the subset whose similarity with an aromatic ring exceeds 0.9 and have a logP greater than 9. Only structures are included in the search which have been in stock since January 1, 2002:
    select id from t where jc_compare(structure, 'c1ccccc1', 'sep=! t:t!simThreshold:0.1!ctFilter:logp()>1!filterQuery:select rowid from t where suppl_date > DATE ''2002-01-01''') = 1;
 

jc_compare_vb

jc_compare_vb (target_structure IN VARCHAR2, query_structure IN BLOB) = 1/0;

Like jc_compare but with BLOB as the query structure argument.

 

jc_tanimoto

jc_tanimoto (target_structure IN VARCHAR2, query_structure IN VARCHAR2) = 0 .. 1;

Performs similarity search using Tanimoto formula on 2D chemical hashed fingerprints.

Returns similarity values in the range of 0.0 - 1.0. Higher values indicate higher similarity.

 

jc_dissimilarity

jc_dissimilarity (target_structure IN VARCHAR2, query_structure IN VARCHAR2) = 0 .. 1;

Performs similarity search using Tanimoto formula on 2D chemical hashed fingerprints.

Returns 1 - tanimoto value in the range of 0.0 - 1.0. Higher values indicate higher dissimilarity.

 

jcf.hitColorAndAlign

jcf.hitColorAndAlign(tblSchema VARCHAR2, tblName VARCHAR2, colName VARCHAR2, query CLOB, rowids VARCHAR2, options VARCHAR2, hitColorAndAlignOptions VARCHAR2) = rows of CLOB columns;

Returns rows each containing (in MRV format) one of target structures referred to by the rowids parameter with the part of the target matching the query aligned and colored according to hitColorAndAlignOptions.

Example: 

select * from table(jcf.hitColorAndAlign(
                             null, 'mystructtable', 'the_struct_col', 
                             'Brc1ccccc1',
                             'AAARVaAAEAAAJkpABP,AAARVaAAEAAAJkpABc,AAARVaAAEAAAJkpABp',
                             't:s', 'alignmentMode:rotate coloring:y'))

 

jchem_core_pkg.get_hit_count

jchem_core_pkg.get_hit_count
    (table_schema IN VARCHAR2,
     table_name IN VARCHAR2,
     column_name IN VARCHAR2,
     query_structure IN VARCHAR2,
     options IN VARCHAR2) = NUMBER;

Unlike the search operators which return each ROWID of the matching targets (such as jc_compare), this search function returns the number of hits. This function is useful, if you are interested only in the number hits (not in the hits themselves) as the call will be processed significantly faster than if each indiviual hit is identified in the table. It is especially useful with the 't:f' option to obtain a fast estimation of substructure search hit count.

Example: 

select jchem_core_pkg.get_hit_count('MYSCHEMA', 'MYTABLE', 'STRUCTURE', 'c1ccccc1[C,c]', 't:f') from dual;

 

jc_evaluate

jc_evaluate (target_structure IN VARCHAR2, expression IN VARCHAR2) = NUMBER;

Applies the chemical terms specified as the second parameter to the target_structure. The molecule context enables implicit reference to the target molecule. You can use the Chemical Terms Reference Tables as a short language reference on the available functions and plugins and refer to the Chemical Terms Language Reference as a more detailed description of the language. Some general examples and working examples are also available.

If expression is automatically calculated during structure insert, the precalculated value is used, which substantially reduces execution time. (See the autoCalcCt index parameter, the addAutoCalcCt alter index parameter [for regular structure tables] or the jchem_table_pkg.create_jctable [for JChem structure tables] on how to specify automatic Chemical Term calculation.) expression must exactly match the Chemical Terms specified for automatic calculation for the precalculated value to be used. The functions jchem_core_pkg.jctf_autocalccts and jchem_core_pkg.jctf_autocalccts_bycol can be used to query the expressions with precalculated values.

Note that the return type NUMBER limits the types of Chemical Terms expressions which can be used with this operator to those returning numbers or booleans. In the case of boolean results, this function returns 1 for true, 0 for false. More flexible variants of this operator are jc_evaluate_x, jc_evaluateb_x, jcf.t_evaluate, jcf.t_evaluateb.

Use jc_compare with the ctFilter option when chemical terms are needed as filter condition on searches.

WARNING: You may need to purchuse sepeate licenses to use this operator depending on the Chemical Terms used (logp, logd, ...)

Examples:

  1. The following statement returns the logP value of the specified structure:
    select jc_evaluate(<input-molecule>, 'logp()') from dual;
  2. The logD value at pH=7.4 of the input molecule:
    select jc_evaluate(<input-molecule>, 'logd("7.4")') from dual;
  3. Check the difference between logD values at two different pH-s:
    select jc_evaluate(<input-molecule>, 'logd("7.4") - logd("3.8")') from dual;
  4. The strongest acidic pKa value of the input molecule:
    select jc_evaluate(<input-molecule>, 'pka("acidic", "1")') from dual;
  5. The second strongest basic pKa value of the input molecule:
    select jc_evaluate(<input-molecule>, 'pka("basic", "2")') from dual;
  6. The topological polar surface area of the input molecule:
    select jc_evaluate(<input-molecule>, 'psa()') from dual;
  7. The number of H bond acceptor atoms in the input molecule:
    select jc_evaluate(<input-molecule>, 'acceptorCount()') from dual;
  8. The number of H bond donor atoms in the input molecule with taking the physiological microspecies at pH 7.4:
    select jc_evaluate(<input-molecule>, 'donorCount("7.4")') from dual;
  9. The following statement returns 1, if the structure <molecule> meets Lipinski's rule of 5s, or 0, if it does not meet them:
    select jc_evaluate(<molecule>, '(mass() <= 500) && (logP() <= 5) && (donorCount() <= 5) && (acceptorCount() <= 10)') from dual;
  10. Lead-likeness:
    select jc_evaluate(<input-molecule>, '(mass() <= 450) && (logD("7.4") >= -4) && (logD("7.4") <= 4) && (ringCount() <= 4) && (rotatableBondCount() <= 10) && (donorCount() <= 5) && (acceptorCount() <= 8)') from dual;

For more examples of chemical terms expressions please read the Evaluator and JChem Cartridge examples.

 

jc_evaluate_x

jc_evaluatex (target_structure IN VARCHAR2, options IN VARCHAR2) = VARCHAR2;

Applies to the target_structure the chemical terms specified as part of the options parameter .

The following options can be specified:

where <output-format> is the identifier of the output format and options as specified in the on line help of the command line tool molconvert.

If expression is automatically calculated during structure insert, the precalculated value is used, which substantially reduces execution time. (See the autoCalcCt index parameter, the addAutoCalcCt alter index parameter [for regular structure tables] or the jchem_table_pkg.create_jctable [for JChem structure tables] on how to specify automatic Chemical Term calculation.) expression must exactly match the Chemical Terms specified for automatic calculation for the precalculated value to be used. The functions jchem_core_pkg.jctf_autocalccts and jchem_core_pkg.jctf_autocalccts_bycol can be used to query the expressions with precalculated values.

Examples:

  1. Generate physiological microspecies at pH 7.4 for <intput-molecule>:
    select jc_evaluate_x(<intput-molecule>, 'chemTerms:microspecies("7.4") outFormat:smiles') from dual;

  2. Generate tautomers for NC1=C(CC=O)C=CCC1:
    select jc_evaluate_x('NC1=C(CC=O)C=CCC1', 'chemTerms:tautomers() outFormat:smiles') from dual;

    JC_EVALUATE_X('NC1=C(CC=O)C=CCC1','CHEMTERMS:TAUTOMERS()OUTFORMAT:SMILES')
    --------------------------------------------------------------------------------
    N=C1CCC=CC1CC=O
    N=C1CCCC=C1CC=O
    NC1=C(C=CO)C=CCC1
    OC=CC1C=CCCC1=N
    OC=CC1=CCCCC1=N
    OCC=C1C=CCCC1=N
    N=C1CCCCC1=CC=O
    NC1=CCC=CC1CC=O
    NC1=CCCC=C1CC=O
    NC1=CCC=CC1C=CO
    NC1=CCCC=C1C=CO

    JC_EVALUATE_X('NC1=C(CC=O)C=CCC1','CHEMTERMS:TAUTOMERS()OUTFORMAT:SMILES')
    --------------------------------------------------------------------------------
    NC1=CCC=CC1=CCO
    NC1=CCCCC1=CC=O
    NC1CCC=CC1=CC=O
    NC1=C(CCC=C1)C=CO
    NC1CC=CC=C1C=CO
    NC1CC=CCC1=CC=O
    NC1C=CCCC1=CC=O
    NC1C=CCC=C1C=CO
    OCC=C1CCC=CC1=N
    NC1=C(CC=CC1)C=CO
    OCC=C1CC=CCC1=N

    JC_EVALUATE_X('NC1=C(CC=O)C=CCC1','CHEMTERMS:TAUTOMERS()OUTFORMAT:SMILES')
    --------------------------------------------------------------------------------
    NC1=CC=CCC1=CCO
    NC1=C(CC=O)C=CCC1
  3. Generate resonants for NC1=C(CC=O)C=CCC1:
    select jc_evaluate_x('NC1=C(CC=O)C=CCC1', 'chemTerms:resonants() outFormat:smiles') from dual

    JC_EVALUATE_X('NC1=C(CC=O)C=CCC1','CHEMTERMS:RESONANTS()OUTFORMAT:SMILES')
    --------------------------------------------------------------------------------
    [NH2+]=C1CCC=C[C-]1CC=O
    [NH2+]=C1CC[CH-]C=C1CC=O
    NC1=C(C[CH+][O-])C=CCC1
    [O-][CH+]C[C-]1C=CCCC1=[NH2+]
    [O-][CH+]CC1=C[CH-]CCC1=[NH2+]
    N[C+]1CC[CH-]C=C1CC=O
    N[C+]1CC[CH-]C=C1C[CH+][O-]
    NC1=C(CC=O)C=CCC1

 

jc_evaluateb_x

jc_evaluateb_x (target_structure IN BLOB, options IN VARCHAR2) = BLOB;

Like jc_evaluate_x but with BLOB as the target argument and return type.

The function equivalent of this operator has the following signature:

jcf_evaluateb_x(target_structure IN BLOB, options IN VARCHAR2, temp_blob BLOB) = BLOB;

For the use of the temp_blob parameter, please, read the issue with dbms_lob.freetemporary in Known Issues.

 

jcf.t_evaluate

jcf.t_evaluate (target_structure IN VARCHAR2, options IN VARCHAR2) = ROWS OF COMPOSITE_CHARs;

The table function variant of jc_evaluate_x. If the Chemical Terms expression --specified as the chemTerms option-- evaluates to multiple distinct values (such as multiple molecular structures), this table function returns them iteratively as COMPOSITE_CHARs, instead of concatenating them in a single VARCHAR2. COMPOSITE_CHAR is defined as follows: 

    CREATE TYPE COMPOSITE_CHAR as OBJECT (c VARCHAR2(32767))

Example:

Generate tautomers for NC1=C(CC=O)C=CCC1: 

    SELECT c FROM table(jcf.t_evaluate('NC1=C(CC=O)C=CCC1', 'chemTerms:tautomers() outFormat:smiles'))

 

jcf.t_evaluateb

jc_evaluateb (target_structure IN BLOB, options IN VARCHAR2) = ROWS OF COMPOSITE_BLOBs;

The table function variant of jc_evaluateb_x. If the Chemical Terms expression evaluates to multiple distinct values (such as multiple molecular structures), this table function returns them iteratively as COMPOSITE_BLOBs, instead of concatenating them in a single BLOB. COMPOSITE_BLOB is defined as follows:

CREATE TYPE COMPOSITE_BLOB as OBJECT (b BLOB)
 

jc_molweight

jc_molweight (target_structure IN VARCHAR2) = NUMBER;

Returns the molecular weight of the target_structure.

 

jc_formula

jc_formula (target_structure IN VARCHAR2) = VARCHAR2;

The target_structure can be either a structure string in any format recognized by JChem or a column containing such structures.

Returns the molecular formula of the target_structure.

For calculating formula after the WHERE clause use jc_formula_eq


jc_formula_eq

jc_formula_eq (target_structure IN VARCHAR2, equals_to IN VARCHAR2) = NUMBER;

target_structure can be either a structure string in any format recognized by JChem or a column containing such structures.

equals_to is the formula which should be matched by the formula of the target_structure.

Returns 1 for match, and returns 0 otherwise.

 

jc_react

jc_react(reaction IN VARCHAR2, reactants IN VARCHAR2, options IN VARCHAR2) = VARCHAR2;

This function enumerates structures based on reaction modeling. It transforms either

given as the second argument into a list of products (the returned result) by applying the reaction model given in the first argument.

reaction: describes the rules for the transformation in the form of "reactant1.reactant2...reactantn > agent1.agent2...agentn > product1.product2...productn".

Reaction rules can be specified in a full reaction string: 

reaction..r:reactivity_rule..s:selectivity_rule..t:tolerance

reactants: contains either

options: a space separated list (option list) a which is composed of any of the following options:

Examples:

  1. The following example demonstrates the use of reaction rules: 
        SELECT jc_react('[C:1](=[O:2])[Cl:3].[H:99][N:4]([H:100])[C:0]>>[C:1](=[O:2])[N:4]([H:100])[C:0].[Cl:3][H:99]..' ||
                    'r:charge(ratom(3))>0..s:-charge(patom(4))', 'ClC(=O)c1ccccc1.CC(C)N', '') products FROM DUAL;
  2. In the following example reactants are fused to one multi-fragment reactant molecule before the reaction: 
        SELECT jc_react('[C:1](=[O:2])[Cl:3].[H:99][N:4]([H:100])[C:0]>>[C:1](=[O:2])[N:4]([H:100])[C:0].[Cl:3][H:99]',
                    'CC(C)N.ClC(=O)c1ccccc1', 'method:f') products FROM DUAL
  3. Like the previous examples, but functional group matches will be combined into one single product set: 
        SELECT jc_react('[C:1](=[O:2])[Cl:3].[H:99][N:4]([H:100])[C:0]>>[C:1](=[O:2])[N:4]([H:100])[C:0].[Cl:3][H:99]',
                    'CC(C)N.ClC(=O)c1ccccc1', 'method:a') products FROM DUAL
  4. The following example demonstrates the use of the standardizer option: 
        SELECT jc_react('[C:1](=[O:2])[Cl:3].[H:99][N:4]([H:100])[C:0]>>[C:1](=[O:2])[N:4]([H:100])[C:0].[Cl:3][H:99]',
                    'ClC(=O)c1ccccc1.CC(C)N', 'sep=~ standardizer:config:
    <Standardizer>
        <Actions>
        <Aromatize ID="aromatize"/>
        <Reaction ID="plusminus" Structure="[*+:1][*-:2]>>[*:1]=[*:2]"/>
        <Removal ID="keepOne" Method="keepLargest" Measure="molMass" Groups="target"/>
        </Actions>
    </Standardizer>
    ') products FROM DUAL;
  5. The following example demonstrates the use of the reverse option with the Beginelli-reaction: 
        SELECT jc_react('[#6]COC(=O)[C:3][C:4]([#6])=[O:19].[H:51][C:5](*)=[O:39].[H:49][N:2]C(=O)[N:1][H:50]>>'
                    || '[#6]COC(=O)[C:3]1=[C:4]([#6])[N:2]C(=O)[N:1][C:5]1*',
                    'CN1C(C2CCCCC2)C(C(=O)OCC3CCCCC3)=C(C4CCCCC4)N(C)C1=O',
                    'reverse:y outputType:reaction') FROM DUAL
  6. The following example demonstrates the use of the removeDuplProdRefs option: 
        select jc_react('[#6:3]C([O:1])C([#6:4])[O:2]>>[#6][C:3]=[O:1].[#6][C:4]=[O:2]',
                    'OC1CCCCC1O', 'removeDuplProdRefs:y') from dual
 
jc_react4

jc_react4(reaction IN VARCHAR2, reactant1 IN VARCHAR2, reactant2 IN VARCHAR2, reactant3 IN VARCHAR2, reactant4 IN VARCHAR2, options IN VARCHAR2) = VARCHAR2;

Similar to jc_react with the difference that it accepts as its second, third, fourth and fifth arguments four single reactants as well as SELECT SQL statements.

Examples:

  1. The following example combines a set of reactants taken from a table with each other and inserts the reactions into a table called jcprodcs: 
        SELECT jcf_react4(reaction_smart,
                      'SELECT cd_smiles, molecule_id FROM amol',
                      'SELECT cd_smiles, molecule_id FROM amol',
                      null,
                      null,
                      'reactantMatching:comb outFormat:sdf productIdTag:synthesis_id '
                      || 'productTable:jcprods productTableJcpt:jchemproperties '
                      || 'productIdColName:synthesis_id reactionId:amide')
         FROM dual;
    Explanation of the options:
    1. reactantMatching:comb Create reactant sets so as to combine each reactant returned by one SELECT statements with each reactant returned by every other SELECT statment.
    2. outFormat:sdf The format of the products must be SDF
    3. productIdTag:synthesis_id The product id must be put in to the "synthesis_id" SDF tag. (Note that the SDF files are stored in JChem tables by default in a compressed form and without their tags in the cd_structure column. To preserve the SDF tag in the original molecule, you must change the default setting [via the JChemManager GUI] and have JChem store the SDF file uncompressed.)
    4. productTable:jcprods Insert the products into the JChem-table "jcprods"
    5. productTableJcpt:jchemproperties The JChemProperties table of "jcprods" is "jchemproperties"
    6. productIdColName:synthesis_id Put the product ids in the column "synthesis_id" of the "jcprods" table. (Note that you can store the the product id both in an SDF-file tag [see above] and in a column at the same time).
    7. reactionId:amide The reaction id is "amide".
  2. The following example is similar to the previous with the exception that the products are returned with their synthesis code one-by-one instead of being inserted into a molecular structure table: 
            SELECT product,
               synthesis_code
        FROM TABLE(
            jcf.t_react4(
                reaction_smart,
                'SELECT cd_smiles, molecule_id FROM amol',
                'SELECT cd_smiles, molecule_id FROM amol',
                null,
                null,
                'reactantMatching:comb outFormat:smiles '
                || 'productIdTag:synthesis_id reactionId:amide'
            )
        );
 

jc_transform

jc_transform(reaction IN VARCHAR2, reactants IN VARCHAR2) = VARCHAR2;

This is a wrapper around jc_react. jc_react is called with the options method:n mappingstyle:d permuteReactants:y to provide Daycart compatible behaviour.

This operator has no function equivalent. If you need a function instead of an operator use jcf_react with the options method:n permuteReactants:y.

Example:

SELECT jcf_react('[C:1](=[O:2])[Cl:3].[H:99][N:4]([H:100])[C:0]>>[C:1](=[O:2])[N:4]([H:100])[C:0].[Cl:3][H:99]', 'CC(C)N.ClC(=O)c1ccccc1', 'method:n permuteReactants:y') products FROM DUAL;

 
jc_reactb4

jc_reactb4(reaction IN BLOB, reactant1 IN BLOB, reactant2 IN BLOB, reactant3 IN BLOB, reactant4 IN BLOB, options IN VARCHAR2) = BLOB;

Similar to jc_reactb with the difference that it accepts four single reactants as its second, third, fourth and fifth arguments.

Example:

The following procedure performs a reaction with two reactants so that the products come with a tag (data field) called PRID which includes a product identifier. This product identifier is generated based on the reactant identifier found in the ID of the first reactant, the ID2 field of the second reactant and the reaction identifier found in the RID field of the reaction data file.

The products are then stored into the JChem table test_products using jchem_table_pkg.jc_insertb so that the product identifiers are stored in the user defined column prod_id. 

        procedure react_store(reactant1 blob, reactant2 blob, reaction blob, cdidarr out cd_id_array) as
            product blob;
            dummy blob;
            product_table_name varchar2(30) := 'test_products';
        begin
            dbms_lob.createtemporary(product, false);
            dummy := jcf_reactb4(reaction, reactant1, reactant2, null, null
                        'outFormat:sdf reactionIdTag:RID reactantIdTags:ID,ID2 productIdTag:PRID',
                        product);
            cdidarr := jchem_table_pkg.jc_insertb(product, product_table_name, null, 'false',
                        'false', 'userDefColMap:PRID=prod_id');
            dbms_lob.freetemporary(product);
        end;

 
jcf.t_react4

jcf.t_react4(reaction IN VARCHAR2, reactant1 IN VARCHAR2, reactant2 IN VARCHAR2, reactant3 IN VARCHAR2, reactant4 IN VARCHAR2, options IN VARCHAR2) = ROWS OF CHAR_PRODUCT_RECORDs;

Similar to jc_react4 with the difference that it returns iteratively the products along with their syntesis codes packed in CHAR_PRODUCT_RECORDs, instead of returning just the products concatenated in one single VARCHAR2.

CHAR_PRODUCT_RECORD is defined as follows: 

    CREATE TYPE CHAR_PRODUCT_RECORD AS OBJECT (
        product VARCHAR(32767),
        synthesis_code VARCHAR(32767)
    );
Example: 
insert into project_punk (synth_id, interm_smiles) (
    select
        n_h_product.synthesis_code,
        n_h_product.product
    from
        rtant1,
        rtant2,
        rtant3,
        table(jcf.t_react4(
            'N1C=CC=C1.C1=CC=CC=C1.C1=CC=CC=C1>>BrC1=CNC=C1.C1=CC=CC=C1',
            rtant1.smiles_struct,
            rtant2.smiles_struct,
            rtant3.smiles_struct,
            null,
            'reactionId:NinaHagenReaction reactantIds:rtant1.' || rtant1.id
                || ',rtant2.' || rtant2.id
                || ',rtant3.' || rtant3.id))
        n_h_product
)

 

jc_standardize

jc_standardize(structure IN VARCHAR2, param IN VARCHAR2) = VARCHAR2;

Transforms structure based on the rules defined in param.

The second argument is an (option list). The following options can be specified:

Examples:

  1. Remove hydrochloric acid from ammonium salts and use the transformed structure in a substructure search as the query:
    stdized_query := jcf_standardize(ammo_salt, 'config:C[N+:1][H:2].[F,Cl,Br,I;H0-:3]>>C[N:1]'); 
    execute immediate 'select count(*) from jctable where jc_contains(cd_smiles, :query) = 1'
        into hitcount using stdized_query;
  2. Apply standardization stored in the "first" row of the stdconfig table. 
    select jc_standardize(cd_smiles, 'sql:select config from stdconfig where id = 1')
        from jc_nci_1k where cd_id < 10
  3. Clean the structure COC(=O)C1C(CC2CCC1N2C)CC(=O)C3=CC=CC=C3 based on the template stored in the column structure in the first row of the table templa 
    select jcf_standardize('COC(=O)C1C(CC2CCC1N2C)CC(=O)C3=CC=CC=C3',
        'sep=~ config:clean:tb~cleaningTemplate:select structure from templa~outFormat:mol') from dual
 

jc_standardizeb

jc_standardizeb(structure IN BLOB, param IN VARCHAR2) = BLOB;

Like jc_standardize but the structure to transform and the returned transformed value is also BLOB. The returned BLOB is a temporary BLOB. It is the responsibility of the caller to release the BLOB returned after it is no longer used.

The function equivalent of this operator has the following signature:

jcf_standardizeb(structure IN BLOB, param IN VARCHAR2, temp_blob BLOB) = BLOB;

For the use of the temp_blob parameter, please, read the issue with dbms_lob.freetemporary in Known Issues.

 

jc_molconvert

jc_molconvert (query_structure, options_outputformat IN VARCHAR2) = VARCHAR2;

Converts a molecular structure into a given format.

The second argument must be the identifier of the output format and options as specified in the on line help of the command line tool molconvert. The following options of molconvert are not supported for this operator: -, -(...), -s, -o. A list of input files cannot be specified either. To convert into binary formats, the operator jc_molconvertb have to be used.

Returns the molecular structure in a given format.

 

jc_molconvertb

jc_molconvertb (query_structure, options_outputformat IN VARCHAR2) = BLOB;

Converts a molecular structure into a given format. Recommended for binary formats, such as images (PNG).

The second argument must be the identifier of the output format and options as specified in the on line help of the command line tool molconvert. The following A list of input files cannot be specified either. To convert into textual formats, the operator jc_molconvert is more efficient to use.

Returns the molecular structure in a given format as a temprorary BLOB. The caller must make sure that the BLOB is freed after the underlying binary stream has been extracted. Unfreed temporary BLOBs eat up the temporary table space for the duration of the session which created it. See in Application Developer’s Guide - Large Objects (LOBs) the section corresponding to your programmatic environment  for how to free temporary LOBs.

To successfully use this operator to generate images, you have to enable image generation in Tomcat

The function equivalent of this operator has the following signature:

jcf_molconvertb (query_structure, options_outputformat IN VARCHAR2, temp_blob BLOB) = BLOB;

For the use of the temp_blob parameter, please, read the issue with dbms_lob.freetemporary in Known Issues.

 

jchem_core_pkg.jctf_autocalccts

jchem_core_pkg.jctf_autocalccts (index_schema_name IN VARCHAR2, index_name IN VARCHAR2) = table function returning one single column: C;

Returns the Chemical Terms expressions which are automatically evaluated on structures inserted in the column indexed by index_schema_name.index_name.

Example: 

SQL> select * from table(jchem_core_pkg.jctf_autocalccts('JCHEMUSER_MAIN', 'JCXAUTOCALCCTTEST'));
C
---------------------
logp()
rotatableBondCount()
pKa("acidic","2")

 

jchem_core_pkg.jctf_autocalccts_bycol

jchem_core_pkg.jctf_autocalccts_bycol(table_schema_name IN VARCHAR2, table_name IN VARCHAR2, column_name IN VARCHAR2) = table function returning one single column: C;

Returns the Chemical Terms expressions which are automatically evaluated on structures inserted in the column column_name of the table table_schema_name.table_name.

Example: 

SQL> select * from table(jchem_core_pkg.jctf_autocalccts_bycol('JCHEMUSER_MAIN', 'AUTOCALCCTTEST', 'STRUCTURE'));
C
---------------------
logp()
rotatableBondCount()
pKa("acidic","2")

 

jchem_table_pkg.create_jctable

jchem_table_pkg.create_jctable(jchem_table_name varchar2, jchem_property_table_name varchar2, number_of_ints number, number_of_ones number, number_of_edges number, coldefs varchar2, standardizerConfig varchar2, absoluteStereo boolean, options)

Creates a JChem table.

jchem_table_name
name of the JChem table to create

jchem_property_table_name
name of the JChem property table to use for the table. The JChem property table contains general options and information needed for using structure tables.

number_of_ints
the number of integers making up the fingerprint to be generated for the structures. The length of the fingerprint will be numberOfInts * 32 bits. (See also Chemical hashed fingerprints.)

number_of_ones
bits to be set for pattern. (See also Chemical hashed fingerprints.)

number_of_edges
maximum pattern length. (See also Chemical hashed fingerprints.)

coldefs
definitions of columns to be added to the table (in addition to those exclusively used by JChem). If not empty, then syntax is ", name1 type1, name2 type2, ...".

standardizerConfig
standardization configuration. If null, the default standardization is used.

absoluteStereo
assume absolute stereo flag: if not equal to 0, all query and target structures are treated as absolute stereo.

options
an (option list) accepting the following elements:

For more information, see the JChem Developers Guide.


Example:

The following statement creates a JChem table of the default type (holding specific targets only) called myjctable using the JChem properties table JChemProperties with 512 bit long fingerprints, to columns defined by the user, the standardized form of the imported structures being aromatized and assuming that all query and target structures are absolute stereo. The table is also assumed to have a logp colunm of numeric type which will hold the logp() values of imported structures (automatically calculated during import) and a rotbl_bnd_cnt column of numeric type whic will hold '1' for structures where the 'rotatableBondCount()>4' Chemical Terms expression returns true, and '0' where the expression returns false. 

    jchem_table_pkg.create_jctable('myjctable',  'JChemProperties', 16, 2, 6, ',
                                    RECNO NUMBER, DESCR VARCHAR2(4000)', 'aromatize', 1,
                                    'ctcolcfg:logp=logp();rotbl_bnd_cnt=rotatableBondCount()>4');
 

jchem_table_pkg.drop_jctable


jchem_table_pkg.drop_jctable(jchem_table_name varchar2, jchem_property_table_name varchar2)

Drops a JChem table.

jchem_table_name
name of the JChem table to drop

jchem_property_table_name
name of the JChem property table used for the table.


Example:

call jchem_table_pkg.drop_jctable('myjctable', 'JChemProperties');
 

jchem_table_pkg.list_jctables


jchem_table_pkg.list_arr(jcproptable varchar2) return char_array

Returns a list of JChem tables registered with jcproptable.


Example:

select * from table(jchem_table_pkg.list_jctables('Jchemproperties'));
 

jchem_table_pkg.jc_insert

jchem_table_pkg.jc_insert(structure IN VARCHAR2, table IN VARCHAR2, jChemPropName IN VARHCAR2, checkDuplicates IN VARHCAR2, haltOnDuplicate IN VARHCAR2, options IN VARHCAR2) = CD_ID_ARRAY

Inserts a structure into a JChem-generated table and returns a VARRAY of INTEGERs with the cd_id(s) of the newly inserted structure(s). If checkDuplicates is true and haltOnDuplicate is false, the cd_id of the first matching structure found in the database is returned as negative number (in case duplicate structures are found).

This function has overloaded version with CLOB and BLOB structure fields.

structure can be either a molecular structure in any format recognized by JChem, or a SELECT statement returning a single column containing such structures. In case of a SELECT statement, the statement will be performed and the structures returned by the statement will be insterted into the table.

table is the name of the table, into which the structure(s) will be inserted. If the schema name is not given, it is obtained from the connection. The table must be JChem-generated. The structure defined in the first parameter will be inserted into the cd_structure column of the new row. The values of the other JChem columns will be automatically calculated.

jChemPropName can be used to define the name of the JChemProperties table. If this parameter is null the default name of the JChemProperties table will be used: JChemProperties.

checkDuplicates, if set to "true", the target table will be checked for structures identical to the structure which is to be inserted. If the tautomerDuplicateChecking option of the table has been set, tautomers will be considered during duplicate checking.

haltOnDuplicate, if also set to "true" and there already exist(s) structure(s) in the target table identical to the new structure, the insert will be aborted with an error message. Otherwise, if duplicate structures are found, the cd_id of the first matching structure found in the database is returned as a negative number and an informational message is output to the current trace file in the <oracle-home>/admin/<your-db>udump directory.

options is an (option list). The following options are accepted:


Examples:

  1. The following call to jc_insert can be used to insert the structures found in the struct column of the table nci_import into the table my_structure_table. Duplicates will be checked, the chiral flags will be set. If duplicates and badly formatted structures are encountered along the way, they will be discarded without aborting the insertion process.
    jchem_table_pkg.jc_insert('SELECT struct FROM nci_import', 'my_structure_table', null, 'true', 'false', 'flags:c haltOnBadFormat:n');
  2. The following call has an effect similar to the previous example with the exception that values of the SDF tags NSC, CAS_RN, SMILES and HASH will also be stored in columns ns_code, cas_regno, smiles and molhash of the table my_structure_table respectively:
    jchem_table_pkg.jc_insert('SELECT struct FROM nci_import', 'my_structure_table', null, 'true', 'false', 'flags:c haltOnBadFormat:n userDefColMap:NSC=ns_code;CAS_RN=cas_regno;SMILES=smiles;HASH=molhash' );
 

jchem_table_pkg.jc_update

jchem_table_pkg.jc_update(structure IN VARCHAR2, table IN VARCHAR2, id IN NUMBER, jChemPropName IN VARHCAR2)

Updates a row of the JChem-table defined in the second parameter. If the table name is defined without the schema name the schema name is obtained from the connection. The structure defined in the first parameter will be placed in the cd_structure of the row which value of the cd_id column eqauls to the value of the id parameter. The procedure calculates the values of the other JChem columns.

The structure can be either in any format recognized by JChem.

The name of the JChemProperties table has to be defined in the third parameter. If this parameter is null the default name of the JChemProperties table will be used: JChemProperties.

This function has overloaded version with CLOB and BLOB structure fields.

jchem_table_pkg.jc_delete

jchem_table_pkg.jc_delete(table IN VARCHAR2, condition IN VARCHAR2, jChemPropName IN VARHCAR2)

Delete the row(s) of the JChem-table defined in the first parameter which meet the codition specified in the second parameter.

Example:

jchem_table_pkg.jc_delete('jc_nci_1m', 'WHERE cd_id > 1800000', null);

 

jchem_core_pkg.send_user_func

jchem_core_pkg.send_user_func(java_class_name VARCHAR2, separator VARCHAR2, param_list VARCHAR2) return VARCHAR2

Sends data to the JChem core engine to be processed by a user defined Java class.

Parameters:

The external Java class has to implement the JChemCartModul interface.

See more about user defined functions and examples: molconverter and getatomcount.

 

jchem_blob_pkg.send_user_func

jchem_blob_pkg.send_user_func(java_class_name VARCHAR2, separator VARCHAR2, param_list BLOB) return VARCHAR2

Like jchem_core_pkg.send_user_func but with BLOB argument as the parameter list.

 
Copyright © 1999-2007 ChemAxon Ltd.    All rights reserved.