Cartridge API

• Searching:
  • jc_contains
  • jc_matchcount
  • jc_equals
  • jc_compare
  • jc_compare_vb
  • jc_tanimoto
  • jc_tversky
  • jc_dissimilarity
  • jcf.hitColorAndAlign
  • jchem_core_pkg.get_hit_count
  • jcf.formula_search
• Calculation / Property prediction:
  • jc_evaluate (logp, logd, psa, acceptorCount, donorCount, charge, etc.)
  • jc_evaluate_x (tautomers, mesomers, microspecies, etc.)
  • jc_evaluateb_x (tautomers, mesomers, microspecies, etc.)
  • jcf.t_evaluate (logp, logd, psa, acceptorCount, donorCount, charge, etc.)
  • jcf.t_evaluateb (logp, logd, psa, acceptorCount, donorCount, charge, etc.)
  • jc_molweight
  • jc_formula / jc_formula_eq
• JChem-table package (jchem_table_pkg):
  • create_jctable
  • drop_jctable
  • list_jctables
  • jc_insert
  • jc_update
  • jc_delete
• User defined function:
  • jchem_core_pkg.send_user_func
  • jchem_blob_pkg.send_user_func
• Miscellanious Functions:
  • jc_react
  • jc_react4
  • jc_reactb4
  • jcf.t_react4
  • jc_transform
  • jc_standardize
  • jc_standardizeb
  • jc_molconvert
  • jc_molconvertb
  • jchem_core_pkg.jctf_autocalccts
  • jchem_core_pkg.jctf_autocalccts_bycol
  • jcf.exec_v
  • jchem_misc_pkg.cache_stmts
  • jcf.insertable

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. (In domain index scan mode, it currently can be used only to select matching target, i.e. returning 1. For selecting non-matching targets in domain index scans, please, use jc_compare with the earlyResults option set to '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 duplicate match as opposed to the full structure match, which, in JChem's terminology, means a comparison where query features (if available in the query) are evaluated. (see full search with jc_compare.) Note that other products may use the term exact match for duplicate search. On the other hand, JChem versions prior to 5.2 used exact to refer to full structure search. (It was renamed to "full" to reduce confusion.)

SMILES strings given in query_structure are imported as SMILES.

Returns 1 if the query matches the target as identical, otherwise it returns 0. (In domain index scan mode, it currently can be used only to select matching target, i.e. returning 1. For selecting non-matching targets in domain index scans, please, use jc_compare with the earlyResults option set to '0'.)

jc_compare

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

Compares molecules or reactions and reports a match by returning 1 or an unmatch by returning 0. (For selecting non-matching targets in domain index scans, the earlyResults option must be set to '0'.) The way the comparison is performed can be configured by setting various attributes to this operator.

The type of the target_structure and the query_structure can be either VARCHAR2 or CLOB or BLOB. This operator is currently available with the following signatures:

    target_s    query_s     option_list
    -----------------------------------
    VARCHAR2,   VARCHAR2,   VARCHAR2
    CLOB,       CLOB,       VARCHAR2
    CLOB,       VARCHAR2,   VARCHAR2
    CLOB,       BLOB,       VARCHAR2
    BLOB,       BLOB,       VARCHAR2
    BLOB,       VARCHAR2,   VARCHAR2

• t (search type)
• haltOnError
• maxHitCount
• maxTime
• simThreshold
• dissimilarityMetric
• descriptorName
• screenConfig
• absoluteStereo
• exactQueryAtomMatching
• radical
• isotope
• charge
• doubleBondStereo
• vagueBond
• HCountMatching
• implicitHMatching
• exactStereoMatching
• stereoSearchType
• stereoModel
• ignoreTetrahedralStereo
• ignoreDoubleBondStereo
• ignoreAlleneStereo
• ignoreAxialStereo
• ignoreSynAntiStereo
• ignoreCTExceptions
• mix
• ignoreMixtureBrackets is deprecated in favor of mix.
• polymer
• endGroupMatching
• phaseShift
• transformMonomer
• copolymerMatching
• tautomer is deprecated in favor of tautomerSearch
• tautomerSearch
• tdf
• checkSpHyb
• undefinedRAtom
• RLigandEqualityCheck
• bridgingRAllowed
• attachedDataMatch
• attachedDataPrefixes
• ctFilter
• filterQuery
• earlyResults
• requireCommit
• exactSpecialAtomMatching
• resultTimeout
• threadCnt
• usrOpId
• indexCost
• funcCost

• t:<search-type>

  Sets the search type. <search-type> may be one of the following:

  • s: substructure search (default)
  • na: substructure search fingerprint-only (returns the result of the fingerprint screening without performing an atom-by-atom search). This option is available only when the jc_compare operator is evaluated as part of a domain index scan.
  • f: full structure search; query and target must have the same heavy atom network for matching. All features are otherwise evaluated the same way as substructure search.
  • ff: full fragment search; query must be full matching to a target fragment. (See f above.)
  • d: duplicate search
  • i: similarity search. The simThreshold or dissimilarityThreshold can be used to specify the similarity or dissimilarity threshold respectively. Their default value is 0.7 and 0.3 respectively.
  • u: superstructure search (isin()).
  • t: similarity search. Deprecation: this value has been deprecated in favor of i
    The simThreshold or dissimilarityThreshold can be used to specify the similarity or dissimilarity threshold respectively. Their default value is 0.7 and 0.3 respectively.
  • r: superstructure search (isin()). Deprecation: this value has been deprecated in favor of u
    
  
  Examples:
  1. To have the same behaviour as jc_equals:
     

     jc_compare(smiles, '<query>', 't:d');

  2. To have the same behaviour as jc_contains:
     

     jc_compare(smiles, '<query>', 't:s');

  3. To have the same behaviour as jc_tanimoto(smiles, '<query>') > 0.9 :
     

     jc_compare(smiles, '<query>', 't:t simThreshold:0.9');

  4. The following statement will return the ids of all structures which are duplicates of either Brc1ccccc1C=C or Cc1cccc(Br)c1C or Cc1ccccc1Br.

     SELECT id FROM mystructures where jc_compare(smiles, 'Brc1ccccc1C=C
Cc1cccc(Br)c1C
Cc1ccccc1Br', 't:d');

  5. The following statement will return 1:
     

     select jc_compare('c1ccccc1', 'Brc1ccccc1', 't:r') from dual;

  6. The following statement will return 0:
     

     select jc_compare('Clc1ccccc1', 'c1ccccc1', 't:r') from dual;



• haltOnError:y/n

  Specifies whether the search is (a) aborted on error (y) or (b) continues searching on error finding and returning as many hits as possible (n). The default value is y except for searches in Markush JCB tables or Markush indexes. With haltOnError:n, the errors which occurred during the corresponding search can be queried using (a combination of) the scanctx_cache and error_cache packages.

• maxHitCount:<max-hit-count>

  Limits the number of structures returned by this operator to the first <max-hit-count> structures found.
  
  Example:

  To return only 50 structures containing benzene(s):

  jc_compare(smiles, 'c1ccccc1', 't:s maxHitCount:50');



• maxTime:<max-time>

  Limits the time of the search. <max-time> is to be specified in milliseconds. Search will be stopped after the specified time has elapsed and the hits found up to this time are returned. The default value for this option is "unlimited time".
  
  Example:

  The following statement will return within approximately 15 seconds as many hits as it found during this time:

  SELECT count(*) FROM nci_12n WHERE jc_compare(smiles, 'c1ccccc1', 't:p maxTime:15000') = 1;



• simThreshold:<threshold>
  

  where <threshold> specifies that structures should be returned whose similiraty to the the query is greater then the value indicated.

  Example:

  1. The following SQL query returns the number of structures in nci_250k, whose similarity with Brc1ccccc1 is greater then 0.9:
     

     SELECT count(*) FROM nci_250k WHERE jc_compare(smiles, 'Brc1ccccc1', 't:t simThreshold:0.9') = 1;

  2. The following SQL query returns the number of structures in nci_250k, whose ECFP-based similarity with Brc1ccccc1 is greater then 0.9:
     

     SELECT count(*) FROM nci_250k WHERE jc_compare(smiles, 'Brc1ccccc1', 't:t descriptorName:ECFP simThreshold:0.9') = 1;

     See the description of the addDfltMd option and the the related example for information on how to generate ECFP type fingerprints for a given structure column.


• dissimilarityThreshold:<threshold>
  

  where <threshold> specifies that structures should be returned whose dissimiliraty to the the query is greater then the value indicated.



• dissimilarityMetric:<metric>
  Specifies the metric similarity search. The values accepted as <metric> depend on the structure type.
  
  1. For anyStructures and molecules, <metric> may be one of the following:
     • tanimoto
     • tversky
     • euclidean
     • ask for others
  2. For reactions <metric> may be one of the following:
     • ReactantTanimoto
     • ProductTanimoto
     • CoarseReactionTanimoto
     • MediumReactionTanimoto (default)
     • FineReactionTanimoto

  Example:

  select count(*) from nci_1k where jc_compare(structure, 'C[C@H](CS)C(=O)N1CCC[C@H]1C(O)=O |r|',
  		't:t dissimilarityThreshold:0.61 dissimilarityMetric:tversky;0.3;0.7') = 1



• descriptorName:<descriptor-name>
  Specifies the descriptor which should be used for the similiarity search. Please, see the addMd ALTER INDEX parameter for details on how to create molecular descriptors
  

  Example:

  The following SQL query returns the number of structures in nci_250k, whose ECFP-based similarity with Brc1ccccc1 is greater then 0.9:
  

  SELECT count(*) FROM nci_250k WHERE jc_compare(smiles, 'Brc1ccccc1', 't:t descriptorName:ECFP simThreshold:0.9') = 1;

  See the description of the addDfltMd option and the the related example for information on how to generate ECFP type fingerprints for a given structure column.


• screenConfig:<screening-configuration-locator>
  Specifies an alternative screening configuration which should be used for the similiarity search. Specifying the base MD configuration through descriptorName is required for this option. <screening-configuration-locator> can be either
  1. the name of a configuration that has been added using the addMdConf ALTER INDEX parameter or
  2. a select SQL statement which returns the configuration XML itself.
  For examples, please see the Generic Molecular Descriptor support in JChem Cartridge
  


• absoluteStereo:<abs-stereo-flag>

  where <abs-stereo-flag> may be one of the following:

  • a: All chiral atoms are absolute;
  • c: consider chiral flag;
  • t: (default) do as configured for the table in case of JChem tables or as configured for the index in case of non-JChem tables.

  Example:

  SELECT count(*) FROM nci_150k WHERE jc_compare(structure, 'Brc1ccccc1', 't:s absoluteStereo:c') = 1;



• exactQueryAtomMatching:y/n
  Exact query atom matching(y) or not(n). Default is n.

  Example:

  SELECT count(*) FROM nci_150k WHERE jc_compare(structure, 'Brc1ccccc1', 't:s exactQueryAtomMatching:y') = 1;



• radical:d/e/i
  Radical matching mode.
  Where
  • d: default
    
  • e: exact
    
  • i: ignore
    
  
  Example:

  SELECT count(*) FROM nci_150k WHERE jc_compare(structure, 'Brc1ccccc1', 't:s radical:i') = 1;



• isotope:d/e/i
  Isotope matching mode
  Where
  • d: default
    
  • e: exact
    
  • i: ignore
    
  
  

  Example:

  SELECT count(*) FROM nci_150k WHERE jc_compare(structure, 'Brc1ccccc1', 't:s isotope:i') = 1;



• charge:d/e/i
  Charge matching mode
  Where
  • d: default
    
  • e: exact
    
  • i: ignore (forces implicitHMatching:i in case of duplicate search)
    
  
  

  Example:

  SELECT count(*) FROM nci_150k WHERE jc_compare(structure, 'Brc1ccccc1', 't:s charge:e') = 1;



• doubleBondStereo:N/M/A
  Double bond stereo Matching mode:None/Marked/All Default is M.

  Example:

  SELECT count(*) FROM nci_150k WHERE jc_compare(structure, 'Brc1ccccc1', 't:s doubleBondStereo:A') = 1;



• vagueBond:n/1/2/3/4
  Vague handling of bond types:
  • n: off;
  • 1 (default): handling of certain 5-membered ambiguous aromatic rings, like [C,N]1C=CC=C1;
  • 2: all ring single and double bonds match aromatic
  • 3: all single and double bonds match aromatic
  • 4: ignore all bond types.


• HCountMatching:<hcount-matching> hydrogen count query property interpretation,
  where <hcount-matching> may be one of the following:
  • G (greater or equal, mdl behaviour) target atom must have H-s greater or equal to query H-s, in excess of explicit H-s. H0 means no extra H other than explicitly drawn.
  • E (equal, daylight behaviour) target atom mus have H-s equal to H count number.
  • A automatically determine whether G or E should be used, from the query source. (smiles and smarts source: E, all other: G).

  Example:

  SELECT count(*) FROM nci_150k WHERE jc_compare(structure, 'Brc1ccccc1', 't:s HCountMatching:A') = 1;



• implicitHMatching:<implicit-hmatching>
  Describes the matching of implicit and explicit hydrogens.
  where <implicit-hmatching> may be one of the following:
  • d default: its value is y in almost every cases. There is only one exception: its value is n in case of duplicate search against a query table in a database.
  • y Implicit and explicit hydrogens can match.
  • n Implicit and explicit hydrogens cannot match.
  • i Implicit and explicit hydrogens are ignored.



• exactStereoMatching:<exact-stereo-matching>
  Specifies whether stereo information should match exactly - looking for query stereo properties in target. Hydrogen count query property interpretation.
  <exact-stereo-matching> may be one of the following:
  • Y: stereo information must match exactly. For example "cis or unspecified" matches only "cis or unspecified", but matches neither "cis", "trans" or "unspecified". The same is true for chirality.
  • N (default)

  Example:

  SELECT count(*) FROM nci_150k WHERE jc_compare(structure, 'Brc1ccccc1', 't:s exactStereoMatching:y') = 1;

• stereoSearchType:s/i/e/d/a;
  • s (default): Stereo information is considered during searching
  • i: Stereo information is not considered during searching
  • e: Equality is needed in stereochemistry. ("All stereo info is exactly the same.") It mainly has an effect when the query has no stereo information: it only matches non-stereo target. Similarly, a query with a wiggly tetrahedral center will only match wiggly tetrahedral center, and not specific R and S configurations.
  • d: The diastereomers targets of a given query structure are also matched. This means that it is only required that if a query atom has stereo information, the target atom should have as well but the two configurations are treated the same.
  • a: The enantiomer targets of a given query structure are also matched.
  See also the Stereochemistry section in the JChem Query Guide.


• stereoModel:l/g/c;
  • l: local stereo model.
  • g: global stereo model.
  • c: comprehensive stereo model.
  See also the StereoModel documentation.


• ignoreTetrahedralStereo:n/y
  Option for ignoring tetrahedral stereo during searching. y - yes ignore, n - no, consider it. (default: n).
  See also the Stereo documentation.


• ignoreDoubleBondStereo:n/y
  Option for ignoring double-bond stereo during searching. y - yes ignore, n - no, consider it. (default: n).
  See also the Stereo documentation.


• ignoreAlleneStereo:n/y
  Option for ignoring allene stereo during searching. y - yes ignore, n - no, consider it. (default: y).
  See also the Stereo documentation.


• ignoreAxialStereo:n/y
  Option for ignoring axial stereo during searching. y - yes ignore, n - no, consider it. (default: y).
  See also the Stereo documentation.


• ignoreSynAntiStereo:n/y
  Option for ignoring syn-anti stereo during searching. y - yes ignore, n - no, consider it. (default: y).
  See also the Stereo documentation.


• ignoreCTExceptions:n/y
  Specifies whether to ignore exceptions during evaluation of expressions (default: n). Problematical molecules are left out from hits. Syntactical errors of CT expression cannot be ignored!
  


• mix:d/i
  Specifies whether COM, MIX and FOR sgroups should be considered during search. The default value is d for "do consider". i means ignore these sgroups.

  Example:

  SELECT count(*) FROM nci_150k WHERE jc_compare(structure, 'Brc1ccccc1', 't:s mix:i') = 1;



• ignoreMixtureBrackets:<ignoreMixtureBrackets> Deprecated in favor of mix.
  Specifies, whether to ignore mixture brackets.
  <ignoreMixtureBrackets> may be one of the following:
  • N(default): Do not ignore mixture brackets.
  • Y: Ignore mixture brackets.

  Example:

  SELECT count(*) FROM nci_150k WHERE jc_compare(structure, 'Brc1ccccc1', 't:s ignoreMixtureBrackets:n') = 1;



• polymer:d/i
  Handling of polymer brackets: d-default, i-ignore.


• endGroupMatching:y/n
  Polymer end groups must match: y-yes, n-no (default: yes)


• phaseShift:y/n
  The phase-shifted polymers must match on each other: y-yes, n-no (default: yes)


• transformMonomer:y/n
  Polymer in their source based representation are transformed to structure based : y-yes, n-no (default: yes)


• copolymerMatching:y/n
  Adjusts copolymer matching mode. If y, then target polymers in copolymers can only be matched by query polymers in copolymers: y-yes, n-no (default: no)


• tautomerSearch:<tautomer-search-mode>
  Specifies tautomer search mode. See the JChem Query Guide.
  <tautomer-search-mode> may be one of the following:
  • d (the default) behaviour is defined by the search context: tautomer search is turned on for duplicate searches in a tautomer duplicate database table; it is turned off in all other contexts.
  • y tautomer search is turned on.
  • n tautomer search is turned off.


• tdf:y/n
  Specifies turning on or off tautomer duplicate filtering as a search option. (See the appropriate section in the Developers Guide.) To be used together with duplicate search (t:d). For jchem index operations, tdf:y option has to be set for the jchem index or the underlying JChem table.
  If y, generic tautomer of query and target used in search.


• ctFilter: <chemical-terms-expression>.
  The chemical terms expression(s) will be used as a filter condition for the search. The expression used here should be a boolean expression that is always evaluated to true or false. The molecule context enables implicit reference to the target molecule. Some general examples and working examples are available, but remember that only the boolean expressions can be used as filters, others are useful in case of the jc_evaluate operator. You can use the Chemical Terms Reference Tables as a short language reference on the available functions and plugins. This option is available only when the jc_compare operator is evaluated as part of a domain index scan. In other cases you should use the jc_evaluate operator.

  Examples:

  1. The following SQL query returns the number of the structures that contain at least one benzene ring and have logP values greater than 9:

     SELECT count(*) FROM nci_10m WHERE jc_compare(structure, 'c1ccccc1', 'sep=! t:s!ctFilter:logp()>9') = 1

  2. Lipinski's rule of 5s:

     SELECT count(*) FROM nci_3m WHERE jc_compare(structure, 'C[C@H](CS)C(=O)N1CCC[C@H]1C(O)=O','sep=! t:s!
ctFilter:(mass() <= 500) && (logP() <= 5) && (donorCount() <= 5) && (acceptorCount() <= 10)') = 1

  3. Another SQL query of interest:

     SELECT count(*) FROM nci_10m WHERE jc_compare(structure, 'C[C@H](CS)C(=O)N1CCC[C@H]1C(O)=O','sep=! t:s!
ctFilter:(PSA() <= 200) && (rotatableBondCount() <= 10) && (mass() <= 500) && (aromaticRingCount() <= 4) ') = 1

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

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

• filterQuery:<select-statement>. The query in <select-statement> will be first executed, then the search will be performed only on the rows returned by the <select-statement>. <select-statement> must return a single column containing the identifier of the rows to be included in the search. The row identifier is cd_id in case of JChem tables and rowid in case of plain Oracle tables.

  This option is available only when the jc_compare operator is evaluated as part of a domain index scan. (When the operator is not evaluated as part of a domain index scan, the execution engine is feeding to it one single target at time. The targets are pipelined from the result of other steps/conditions of the SQL query making it meaningless to use a filter query (producing typically more than one single target) as yet another source of target(s) to operate on.)

  Examples:
  1. The following SQL statement performs a search on a subset of the structures stored in the JChem-generated table jc_nci_10m and returns the number of structures in the subset which contain aromatic ring. The subset considered for the search is specified to belong to project #502:

     SELECT count(*) FROM jc_nci_10m WHERE jc_compare(structure, 'c1ccccc1', 'sep=! t:s!filterQuery:select cd_id from jc_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 nci_10m and returns the number of structures in the subset which contain aromatic ring. 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!filterQuery:select rowid from nci_10m where projid = 502') = 1

• earlyResults:<block-size>. Specifies the size of blocks in which hits will be returned. The smaller the block size, the sooner the first hits will be returned, but the larger the communication overhead per hits between Oracle and the JChemServer. A block size of 0 disables the feature of incremental hit return, meaning that all hits will be returned in one single block.

  Examples:

  1. The following query will return hits in blocks of 2000 at a time as soon as they are found.

     SELECT count(*) FROM jc_nci_10m WHERE jc_compare(structure, 'c1ccccc1', 't:s earlyResults:2000') = 1

  2. The following query will wait until all hits are available and return them in one single block:

     SELECT count(*) FROM jc_nci_10m WHERE jc_compare(structure, 'c1ccccc1', 't:s earlyResults:0') = 1

• checkSpHyb:[y|n]
  Turns on or off sp-hybridization state check.

  Example
  

          SELECT count(*) FROM sphyb WHERE pkovacs_main.jc_compare(structure, '
    Marvin  06240817132D
  
    7  7  0  0  0  0            999 V2000
     -2.2393   -0.4125    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0
     -2.9538   -0.8250    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0
     -2.9538   -1.6500    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0
     -2.2393   -2.0625    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0
     -1.5248   -1.6500    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0
     -1.5248   -0.8250    0.0000 C   0  0  0  0  0  0  0  0  0  0  0  0
     -2.3277    0.4420    0.0000 O   0  0  0  0  0  0  0  0  0  0  0  0
    1  2  1  0  0  0  0
    1  6  1  0  0  0  0
    2  3  1  0  0  0  0
    3  4  1  0  0  0  0
    4  5  1  0  0  0  0
    5  6  1  0  0  0  0
    7  1  2  0  0  0  0
  M  END', 't:s checkSpHyb:y vagueBond:4') = 1
          

• undefinedRAtom:[g|gh|ghe|a|u]
  Describes the matching of undefined R-groups. See details.
  • g (default): undefined R-atom matches a group of one or more connected atoms in target, including at least one heavy atom
  • gh: undefined R-atom matches a group of one or more connected atoms in target, which can also be a single H atom
  • ghe: undefined R-atom matches a group of one or more connected atoms in target, which can also be a single H atom or the empty set (empty set match is allowed for isolated or one-attachment R-atoms only)
  • a: undefined R-atom matches any single atom in target
  • u: undefined R-atom matches only an undefined R atom in target
  If the exactQueryAtomMatching option is turned on, the value of this option is ignored and R1 only matches R1, but not R2.
• RLigandEqualityCheck:[y|n]
  If y then R-atoms with the same ID (e.g. two R1 atoms) should match ligands with the same structure. (Default: y.)
• bridgingRAllowed:[n|y]
  If y then different R-atoms are allowed to match the same group of target atoms. (Default: n.)
• attachedDataMatch:[i|g|e]
  Describes the matching behaviour of attached data. See details.
  • i (default): ignores attached data
  • g: general match
  • e: exact match
• attachedDataPrefixes:<prefix-list>
  Comma separated list of prefixes of attached data names that will be matched. See details.
• requireCommit:[y|n]
  Can be used with duplicate search (t:d) to specify whether a commit is required for recent modifications (structure INSERTs or UPDATEs) to be included in the search. With requireCommit:n, the search will be performed in the current transaction. By default, duplicate search requires committing modifications for them to be included. See the database access modes in JChem Cartridge.) Searching with requireCommit:n may be slower than with requireCommit:y.
• exactSpecialAtomMatching: <comma-separated-list-of-identifiers>
  Exact matching of enlisted special atoms is required (e.g. X cannot match to Br, only to X). Identifiers can be generic query atoms (A, AH, Q, QH, ...) or aliases of pseudo atoms.
• resultTimeout:<timeout-value>
  Specifies in seconds, how long the search results should be kept in JChem Server's memory after they last have been accessed. This value overrides the value of the scanresult.timeout global property in the jchem/conf/jcart.properties file. The resultTimeout option and the scanresult.timeout property may be useful in the rare cases, when a regular cancellation of the operation cannot be completed due to some exceptional circumstance, such as the database client being abruptly shut down or crashing. In such cases, setting a timeout makes sure that the memory and other resources associated with one particular search operation are not held indefinitly. The default value of both the option and the property is 0, which effectively disables watching the idle time of the results.
• threadCnt:<thread-count< Specifies the number of threads search should be executed on (in parallel) in domain index scan mode.
• usrOpId:<user-assigned-operation-id< A name identifying uniquely this search operation. It can be selected by the user at their discretion.

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

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, options IN VARCHAR2) = 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_tversky

jc_tversky (target_structure IN VARCHAR2, query_structure IN VARCHAR2, targetWeight IN NUMBER, queryWeight IN NUMBER) = 0 .. 1;

Performs similarity search using the Tversky 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, option IN VARCHAR2) = 0 .. 1;

Performs similarity search using comparing fingerprints of the target_structure and the query_structure.The third argument is an ( option list ). The options listed for jc_compare are applicable with the exception of the t (search type) option.

The Tanimoto formula is used by default as the metric. Other metrics supported can be specified through the dissimilarityMetric option.

By default, the 2D chemical hashed fingerprints are used which are automatically available with jc_idxtype indexes. Different fingerprints can be generated through the addMd ALTER INDEX parameter and can be selected for use with descriptorName option. For example, you can use ECFP fingerprints this way.

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

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.

• tblSchema and tblName together specify the structure table containing the target; if tblSchema is NULL, it is assumed that the table denoted by tblName is in the current user's schema;
• colName speficies the structure column in the table;
• query is the query structure to use as the "mold" for aligning and/or coloring the target;
• rowids is a comma separated list of the ROWIDs of the targets that needs to be colored and/or aligned; if query cannot be found in a target denoted by an element in rowids nothing will be returned for that element;
• options the search options to apply when looking for matches in the targets; this parameter has the same syntax as the third parameter of jc_compare;
• hitColorAndAlignOptions a list of options defining how the coloring and/or aligning should be performed; it accepts the following elemnts:
  
  • alignmentMode:{off|rotate|partialClean}
  • coloring:{y|n}
  • hitColor:<color-value>
  • nonHitColor:<color-value>
  • nonHitColor3D:<color-value>
  • removeUnusedDef:{y|n}
  where <color-value> can be:
  1. Hexa RGB: #RRGGBB; example: "#00FF00" means full green
  2. Java awt.Color constant names; examples: "red", "green", "blue"

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:na' option to obtain a fast estimation of substructure search hit count.

• table_schema the owner of the structure table in which to search
• table_name the name of the structure table in which to search
• column_name the column of the structure table in which to search (must be index with jc_idxtype)
• query_structure the query stucture to search for
• options the same search options as can be used with jc_compare

Example:

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

jcf.formula_search

jcf.formula_search(target VARCHAR2, query VARCHAR2, options VARCHAR2) = NUMBER

• t:<formula-search-type> Sets the formula search type. <formula-search-type> may be one of the following:
  • exact, if the target needs to match the query exactly for all atoms and cannot contain other atoms.
  • exact_sub, if tte target needs to match the query exactly for all atoms but can contain unspecified atoms as well.
  • sub, if the target needs to contain at least the specified number of atoms given in the query.
  Returns 1, if target formula matches the query formula, 0 otherwise.

  For more details on this option please see Sophisticated Chemical Formula Search.

  Example:

  select jcf.formula_search('C11H15NO', 'C11H15NO', 't:exact') 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.

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 pK_a 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;

11. The following SQL query checks the structure for errors, according to the configuration:

    select jc_evaluate('C[C](C)(C)(C)c1ccocc1', 'isValid("aromaticity..valence..queryAtom..queryBond")') from dual

    Returns 0 indicating that the structure is not valid. Please, see the example for the check() function for obtaining information on the invalidity of the structure.

    Please, see the isValid Evaluator functions for creating configurations.

12. The following SQL query checks the structure for errors, according to the configuration:

    select jc_evaluate('CCCC', 'isValid("aromaticity..valence..queryAtom..queryBond")') from dual

    Returns 1 indicating that the structure is valid.

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:

• chemTerms:<expression>
• outFormat:<output-format>
• haltOnError:y|n

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.

The haltOnError:y/n option specifies the behaviour in case an error occurs: The default behaviour (y) is to abort the jc_evaluate_x_haltOnError_option call with an error message. When n is specified the call returns null and the error which occurred is made available through the scanctx_cache and error_cache packages.

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
   

4. The following SQL query checks the structure for errors, according to the configuration, returns the result (passed/failed) along with (in the case of the structure is invalid) a short explanation why the structure is invalid:

   select jc_evaluate_x('C[C](C)(C)(C)c1ccocc1', 'chemTerms:check("aromaticity..valence..queryAtom..queryBond")') from dual

   Returns:

       failed, Aromaticity Error Checker: 6 wrong aromatic bonds found, Valence Error Checker: 1 atom with valence problem found

   Please, see the isValid Evaluator functions for creating configurations.

5. The following SQL queries check the structure for errors, according to the configuration, and then fix the errors:

   1. select jc_evaluate_x('[H]C1=C([H])C([H])=C([3H])C([2H])=C1[1H]',
          'chemTerms:fix("explicitH->removeExplicitH..valence->fixValence..bondAngle->clean..atomMap..abbrevGroup->contractGroup")')
          from dual
                  

      returns:

      c1ccccc1

   2. select jc_evaluate_x('[CH3:1][C:3]([OH:2])=[O:4]',
          'chemTerms:fix("explicitH->removeExplicitH..valence->fixValence..bondAngle->clean..atomMap..abbrevGroup->contractGroup")')
          from dual
                  

      returns:

      CC(O)=O

   Please, see the isValid Evaluator functions for creating configurations.

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 (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'))

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

• a dot separated list of SMILES or
• a single non-SMILES molecular structure

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

• a list of molecular structures to be transformed in the form of

      reactant1.reactant2...reactantn
      

  where reactant1, reactant1...reactantn are single fragment structures in SMILES format or
• a single non-SMILES molecular structure.

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

• method:n/f/a where
  • n (default): (for "normal" method) the reactants will be processed in the order specified by the argument "reaction";
  • f: (for "fuse reactants" method) all reactants are fused to one multi-fragment reactant molecule before the reaction. Any of the specified reactants can act as any reactant of the reaction scheme regardless of their order in the argument. (Example)
  • a: (for "all match" method) like f, but functional group matches will be combined into one single product set. (Example)
  
  
• standardizer:<config-method>:<standardizer-config>

  specifies (directly or indirectly -- depending on the value of <config-method>) the standardization actions which the reactant(s) should undergo before reaction.

  <config-method> is one of config or sql. The meaning of <standardizer-config> depends on <config-method>.

  • If <config-method> is config, <standardizer-config> is the Standardizer configuration itself. In this case, <standardizer-config> can be any valid Standardizer configuration either in XML form or in the simplified action string form.
  • If <config-method> is sql, <standardizer-config> must be a SQL SELECT statement returning a Standardizer configuration. The value of the first column (can be either VARCHAR2 or BLOB) of the first row returned by <select-statement> will be taken as the Standardizer configuration itself.

  See a sample use of this option here

• productStandardizer:<config-method>:<standardizer-config>

  specifies (directly or indirectly -- depending on the value of <config-method>) the standardization actions which the product(s) should undergo after reaction.

  The syntax of this option is identical to the standardizer option.

• reverse:<yes-or-no>

  specifies that the reverse of the reaction parameter should be performed (the two sides of the reaction are swapped: products are taken as reactants and reactants are taken as products).

  <yes-or-no> can be any of the following:

  • y: perform the reverse reaction
  • n (default): perform the straigth reaction

  See a sample use of this option here

• unambiguousReactionsOnly:<yes-or-no>

  specifies that the resulting product list is accepted only if there is only one possible product list. If there are more than one product lists then no result will be returned.

  <yes-or-no> can be any of the following:

  • y: No products for ambiguous reactions
  • n (default): Return products even for ambiguous reactions.

• ignoreRules:r/s/t/rs/rt

  specifies that the reaction rules listed after the colon must be ignored.

  • r: Ignore reactivity rules
  • s: Ignore selectivity rules
  • t: Ignore selectivity tolerance
  • rs: Ignore reactivity and selectivity rules
  • rt: Ignore reactivity rules and selectivity tolerance

• allowDuplProdLists:<yes-or-no>

  specifies that any given product list should be returned as many times is it is found in the results. The default is to filter duplicate products out.

  <yes-or-no> can be any of the following:

  • y: Do not filter out multiple product lists.
  • n (default): Return one product list only once.

• removeDuplProdRefs:<yes-or-no>

  specifies that any given product list should be returned as many times is it is found in the results. The default is to filter duplicate products out.

  <yes-or-no> can be any of the following:

  • y: Do not filter out multiple product lists.
  • n (default): Return one product list only once.

  See a sample use of this option here

• outFormat:<output-format>

  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 <output-format> is not specified, the product structures will be returned in SMILES with the products being separated with a dot('.') in the returned list. If the output format is specified, the products will be separated by a new line character. (So if you specify smiles, the products will be returned as SMILES and dots will appear only in the output to separate fragments within products.)

• extract:<i1,i2,...>

  Return only the specified products: i1,i2,... is the comma-separated list of 1-based product indexes according to reaction equation.

• ignoreErrors:<yes-or-no>

  specifies that errors (such as errors during import of the reactants or reaction(s)) should be ignored. Most useful when jc_react performs multiple reactions (such as when the reactants are specified as SELECT SQL statements)

  <yes-or-no> can be any of the following:

  • y: Ignore errors and continue processing.
  • n (default): Abort processing upon the first error encountered.

• outputType:products/reaction/fusedReaction

  specifies whether the results are to be output as an array of products or as an array of reaction molecules.

  The value of this option can be one of the following:

  • products (defalt) to output the results as an array of products
  • reaction to output the results as an array of reaction molecules.
  • fusedReaction to output the results as reactants -> products reaction, with all possible products on the right side.

• outputReactionMappingStyle:<mapping-style>

  specifies how the output reaction should be mapped.

  <mapping-style> can be any of the following:

  • none: Do not map the resulting reaction;
  • changing: changing atoms are mapped;
  • matching: matching atoms are mapped;
  • complete: all atoms are mapped.

• ratio:<ratio>

  where <ratio> is a semi-colon-separated list of reactant ratio. The length of the ratio array should be equal with the number of reactants in the reaction. If there are more than one reactants in the reaction, then one of the reactant ratios should be 1.

• Options for product ID generation

  • reactionId:<reaction-id>

    <reaction-id> will be directly used to generate the product identifiers.

  • reactionIdTag:<reaction-id-tag>

    <reaction-id-tag> is the name of the tag in the RDF data file whose value will be used to generate the product identifiers.

    Only one of the reactionId or the reactionIdTag can be specified, not both.

  • reactantIds:<reactant-ids>

    <reactant-ids> is a comma-separated list of reactant identifiers that will be directly used to generate the product identifiers.

    Note that if any of the reactants are specified using a SELECT SQL statement and the statement returns two columns, the second column is assumed to be the id for the reactant overriding the reactant id option.

  • reactantIdTags:<reactant-id-tags>

    <reactant-id-tags> is a comma-separated list of the SDF tags whose values will be used to generate the product identifiers.

    Note that if any of the reactants are specified using a SELECT SQL statement and the statement returns two columns, the second column is assumed to be the id for the reactant overriding the reactant id tags option(s).

  • productIdTags:<product-id-tags>

    <product-id-tags> is a comma-separated list of the SDF tags which will be included in the product data files to hold the generated product identifiers.

    The product IDs are determined using the following pattern:

            reaction ID: R1
            first reactant ID: A1
            second reactant ID: B1
    
            ID of the first product: R1(A1, B1):1
            ID of the second product: R1(A1, B1):2
    
            React the first product with C8 by reaction R2:
            ID of the first product R2(R1(A1, B1):1, C8):1
            

    Please, note that in order to use the product ID generation feature of jc_react, the output format of the product structures should be such as to allow the inclusion of user defined tags/fields (typically SDF or MRV). Since the default output format of the product structures is SMILES, you have to specify the appropriate format using the outFormat option.

    For an example of the product ID generation feature, please see the example for the jc_reactb2 operator.

• Options for inserting results directly into a database table

  • productTable:<product-table-name>

    If specified, the results are inserted into <product-table-name> and nothing is returned.

  • productTableJcpt:<jchem-property-table-name>

    If specified, the table given as the value of productTable option is a JChem table and its jchemproperties table is <jchem-property-table-name>.

  • productColName:<product-column-name>

    If specified, the table given as the value of productTable option is a regular table (non-JChem table) and the product will be inserted into the <product-column-name> column of this regular table.

  • productIdColName:<product-id-column-name>

    If specified, the id for each each product will be inserted into the column <product-id-colunm-name> This column must be in the same table where the product itself is inserted. If products are inserted into a non-JChem table's BLOB column, this option must always be specified and the product id column must be unique.

        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_insert(product, product_table_name, null, 'false',
                        'false', 'userDefColMap:PRID=prod_id');
            dbms_lob.freetemporary(product);
        end;
        

    CREATE TYPE CHAR_PRODUCT_RECORD AS OBJECT (
        product VARCHAR(32767),
        synthesis_code VARCHAR(32767)
    );

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
)

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(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")

jcf.exec_v(options IN VARCHAR2, execPath IN VARCHAR2, param1 IN VARCHAR2, param2, IN VARCHAR2) RETURN VARCHAR2

Executes a program file that the host operating system can (natively) execute.

1. options: currently not used, may be NULL;
2. execPath: the absolute path to the executable program file;
3. param1: the first argument to the program; must be NULL, if not used (e.g. the program doesn't take any arguments);
4. param2: the second argument to the program; must be NULL, if not used (e.g. the program doesn't take a second argument).

In the case of the successful completion, the standard output of the program will be returned. In the case of an error, an exception is raised with the following message: ORA-29532: Java call terminated by uncaught Java exception: java.lang.RuntimeException: <program-error-message>, where <program-error-message> is the character string output by the program to its standard error stream.

jchem_misc_pkg.cache_stmts(yesno IN NUMBER) Turns on or off caching SQL statements in Java Stored Procedures. Default is on. The value 0 for yesno turn off caching, any other value turns it on.

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:

• ctColCfg:<chemterm-column-config>
  where <chemterm-column-config> is a semicolon separated list of pairs of column names and Chemical Terms expressions. Each pair specifies a Chemical Terms calculated column: that the values of the given column should be automatically calculated using the given Chemical Terms expression. In each pair, the column name and the Chemical Terms expression is separated with an equal sign ('=').


• tableType:<table-type>
  where <table-type> can be one of
  
  • anyStructures: All types of structures are allowed, but no structure type-specific searching.
  • molecules: Specific structures, like single molecules, mixtures, salts, polymers. Other structures (e.g. with query or Markush features or reactions) are not allowed in database (exception is thrown).
  • reactions: Table for storing single step reactions.

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

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.

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

jchem_table_pkg.list_arr(jcproptable varchar2) return char_array

Returns a list of JChem tables registered with jcproptable.

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 := NULL, haltOnDuplicate IN VARHCAR2 := NULL, options IN VARHCAR2 := NULL) = 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. If set to null, the duplicateFilter table option is checked and honored.

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

• flags:<flags> Currently the 'c' flag is supported. Specify the 'c' flag if you want the chiral flag to be set for MDL file formats.
• haltOnBadFormat:<yes-or-no> This options can be used only when the first parameter to jc_insert is a SELECT statement. <yes-or-no> can be any of the following:
  • y (default): abort with an error message when a structure with invalid format is encountered.
  • n: discard structures with invalid format and continue inserting.
• userDefColMap:<tags-to-columns-map> This options instructs jc_insert to store SDF tagged values in user defined columns of the target JChem table according to <tags-to-columns-map>. <tags-to-columns-map> is a semi-colon-separated list of mappping elements which take the following form: <sdf-tag-name>=<column-name>.

Examples:

1. The following code inserts bromobenzene into the JCB table called SSS_TEST:

   declare
       a cd_id_array;
   begin
       a := jchem.jchem_table_pkg.jc_insert('Brc1ccccc1','sss_test');
   end;
   /

2. The following code inserts bromobenzene into the JCB table called SSS_TEST and sets the column called SAMPLE_CODE:

   declare
       a cd_id_array;
   begin
       a := jchem.jchem_table_pkg.jc_insert('Brc1ccccc1','sss_test');
       execute immediate 'update sss_test set sample_code = :scode where cd_id = :cdid' using 'BI00869034', a(1);
   end;
   /

3. 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.

   declare
       a cd_id_array;
   begin
       a := jchem_table_pkg.jc_insert('SELECT struct FROM nci_import',
                           'my_structure_table', null, null, 'false', 'flags:c
                           haltOnBadFormat:n');
   end;
   /

4. 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:

   declare
       a cd_id_array;
   begin
       a := jchem_table_pkg.jc_insert('SELECT struct FROM nci_import', 'my_structure_table',
                   null, null, 'false', 'flags:c haltOnBadFormat:n
                   userDefColMap:NSC=ns_code;CAS_RN=cas_regno;SMILES=smiles;HASH=molhash');
   end;
   /

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 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.

• java_class_name:
  name of the user provided Java class.
• separator:
  separator string to separate parameters in param_list
• param_list:
  parameters list separated by the string defined in separator

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.

jcf.insertable

jcf.insertable(structure IN VARCHAR2/CLOB/BLOB, schemaname IN VARCHAR2, tablename IN VARCHAR2, columnname IN VARCHAR2) return NUMBER

Checks whether a structure is consistent with the structuretype parameter of an index. Returns 1 if the structure is insertable into the given column, 0 otherwise.

• structure: the structure to be checked
• schemaname, tablename, columnname: the name of the schema, table and column to be checked, respectively. If the schema parameter is NULL, then the current user''s schema will be used.