jc_contains

jc_contains (target_structureIN VARCHAR2, query_structureIN VARCHAR2) = 1/0;

The operator is deprecated for

jc_compare (target_structure IN VARCHAR2, query_structure IN VARCHAR2, 't:s' ).

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:

jc_matchcount

jc_matchcount( target_structureIN VARCHAR2/CLOB/BLOB, query_structureIN VARCHAR2/CLOB/BLOB) = 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.

This operator is currently available with the following signatures:

    target_str  query_str
    ---------------------
    VARCHAR2,   VARCHAR2
    CLOB,       CLOB
    BLOB,       BLOB

jc_equals

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

The operator is deprecated for

jc_compare (target_structure IN VARCHAR2, query_structure IN VARCHAR2, 't:d').

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_structureIN VARCHAR2/CLOB/BLOB, query_structureIN 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_str  query_str   options
    -----------------------------------
    VARCHAR2,   VARCHAR2,   VARCHAR2
    CLOB,       CLOB,       VARCHAR2
    CLOB,       VARCHAR2,   VARCHAR2
    CLOB,       BLOB,       VARCHAR2
    BLOB,       BLOB,       VARCHAR2
    BLOB,       VARCHAR2,   VARCHAR2

(The anonymous block hidden in the following HTML/XML comment can be used to generate the available signatures.)

NOTE: In case of some search options (e.g. substructure search) if the query structure is given in SMILES format it will be converted to SMARTS. See more here.
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 and the Query Guide.)

• t (search type)

• haltOnError

• maxHitCount

• maxTime

• totalSearchTimeoutLimitMilliseconds

• simThreshold

• dissimilarityMetric

• descriptorName

• screenConfig

• absoluteStereo

• exactQueryAtomMatching

• radical

• isotope

• charge

• valence

• doubleBondStereo

• vagueBond

• HCountMatching

• implicitHMatching

• exactStereoMatching

• stereoSearchType

• stereoModel

• ignoreTetrahedralStereo

• ignoreDoubleBondStereo

• ignoreAlleneStereo

• ignoreCumuleneOrRingCisTransStereo

• ignoreAxialStereo

• ignoreSynAntiStereo

• ignoreCTExceptions

• mix

• polymer

• endGroupMatching

• phaseShift

• transformMonomer

• copolymerMatching

• tautomerSearch

• tdf

• checkSpHyb

• undefinedRAtom

• RLigandEqualityCheck

• bridgingRAllowed

• attachedDataMatch

• attachedDataPrefixes

• ctFilter

• filterQuery

• earlyResults

• requireCommit

• exactSpecialAtomMatching

• resultTimeout

• threadCnt

• completeHG

• exactBondMatching

• exhaustiveModeLimit

• homologyBroadTranslation

• homologyNarrowTranslation

• reactionUnpairedMap

• ssrType

• usrOpId

• indexCost

• funcCost 

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

  • s: substructure search (default). Performs substructure search on molecules or reactions.

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

• 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 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:i 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') = 1;

5. The following statement will return 1:

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

6. The following statement will return 0:

   select jc_compare('Clc1ccccc1', 'c1ccccc1', 't:u') 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". From version 15.6.22, use totalSearchTimeoutLimitMilliseconds instead of maxTime.
    
    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:d maxTime:15000') = 1;

  • totalSearchTimeoutLimitMilliseconds :<max-time>
    
    Avaliable since version 15.6.22.
    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. There is no timeout limit, by default.
    
    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:d totalSearchTimeoutLimitMilliseconds: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:i 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:i 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 dissimilarity 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. 1. 1. • For anyStructures and molecules, <metric> may be one of the following:

   • • • • tanimoto

         • tversky

         • euclidean

         • ask for others

   1. 1. • 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:i 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:i descriptorName:ECFP simThreshold:0.9') = 1;

  See the description of the addDfltMd option and 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;

  • valence:d/i
    Valence matching mode
    Where

    • d: default

    • i: ignore

    Example:
    SELECT count(*) FROM nci_150k WHERE jc_compare(structure, '[#8;v3]', 't:s valence:i') = 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/h/1/2/3/4
    Vague handling of bond types:

    • n: off

    • h: handling of certain 5-membered ambiguous aromatic rings like [C,N]1C=CC=C1 (default from version 15.9.14)

    • 1: handling of certain 5-membered ambiguous aromatic rings like [C,N]1C=CC=C1 and 1-atom-long aromatic ring ligands and bridging bonds between two aromatic rings (default in versions prior to 15.9.14)

    • 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.
    <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 in all search types except in duplicate search): Stereospecific; only the specified stereo information of the query is considered during searching; unspecified query matches to specified and non-specified targets as well

    • 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. (default in duplicate search)

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

  • ignoreCumuleneOrRingCisTranstereo:n/y
    Option for ignoring cumulene or ring cis/trans 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.

    • is tautomer search is turned on with ignore stereo information in tautomer regions.

    • n tautomer search is turned off.

  • tdf:y/n
    Deprecation: this search option has been deprecated in favor of tautomerSearch

  • 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 9SELECT 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 purchase separate 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.
    Deprecation: filterQuery is deprecated. Use standard SQL tools to select the rows to be included in the search.
    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.

  • completeHG:[y|n] Sets if only complete structures should match on homology groups. See details.

  • exactBondMatching:[y|n] Specifies whether bond types should match exactly. See details .

  • exhaustiveModeLimit:<limit> Upon reaching this number of steps, the search switches to exhaustive mode from fast mode. See details.

  • homologyBroadTranslation:[n|a|m] Handling of target side homology groups is controlled by the homology broad translation option. See details.

    • a: all query atoms can match on broader homology atoms, if the properties of the group are fulfilled.

    • m: Marked. Only the marked query atoms can match on broader homology groups. This option is not yet implemented it works as none.

    • n (default): None. No query atoms can match on broader groups, they can only match if the query atom is a homology atom of the same group

• • homologyNarrowTranslation:[n|a|m] Handling of query side homology groups is controlled by the homology narrow translation option. The homology narrow translation option can switch off special handling of homology groups, hence they can match the homology group of the same type only (e.g. alkyl on alkyl or chk). See details.

    • a: All. All atoms can have translation.

    • m: Marked. Only marked atoms can have translation. (Not implemented yet.)

    • n (default): None. No atom can have translation.

  • reactionUnpairedMap:[all/unpairedOnly] Specifies option for matching unpaired (orphan or widow) atom maps to any atom. See details .

  • ssrType:[s/c] Describes the set of smallest rings to use for atom property calculations.

    • s: Smallest set of smallest rings(SSSR), may vary depending on atom orders.

    • c: Complete set of smallest rings(CSSR)

  • 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. 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 #502SELECT 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:i!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_structureIN VARCHAR2, query_structureIN BLOB, options IN VARCHAR2) = 1/0;

Like jc_compare but with BLOB as the query structure argument.

jc_tanimoto

jc_tanimoto (target_structureIN VARCHAR2/CLOB/BLOB, query_structureIN VARCHAR2/CLOB/BLOB) = 0 .. 1;

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

(See also how to use ECFP fingerprints.)

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

This operator is currently available with the following target-query combinations:

    target_str  query_str
    ---------------------
    VARCHAR2,   VARCHAR2
    CLOB,       CLOB
    BLOB,       BLOB

jc_tversky

jc_tversky (target_structureIN VARCHAR2/CLOB/BLOB, query_structureIN VARCHAR2/CLOB/BLOB, targetWeight IN NUMBER, queryWeight IN NUMBER) = 0 .. 1;

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

(See also how to use ECFP fingerprints.)

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

This operator is currently available with the following target-query combinations:

    target_str  query_str
    ---------------------
    VARCHAR2,   VARCHAR2
    CLOB,       CLOB
    BLOB,       BLOB

jc_dissimilarity

jc_dissimilarity (target_structureIN VARCHAR2/CLOB/BLOB, query_structure IN VARCHAR2/CLOB/BLOB, option IN VARCHAR2) = 0 .. 1;

Performs similarity search using comparing fingerprints of the target_structure and the query_structure.

The type of the target_structure and the query_structure can be either VARCHAR2 or CLOB or BLOB. 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 operator is available in 2 parameter mode too, or the option parameter can be set to null with the same result. The default parameters described below.

This operator is currently available with the following signatures:

    target_str  query_str   options
    -----------------------------------
    VARCHAR2,   VARCHAR2,   VARCHAR2
    CLOB,       CLOB,       VARCHAR2
    BLOB,       BLOB,       VARCHAR2
    VARCHAR2,   VARCHAR2
    CLOB,       CLOB
    BLOB,       BLOB

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.

Note: In order to speed up descriptor-based similarity search, take into account the possibility of descriptor caching.

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.

• 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 indexed 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; in the case of JChem tables cd_id list should be used in place of the list of ROWIDs;

• 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 the elements shown on this page.

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

where target and query are formulae such as C2H6 and option is a list of options currently accepting the following option:

• 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/CLOB/BLOB, expression IN VARCHAR2, options 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.

Marvin Services are available in JChem Cartridge through this operator (and the equivalent functions in the JCF package). Marvin Services can be globally configured by setting the marvin.services.config.file property to the path to the corresponding configuration file (See here .)

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 following options can be specified:

• haltOnError:y|n

The haltOnError:y/n option specifies the behaviour in case an error occurs: The default behaviour (y) is to abort the jc_evaluate_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.

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 purchase separate Calculator Plugins license to use this operator depending on the Chemical Terms used (logp, logd, ...)

Examples:

The following statement returns the logP value of the specified structure
select jc_evaluate(<input-molecule>, 'logp()') from dual;

1. The logD value at pH=7.4 of the input molecule:
   select jc_evaluate(<input-molecule>, 'logd("7.4")') from dual;

2. Check the difference between logD values at two different pH-s:
   select jc_evaluate(<input-molecule>, 'logd("7.4") - logd("3.8")') from dual;

3. The strongest acidic pKa value of the input molecule:
   select jc_evaluate(<input-molecule>, 'pka("acidic", "1")') from dual;

4. The second strongest basic pK _a value of the input molecule:
   select jc_evaluate(<input-molecule>, 'pka("basic", "2")') from dual;

5. The topological polar surface area of the input molecule:
   select jc_evaluate(<input-molecule>, 'psa()') from dual;

6. The number of H bond acceptor atoms in the input molecule:
   select jc_evaluate(<input-molecule>, 'acceptorCount()') from dual;

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

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

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

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

11. The following SQL query checks the structure for errors, according to the configuration:
    select jc_evaluate('CCCC', 'isValid("aromaticity..valence..queryAtom..queryBond")') from dualReturns 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_evaluate_x (target_structureIN VARCHAR2/CLOB/BLOB, options IN VARCHAR2) RETURN 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

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

Calculator Plugins license may be required depending on the Chemical Terms used.

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:

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

• 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

• 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

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

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

Calculator Plugins license may be required depending on the Chemical Terms used.

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

Calculator Plugins license may be required depending on the Chemical Terms used.

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)

Calculator Plugins license may be required depending on the Chemical Terms used.

jc_molweight

jc_molweight (target_structureIN VARCHAR2/CLOB/BLOB) = NUMBER;

Returns the molecular weight of the target_structure.

jc_formula

jc_formula (target_structureIN VARCHAR2/CLOB/BLOB) RETURN 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_structureIN VARCHAR2/CLOB/BLOB, 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) RETURN 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

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

• 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: is deleted; use jc_standardize instead. (for "all match" method) like f, but functional group matches will be combined into one single product set.

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

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

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

4. 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*',
                       'C[C@H](CS)C(=O)N1CCC[C@H]1C(O)=O',
                       'reverse:y outputType:reaction') FROM DUAL

5. 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/CLOB/BLOB, reactant1 IN VARCHAR2/CLOB/BLOB, reactant2 IN VARCHAR2/CLOB/BLOB, reactant3 IN VARCHAR2/CLOB/BLOB, reactant4 IN VARCHAR2/CLOB/BLOB, options IN VARCHAR2) RETURN 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. The reactants must have the same Oracle type as the reaction. Reactor license is required.

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 jcprods:

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

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

The operator is deprecated for jc_react.

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

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

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, optionsIN VARCHAR2) = BLOB;

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

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_insert 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_insert(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. Reactor license is required.

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/CLOB/BLOB, param IN VARCHAR2) RETURN VARCHAR2/CLOB/BLOB;

Transforms structure based on the rules defined in param. The RETURN datatype is the same as the IN datatype.

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

• config:<standardizer-configuration-string>

  where <standardizer-configuration> is the Standardizer configuration.

  <standardizer-configuration-string> can be any valid Standardizer configuration either in XML form or in the simplified action string form.

• sql:<select-config-statement>

  where <select-config-statement> is 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 <standardizer-configuration-string>.

• cleaningTemplate:<select-template-statement where <select-template-statement> is a SQL SELECT statement returning a the template for template based cleaning. Please, see the following link for more details:

  • description

• outFormat:<output-format>

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

• onError: halt/null/inputStructure

  specifies the behaviour in case an error occurs: The default behaviour, halt is to abort the call with an error message. When null is specified the call returns null, if inputStructure is specified then the call returns the input structure.

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_compare(cd_smiles, :query, 't:s') = 1'
           into hitcount using stdized_query;

2. Apply standardization stored in the "first" row of the stdconfig table.

   select jc_standardize(cd_smiles, 'sep=~ sql:select config from stdconfig where id = 1')
           from jc_nci_1k where cd_id < 10

3. Clean the structure C[C@H](CS)C(=O)N1CCC[C@H]1C(O)=O based on the template stored in the column structure in the first row of the table templa

   select jcf.standardize('C[C@H](CS)C(=O)N1CCC[C@H]1C(O)=O',
           'sep=~ config:clean:tb~cleaningTemplate:select structure from templa~outFormat:mol') from dual

4. For cleaning structures based on more templates, create a standardizer action string in Standardizer GUI, then apply the action string in the jc_standardize statement:

   select jc_standardize('C1CCCCC1',

   'sep=~ config:clean2d:ID=''Clean2D'':Type=''TemplateBased'':templates=''H4sIAAAAAAAAAL1XTW/jNhC991cQOtcyhx/DYWB7EexeCuyi7W4PvQWKrWQNxFIqO3Hy7/soxYkte2O7aAvHgTJ882Y4fDNURh+eFnfqsWyW87oaZ5TrTJXVtJ7Nq9txtp5Xs3q9HJDxOvswGU2BBb5ajrPvq9X9xXC4Xq/z6fdyUTzVVT6tF1m3fvG0nO9g1javm9uh0ZqGf375/K31Gcyr5aqopiW8lvOLZWv8XE+LVZvMD0IMF0XzOK+GHXy4aB47tityV2SuNOVPy1n2tqePWLyEr7qZ35Xqpm4WxUo9ksvJ5Jp+VrdlVTbFqpyp62fYfW7w0dnkp9GXT/X0YVFWq8noS2KZT4u7b6vmYQrDor4rpw8gxMMvn8bZgrLJqFjVi8umKZ5VekrmglRhVGFV4VThUdu7MhH+8XxfIjP18skUdvH7Q9k8/9bU96ju5YXa+kF5zDizOWthE7wPwTtPzAqZBhflzRQOmVwenTH8ZvIHTJl6bmN47ayxYq1jLLIDFITbJuVzoR2LP2Ta80N9hq8Fmoyu62q29ajms3F2TVlbua/lzdJ0xUNidTMrG2gzU3+lEnW1u6yeE2Fy3SYwuwTubAK7S5AO7jwCt0uQDv88Ar9XA3MKwXCroMONOvG4p9zhm6zPlLg5QeKq4B+pPOk8CXngcnFRR+tsoOjEODWAuF2wIdoI/YmJNpkcBGSDYYEp6qAGlEfyRAIYeWODVwOTk4/iLVOI3pIRNdA5UxSrA2utRbxxnbg1oNr66B1RCOgStmgXzWyEgCOQGC+KciITBI7RgdNFp8CofUSE4LygcVxCMQeOMXjh6JGogIs1uJzo6D2LxH8o+e3jPi5x2hXY2ZI+ScL+fYeeZDsRvOfAe2160OF/0LQ9TdOqCKoQVURVkMYX6wQAAUGAEDAEEAFFcqABfsXn4+t38/v16aUxOCftIVpNkLt1wmoQckiLOVrWZAj6O2hyeQhkreYg2niTugDtYwMzrBwhVE4thWbRNkaK0UcjAbs40aTBry1uDO9EtPPGH7a1eTh0gGMdcSP4lzxCkKDT1ULpamgjOPzNIUYmdJ7qto5VjXjBhCgv+xSHdjXGYHWz9SAc0LKC5jRqIAiJhgRKR4OODT2TOGc3/R+j1ha1w94CBohBY5v0SsKAsRUiYpOmjO2MmBwpwXYA7OMO0J3qijq5NJscihUIZ0TtBLQaFcFgIh9QvVQm3UWIFgeiYfG4tzUKJ5Yw8STNRM4DdX4UDShlg0rsmHaS/DoQmDCUxLFzGxDychGvd6zdJquUvTY4NNOeHpuYhinjlcLglLlNS7Zt/8KYO/9mp6NTzL57ER8fe/5ohP2xF87bU28M8i7BAYew50D6fQ/Z9Ugj7H2HuOsQeyFO2FXC92LSkdJTXx7tfD3i01eE6cU5JVV7IO6RU6eeTrr5f2bc/ktee38cidtTS3ffHPHpCYbkaBdQTzGbK+2/egMd4p/Kyd/hVkd1hw4AAA=='':format=''base64:gzip:mrv''~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.
Standardizer license is required.

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 IN VARCHAR2/BLOB/CLOB, options_outputformat IN VARCHAR2) RETURN VARCHAR2/BLOB/CLOB;

Converts a molecular structure into a given format. The Oracle type of the result is same as the type of the query_structure.

The second argument must be the identifier of the output format and options as specified in the online 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 different Oracle formats, the operators jc_molconvertv, jc_molconvertb or jc_molconvertc have to be used.

Returns the molecular structure in a given format.

Name to Structure or Structure to Name license may be required if name is input/output format.

A molconvert function with an extended argument list is available in the JCF package:

molconvert (query_structureIN VARCHAR2/BLOB/CLOB, input_format VARCHAR2, options_outputformat IN VARCHAR2, option_list VARCHAR2 := null) = VARCHAR2/BLOB/CLOB;

The additional second parameter in the middle (input_format) can be used to force the interpretation of the query_structure to be limited to a specific format. If it is null, there is no limitation on the allowed input formats. The third argument (option_list) accepts the following options:

• haltOnError:y/n

  The default value is y to propagate errors which occur during execution of this function. If set to n, NULL is returned on error and the error which occurred is made available through the scanctx_cache and error_cache packages.

• newLine:y/n

  Determines whether there are end of line characters at the end of the returned string. If set to y, the new line characters are trimmed at the end of the output. The default value is y for smiles and smarts types, n for the others.

jc_molconvertv

jc_molconvertv (query_structure IN VARCHAR2/BLOB/CLOB, options_outputformat IN VARCHAR2) RETURN 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 online 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 other formats, the operator jc_molconvertb or jc_molconvertc have to be used.

Returns the molecular structure in a given format.

Name to Structure or Structure to Name license may be required if name is input/output format.

A molconvertv function with an extended argument list is available in the JCF package:

molconvertv (query_structure IN VARCHAR2/BLOB/CLOB, input_format VARCHAR2, options_outputformat IN VARCHAR2, option_list VARCHAR2 := null) = VARCHAR2;

The additional second parameter in the middle (input_format) can be used to force the interpretation of the query_structure to be limited to a specific format. If it is null, there is no limitation on the allowed input formats. The third argument (option_list) accepts the following options:

• haltOnError:y/n

  The default value is y to propagate errors which occur during execution of this function. If set to n, NULL is returned on error and the error which occurred is made available through the scanctx_cache and error_cache packages.

• newLine:y/n

  Determines whether there are end of line characters at the end of the returned string. If set to y, the new line characters are trimmed at the end of the output. The default value is y for smiles and smarts types, n for the others.

jc_molconvertb

jc_molconvertb (query_structure IN VARCHAR2/BLOB/CLOB, 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 online 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_molconvertv 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.

Name to Structure or Structure to Name license may be required if name is input/output format.

The function equivalent of this operator has the following signature:

jcf.molconvertb (query_structure IN VARCHAR2/BLOB/CLOB, 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.

jc_molconvertbb

The operator is deprecated. Does the same as jc_molconvertb, but only works with BLOB type query parameter.

jc_molconvertc

jc_molconvertc (query_structure IN VARCHAR2/BLOB/CLOB,options_outputformat IN VARCHAR2) = CLOB;

Converts a molecular structure into a given format. Recommended for large structures that do not fit into 4000 characters (limitation of VARCHAR2).

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

Returns the molecular structure in a given format as a temprorary CLOB. The caller must make sure that the CLOB is freed after the underlying binary stream has been extracted. Unfreed temporary CLOBs 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.

Name to Structure or Structure to Name license may be required if name is input/output format.

The function equivalent of this operator has the following signature:

jcf.molconvertc ( query_structure IN VARCHAR2/BLOB/CLOB, options_outputformat IN VARCHAR2, temp_clob CLOB) = CLOB;

For the use of the temp_clob 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.

Calculator Plugins license may be required depending on the Chemical Terms used.

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.

Calculator Plugins license may be required depending on the Chemical Terms used.

Example:

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

jcf.exec_v

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

The executable program must indicate (according to widely used conventions) successful completion by exiting with a value of 0. Any other exit value, is interpreted to indicate that an error occurred during execution of the program.

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

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. Standardizer license is required in case of other than 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 ('=').

  Calculator Plugins license may be required depending on the Chemical Terms used.

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

  • queries: Only superstructure, full and duplicate searches are allowed.

  • markush: Structures with markush features. Markush search license is required.

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

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:

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

Function is deprecated in version 15.3.2. The use of Chemical Terms is recommended instead. See description of ctFilter .

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:

• 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

Function is deprecated in version 15.3.2. The use of Chemical Terms is recommended instead. See description of ctFilter .

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.

jchem_core_pkg.getenvironment

Returns information about the software environment such as Oracle version, JChem version and Java version.

jchem_util.readmol / readmolc

FUNCTION readmol(fileName IN VARCHAR2,inputFormat IN VARCHAR2, type IN VARCHAR2, options IN VARCHAR2) return VARCHAR2

FUNCTION readmolc(fileName IN VARCHAR2,inputFormat IN VARCHAR2, type IN VARCHAR2, options IN VARCHAR2) return CLOB

Reads a molecule from a file. Only fileName parameter is mandatory, the following parameters are optional and treated as null if not set.

• fileName: the input file's name. The user and the cartridge user both need to have read options to the file. To do it, use dbms_java.grant_permission Oracle command. Setting * or - in the file name make all files in the directory and the - flag makes all subdirectories readable. Please note that in order to use the grant the user has to reconnect.

• inputFormat: the input file's format. If the parameter is not set or null, then there is no limitation on the allowed input formats.

• type: the format of the returned molecule. If the parameter is not set or null, the molecule is read in the same format as in the input file.

• options: accepts the following options:

  • haltOnError:y/n

    The default value is y to propagate errors which occur during execution of this function. If set to n, NULL is returned on error and the error which occurred is made available through the scanctx_cache and error_cache packages.

Name to Structure or Structure to Name license may be required if name is input/output format.

jchem_util.writemol / writemolc

PROCEDURE writemol(fileName IN VARCHAR2,query IN VARCHAR2, inputFormat IN VARCHAR2, type IN VARCHAR2, options IN VARCHAR2)

PROCEDURE writemolc(fileName IN VARCHAR2,query IN CLOB, inputFormat IN VARCHAR2, type IN VARCHAR2, options IN VARCHAR2)

Writes a molecule to a file. Only fileName and query parameters are mandatory, the following parameters are optional and treated as null if not set.

• fileName: the output file's name. The user and the cartridge user both need to have write options to the file. To do it, use dbms_java.grant_permission Oracle command. Setting * or - in the file name make all files in the directory and the - flag makes all subdirectories writable. Please note that in order to use the grant the user has to reconnect.

• query: the molecule to be written.

• inputFormat: the molecule's format. If the parameter is not set or null, then there is no limitation on the allowed formats.

• type: the output file's format. If the parameter is not set or null, the molecule is written in the same format as the input.

• options: accepts the following options:

  • haltOnError:y/n

    The default value is y to propagate errors which occur during execution of this function. If set to n, the error which occurred is made available through the scanctx_cache and error_cache packages.

Name to Structure or Structure to Name license may be required if name is input/output format.

jc_fuse

jc_fuse (first_structure IN VARCHAR2/BLOB/CLOB, second_structure IN VARCHAR2/BLOB/CLOB) RETURN VARCHAR2/BLOB/CLOB;

Fuses two molecules into one. The two original molecules will be in different fragments. The Oracle type of the result is same as the type of the structures. The output format is the format of the first input parameter.

Name to Structure or Structure to Name license may be required if name is input/output format.

A fuse function with an extended argument list is available in the JCF package:

fuse(first_structure IN VARCHAR2/BLOB/CLOB, second_structure IN VARCHAR2/BLOB/CLOB, input_format VARCHAR2) RETURN VARCHAR2/BLOB/CLOB;

The additional third parameter in the middle (input_format) can be used to force the interpretation of the input structures to be limited to a specific format. If it is null, there is no limitation on the allowed input formats.