Daylight Chemistry Cartridge Worksheet

The Daylight Chemistry Cartridge provides chemical intelligence to an Oracle server. The interface is PL/SQL and the cartridge includes both functions as well as indexes. Two different interfaces are given for this lab. The first is sqlplus which is an Oracle application which allows pl/sql commands. The second is a JDBC interface which uses a Java Servlet. NOTE: The JDBC interface does not require a ";" at the end of the SQL statements . The Oracle cartridge has already been installed and tables created for you. This exercise is one to "try out" its usage rather than its installation and administration.

Decide which interface you would like to use to access the Oracle Cartrdige and follow the connection instructions

• SQLPLUS
  • telnet sun1, login mug/coffee
  • sqlplus mug/coffee
• JDBC

Oracle Searching Functions

1. Lookup a Dopamine (NCCc1ccc(O)c(O)c1) in the Oracle table "small"
   
   The function utilized for an exact match search is:
   
   ddpackage.fexact (a IN VARCHAR2, b IN VARCHAR2) => NUMBER
   operator exact (a IN VARCHAR2, b IN VARCHAR2) => NUMBER
   
   The SQL command is:
   Select * from small where exact (smiles, 'NCCc1ccc(O)c(O)c1') = 1;
   
   
2. Do a similarity search for Dopamine imposing a tanimoto/euclidean filter of 0.7
   
   The function utilized for a tanimoto search is:
   
   ddpackage.ftanimoto (fp_or_smi1 IN VARCHAR2, fp_or_smi2 IN VARCHAR2) => NUMBER
   operator tanimoto (fp_or_smi1 IN VARCHAR2, fp_or_smi2 IN VARCHAR2) => NUMBER
   
   
   The SQL command is:
   
   Select ID,SMILES,tanimoto (smiles, 'NCCc1ccc(O)c(O)c1') from small where tanimoto (smiles, 'NCCc1ccc(O)c(O)c1') > 0.7;
   
   
   The function utilized for a euclidean search is:
   
   ddpackage.feuclid (fp_or_smi1 IN VARCHAR2, fp_or_smi2 IN VARCHAR2) => NUMBER
   operator euclid (fp_or_smi1 IN VARCHAR2, fp_or_smi2 IN VARCHAR2) => NUMBER
   
   
   The SQL command is:
   Select * from small where euclid (smiles, 'NCCc1ccc(O)c(O)c1') < 0.3;
   
   
3. Count the number of compounds in the small table which have phenol as a substructure
   
   
   The function utilized for a substructre search is:
   
   function ddpackage.fcontains (smiles1 IN VARCHAR2, smiles2 IN VARCHAR2) => NUMBER
   operator contains (smiles1 IN VARCHAR2, smiles2 IN VARCHAR2) => NUMBER
   
   
   The SQL command is:
   
   Select count(1) from small where contains(smiles,'c1ccccc1O') = 1;
   
   
4. Perform a SMARTS query on the small table
   
   The function utilized for a SMARTS query is:
   
   function ddpackage.fmatches (smiles IN VARCHAR2, smarts IN VARCHAR2) => NUMBER
   operator matches (smiles IN VARCHAR2, smarts IN VARCHAR2) => NUMBER
   
   
   The SQL command is:
   Select smiles from small where matches(smiles,'[N,O]') = 1;
   
   
   
5. Find all the reactions from the reaction table "rxn" which used phenol as a reagent. Do the same except look for phenol as a product.
   
   The function utilized for a reactant search is:
   
   function ddpackage.freactant (smiles1 IN VARCHAR2, smiles2 IN VARCHAR2) => NUMBER
   operator reactant (smiles1 IN VARCHAR2, smiles2 IN VARCHAR2) => NUMBER
   
   
   The SQL command is: Select * from rxn where reactant(smiles, 'Oc1ccccc1') = 1;
   

Oracle Molecule/Reaction Functions

1. Find all compounds in the small table which have a molecular weight greater than that of dopamine.
   
   The function utilized to calculate a molecular weight is:
   
   function ddpackage.fsmi2amw (smiles IN VARCHAR2) => NUMBER
   operator smi2amw (smiles IN VARCHAR2) => NUMBER
   
   
   The SQL command is:
   Select smiles from small where smi2amw(smiles) > smi2amw ('NCCc1ccc(O)c(O)c1');
   
   
   
2. Find all compounds who share the same molecular formula with Dopamine.
   
   
   The function utilized to calculate a molecular formula is:
   
   function ddpackage.fsmi2mf (smiles IN VARCHAR2) => VARCHAR2
   operator smi2mf (smiles IN VARCHAR2) => VARCHAR2
   
   
   The SQL command is:
   
   Select smiles from small where smi2mf(smiles) = 'C8H14NO2';

   Oracle Cartridge Administration - For Advanced users only who want to play some more

   1. Create your own table and try out the administration features of the cartridge, like indexing, etc.
      

      The SQL command to generate a new table is:
      

      Create table tablename as
      select * from small where rownum < 1000;