REM Copyright (c) 2003 Konrad Buessow

REM This program is free software; you can redistribute and/or modify 
REM it under the terms of the GNU General Public Licence as published by
REM the Free Software Foundation; eiher version 2, or (at your option) any
REM later version.

REM This program is distributed in the hope that it will be useful, but 
REM WITHOUT ANY WARRANTY; without even the implied warranty of 
REM MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
REM GNU General Public Licence for more details. 

REM You should have received a copy of the GNU General Public Licence 
REM along with this program; if not, write to the Free Software 
REM Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA

-- BEGIN PL/SQL BLOCK (do not remove this line) -------------------------------- 

CREATE SEQUENCE ORFER_SEQ INCREMENT BY 1 START WITH 1 
    MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE 
    CACHE 20 NOORDER
;
create SEQUENCE BIG_SEQ INCREMENT BY 1 START 
    WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE 
    NOCACHE NOORDER;
/
CREATE SEQUENCE PAGE_SEQ INCREMENT BY 1 START WITH 1 
    MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE 
    NOCACHE NOORDER
;
/
CREATE SEQUENCE EXPERIMENT_SEQ INCREMENT BY 1 START WITH 1 
    MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE 
    NOCACHE NOORDER
;
/

CREATE PACKAGE ORFER IS
  FUNCTION GET_NEW_ID RETURN NUMBER;
END ORFER
;
/
CREATE PACKAGE BODY ORFER IS
  FUNCTION GET_NEW_ID RETURN NUMBER IS
    NEW_ID NUMBER;
  BEGIN
    SELECT ORFER_SEQ.NEXTVAL INTO NEW_ID FROM DUAL;
    RETURN NEW_ID;
  END;
END ORFER
;
/
CREATE OR REPLACE  PACKAGE CLONES     IS
  
  
  FUNCTION INS_CLONEINDEX (
    indexclonename_in IN cloneindex.indexclonename%TYPE,
    rzpdclone_in IN cloneindex.rzpdclone%TYPE,
    ligclone_in IN cloneindex.ligclone%TYPE,
    skclone_in IN cloneindex.skclone%TYPE
  )
  RETURN 
    cloneindex.cloneid%TYPE;
  /* Inserts a record into the the CLONEINDEX table. Returns the generated Clone ID. */  
  
  FUNCTION rzpdclone_row (
    clonename_in IN clonesrzpd.clonename%TYPE)
  RETURN clonesrzpd.row_%TYPE;

  FUNCTION rzpdclone_column (
    clonename_in IN clonesrzpd.clonename%TYPE)
  RETURN clonesrzpd.column_%TYPE;

  FUNCTION rzpdclone_plate (
    clonename_in IN clonesrzpd.clonename%TYPE)
  RETURN clonesrzpd.plate%TYPE;
   
END clones;
/
CREATE OR REPLACE  PACKAGE BODY CLONES   IS

 
  FUNCTION rzpdclone_row (
    clonename_in IN clonesrzpd.clonename%TYPE)
  RETURN clonesrzpd.row_%TYPE IS BEGIN
    RETURN SUBSTR(clonename_in,9,1);
  END;
 
  FUNCTION rzpdclone_column (
    clonename_in IN clonesrzpd.clonename%TYPE)
  RETURN clonesrzpd.column_%TYPE IS BEGIN
    RETURN TO_NUMBER(SUBSTR(clonename_in,10,2));
  END; 
  
  FUNCTION rzpdclone_plate (
    clonename_in IN clonesrzpd.clonename%TYPE)
  RETURN clonesrzpd.plate%TYPE IS BEGIN
    RETURN TO_NUMBER(SUBSTR(clonename_in,12));
  END;
  
 
 
  FUNCTION INS_CLONEINDEX (
    indexclonename_in IN cloneindex.indexclonename%TYPE,
    rzpdclone_in IN cloneindex.rzpdclone%TYPE,
    ligclone_in IN cloneindex.ligclone%TYPE,
    skclone_in IN cloneindex.skclone%TYPE
  )
  RETURN 
    cloneindex.cloneid%TYPE
  IS
    new_cloneid cloneindex.cloneid%TYPE;
    no_insert EXCEPTION; 
    PRAGMA EXCEPTION_INIT(no_insert, -20001);
  BEGIN
    SELECT BIG_SEQ.NEXTVAL INTO new_cloneid FROM dual;
    INSERT INTO cloneindex (cloneid,indexclonename,rzpdclone,ligclone,skclone)
      VALUES (new_cloneid,indexclonename_in,rzpdclone_in,ligclone_in,skclone_in);
    RETURN new_cloneid; 
  EXCEPTION 
    WHEN no_insert THEN BEGIN 
      DBMS_OUTPUT.PUT_LINE('Insert Trigger TR_SEQ_309_1 raised an unforseable exception.');
      RETURN NULL;
    END;
    WHEN OTHERS THEN BEGIN
      DBMS_OUTPUT.PUT_LINE('Fehler in clones.ins_cloneindex.');
      RETURN NULL;
    END;      
  END;    
END clones;
/

CREATE OR REPLACE  FUNCTION GET_VEC_CLONELIG            
  (cloneid_in IN cloneligation.cloneid%TYPE)
  /* follow_insertclone: Soll bei einer Ligation, deren INsertclone ein Ligationclone
  ist, die Ligation dieses Clones gesucht werden:1 */
RETURN vectors.vectorid%TYPE
IS 
  return_vectorid vectors.vectorid%TYPE;
  test_vectorid vectors.vectorid%TYPE; /* zum Testen ob die Funktion was liefert */

  CURSOR traf_cur 
    (cloneid_curin IN cloneligation.cloneid%TYPE) 
  IS
    SELECT cl.trafo_id,t.ligation_id,t.plasmid_cloneid_source
      FROM cloneligation cl, transformations t
     WHERE cl.cloneid = cloneid_in
       AND t.trafo_id = cl.trafo_id;

  CURSOR lig_cur 
    (ligid_curin IN ligation.ligationid%TYPE) 
  IS 
    select l.insertcloneid, l.vectorid
      from ligation l 
     where l.pcrexpid is null 
       and l.ligationid = ligid_curin
  union
    select p.templatecloneid as insertcloneid, l.vectorid
      from ligation l, pcrexperiment p
     where l.pcrexpid = p.pcrexperimentid 
       and l.ligationid = ligid_curin;

  CURSOR oclone_cur 
    (cloneid_curin IN cloneindex.cloneid%TYPE)
  IS
    SELECT
      CR.CLONEID,
      LIB.VECTOR_ID AS VECTORID, 
      LIB.STRAIN_ID AS STRAINID, 
      LIB.HELPER_VEC_ID AS HELPID
    FROM CLONESRZPD CR, LIBRARIES LIB
    WHERE
      CR.LIBNO = LIB.LIBNO AND
      LIB.VECTOR_ID IS NOT NULL AND
      CR.CLONEID = cloneid_curin
    UNION
    SELECT
      CI.CLONEID,
      DUMMY AS STRAINID,
      IL.VECTOR_ID AS VECTORID,
      DUMMY AS HELPID
    FROM 
      CLONEIMAGE CI, 
      IMAGE_LIB IL
    WHERE 
      CI.RZPD_IMAGE_LIB = IL.ID_LIB AND
      CI.CLONEID = cloneid_curin
    UNION
    SELECT
      CO.CLONEID, 
      CO.VECTORID, 
      CO.HELPERPLASMIDID AS HELPID,
      CO.STRAINID
    FROM CLONEOTHER CO 
    WHERE
      CO.CLONEID = cloneid_curin;
  oclone_rec oclone_cur%ROWTYPE;
  traf_rec traf_cur%ROWTYPE;
  lig_rec lig_cur%ROWTYPE;
  ist_kein_ligclone EXCEPTION;
  weder_noch EXCEPTION;
  null_input EXCEPTION; 
  plasmid_source_not_identified EXCEPTION;
  found_data BOOLEAN;
BEGIN
    /* I expect that there is one entry for cloneid in cloneligation */
    IF cloneid_in IS NULL THEN
      RAISE null_input;
    END IF;
    OPEN traf_cur (cloneid_in);
    FETCH traf_cur INTO traf_rec;
    found_data := traf_cur%FOUND;
    CLOSE traf_cur;
    IF NOT found_data THEN
      RAISE ist_kein_ligclone; /*abbruch*/
    END IF;
    IF traf_rec.ligation_id IS NOT NULL THEN
      /* handelt es sich bei dem Template Klon um einen Ligation Clone:2
      Wenn ja, soll die Ligation dieses Klons herausgefunden werden. */
      OPEN lig_cur (traf_rec.ligation_id);
      FETCH lig_cur INTO lig_rec;
      CLOSE lig_cur;
      return_vectorid := lig_rec.vectorid;
    ELSIF traf_rec.plasmid_cloneid_source IS NOT NULL THEN
      test_vectorid := get_vec_clonelig(traf_rec.plasmid_cloneid_source); /* Rekursion! */
      IF test_vectorid IS NOT NULL THEN
        return_vectorid := test_vectorid;
      ELSE
        OPEN oclone_cur (traf_rec.plasmid_cloneid_source);
        FETCH oclone_cur INTO oclone_rec;
        found_data := oclone_cur%FOUND;
        CLOSE oclone_cur;
        IF NOT found_data THEN
          RAISE plasmid_source_not_identified;
        END IF;
        return_vectorid := oclone_rec.vectorid;
      END IF;
    ELSE
      RAISE weder_noch;
    END IF;
    RETURN return_vectorid;
EXCEPTION
  WHEN ist_kein_ligclone
  THEN
    BEGIN
      DBMS_OUTPUT.PUT_LINE ('ERROR: Der Klon mit Clone ID = ' || cloneid_in || ' ist nicht in CLONELIGATION!');
      RETURN NULL;
    END;
  WHEN weder_noch THEN
    BEGIN
      DBMS_OUTPUT.PUT_LINE ('weder_ligation_noch_plasmid_cloneid_source!');
      RETURN NULL;
    END;
  WHEN plasmid_source_not_identified THEN
    BEGIN
      DBMS_OUTPUT.PUT_LINE ('Plasmid_cloneid_source gefunden, aber dieser Klon ist kein Ligation Klon!');
      RETURN NULL;
    END;
  WHEN null_input THEN
    BEGIN
      DBMS_OUTPUT.PUT_LINE ('Input was NULL!');
      RETURN NULL;
    END;
END;
/
CREATE OR REPLACE FUNCTION GET_LIG_CLONLIG
/* This function returns information on clones in the table CLONELIGATION
   generated by a transformation. 
   A ligation ID is returned that can be used to retrieve information 
   on vector and cloning details or the insert sequence of the clone.
 
   Cells are transformed with DNA that comes from a ligation or simply 
   from a plasmid prep. In the latter case, a plasmid is transferred 
   from one host strain to the other. 
   The insert sequence of a clone can be inferred from the sequence specifity
   of PCR primers used during the cloning procedure. Therefore we try to go
   back in the making history of a clone to find a PCR experiment. More
   specifically, if a restriction fragment was ligated, we go back further
   in the database and take a look on the clone that the restriction fragment
   came from. However if we are interested in the vector of the clone, we have
   to stop when vector information is found. The goal of the function -
   vector or insert information - is controlled by the second parameter
   "follow_insertclone" in (0, 1). In most cases this paramter will not 
   make a difference. The parameter would make a difference, if a clone
   originated from a ligation of a restriction fragment from a clone that
   himself originated from a PCR. */
   
         
  (cloneid_in IN cloneligation.cloneid%TYPE, follow_insertclone IN INTEGER)
  /* follow_insertclone: Soll bei einer Ligation, deren INsertclone ein Ligationclone
  ist, die Ligation dieses Clones gesucht werden:1 */
RETURN transformations.ligation_id%TYPE
IS
  return_ligationid transformations.ligation_id%TYPE;
  test_ligationid transformations.ligation_id%TYPE; /* zum Testen ob die Funktion was liefert */
  CURSOR traf_cur 
  (cloneid_curin IN cloneligation.cloneid%TYPE) 
  IS
    SELECT cl.trafo_id,t.ligation_id,t.plasmid_cloneid_source
      FROM cloneligation cl, transformations t
     WHERE cl.cloneid = cloneid_in
       AND t.trafo_id = cl.trafo_id;
  CURSOR lig_cur 
  (ligid_curin IN ligation.ligationid%TYPE) 
  IS
    select l.insertcloneid 
      from ligation l 
     where l.pcrexpid is null 
       and l.ligationid = ligid_curin
  union
    select p.templatecloneid as insertcloneid
      from ligation l, pcrexperiment p
     where l.pcrexpid = p.pcrexperimentid 
       and l.ligationid = ligid_curin;
  traf_rec traf_cur%ROWTYPE;
  lig_rec lig_cur%ROWTYPE;
  ist_kein_ligclone EXCEPTION;
  weder_noch EXCEPTION;
  null_input EXCEPTION;
  plasmid_source_not_ligclone EXCEPTION;
  found_data BOOLEAN;
BEGIN
    /* I expect that there is one entry for cloneid in cloneligation */
    IF cloneid_in IS NULL THEN
      RAISE null_input;
    END IF;
    OPEN traf_cur (cloneid_in);
    FETCH traf_cur INTO traf_rec;
    found_data := traf_cur%FOUND;
    CLOSE traf_cur;
    IF NOT found_data THEN
      RAISE ist_kein_ligclone; /*abbruch*/
    END IF;
    IF traf_rec.ligation_id IS NOT NULL THEN
      /* handelt es sich bei dem Template Klon um einen Ligation Clone:2
      Wenn ja, soll die Ligation dieses Klons herausgefunden werden. */
      OPEN lig_cur (traf_rec.ligation_id);
      FETCH lig_cur INTO lig_rec;
      CLOSE lig_cur;
      IF lig_rec.insertcloneid IS NULL OR follow_insertclone = 0 THEN
        return_ligationid := traf_rec.ligation_id;
      ELSE
        /* mal schaun ob der insertclone ein ligationclone ist und eine Ligation-ID liefert */
        test_ligationid := get_lig_clonlig(lig_rec.insertcloneid,follow_insertclone); /* Rekursion! */
        IF test_ligationid IS NOT NULL THEN /* hat geklappt */
          return_ligationid := test_ligationid;
        ELSE /* Der Insertclone liefert keine weitere Information */
          return_ligationid := traf_rec.ligation_id;
        END IF;
      END IF;
    ELSIF traf_rec.plasmid_cloneid_source IS NOT NULL THEN
      test_ligationid := get_lig_clonlig(traf_rec.plasmid_cloneid_source,follow_insertclone); /* Rekursion! */
      IF test_ligationid IS NOT NULL THEN
        return_ligationid := test_ligationid;
      ELSE
        RAISE plasmid_source_not_ligclone;
      END IF;
    ELSE
      RAISE weder_noch;
    END IF;
    RETURN return_ligationid;
EXCEPTION
  WHEN ist_kein_ligclone
  THEN
    BEGIN
      DBMS_OUTPUT.PUT_LINE ('ERROR: Der Klon mit Clone ID = ' || cloneid_in || ' ist nicht in CLONELIGATION!');
      RETURN NULL;
    END;
  WHEN weder_noch THEN
    BEGIN
      DBMS_OUTPUT.PUT_LINE ('weder_ligation_noch_plasmid_cloneid_source!');
      RETURN NULL;
    END;
  WHEN plasmid_source_not_ligclone THEN
    BEGIN
      DBMS_OUTPUT.PUT_LINE ('Plasmid_cloneid_source gefunden, aber dieser Klon ist kein Ligation Klon!');
      RETURN NULL;
    END;
  WHEN null_input THEN
    BEGIN
      DBMS_OUTPUT.PUT_LINE ('Input was NULL!');
      RETURN NULL;
    END;
END; 
-- END PL/SQL BLOCK (do not remove this line) ---------------------------------- 
/ 
CREATE OR REPLACE  PACKAGE GET_NAME    is  

  function vector(vectorid_in in vectors.vectorid%type) return vectors.vectorname%type;
  function indexclone(cloneid_in in cloneindex.cloneid%type) return cloneindex.indexclonename%type;
  function primer(primerid_in in pcrprimer.id%type) return pcrprimer.primername%type;
  function experimentator_longname(experimentatorid_in in experimentator.id%type) return experimentator.LONGNAME%type;
end get_name; 
/
CREATE OR REPLACE  PACKAGE BODY GET_NAME     is 

  function vector(vectorid_in in vectors.vectorid%type) return vectors.vectorname%type is
    vectorname_out vectors.vectorname%type;
  begin
    select vectorname
      into vectorname_out 
      from vectors 
      where vectorid = vectorid_in;
    return vectorname_out;
  end;
   
  function indexclone(cloneid_in in cloneindex.cloneid%type) return cloneindex.indexclonename%type is
    indexclonename_out cloneindex.indexclonename%type;
  begin
    select indexclonename
      into indexclonename_out
      from cloneindex 
      where cloneid = cloneid_in;
    return indexclonename_out;
  end;
  
  function primer(primerid_in in pcrprimer.id%type) return pcrprimer.primername%type is
    primername_out pcrprimer.primername%type;
  begin
    select primername
      into primername_out
      from pcrprimer
      where id = primerid_in;
    return primername_out;
  end;
  
  function experimentator_longname(experimentatorid_in in experimentator.id%type) 
  return experimentator.LONGNAME%type 
  is
  longname_out experimentator.LONGNAME%type;
  begin
    select longname
    into longname_out
    from experimentator
    where id = experimentatorid_in;
    return longname_out;
  end;
end get_name; 
/
CREATE OR REPLACE PACKAGE PLABO is  
subtype varchar_item is varchar2(30);
function plabo_itemname(plabo_id_in in number) return varchar_item;
end plabo; 
/
CREATE OR REPLACE PACKAGE BODY PLABO is 
function plabo_itemname(plabo_id_in in number) return varchar_item
is
  CLONEID# platesboxes.CLONEID%type;
  PCRPRIMERID# platesboxes.PCRPRIMERID%type;
  PROTEIN_ID# platesboxes.PROTEIN_ID%type;
  LIGATIONID# platesboxes.LIGATIONID%type;
  DNAPREPID# platesboxes.DNAPREPID%type;
  PCRPRODUCTID# platesboxes.PCRPRODUCTID%type;
begin
  select CLONEID,PCRPRIMERID,PROTEIN_ID,LIGATIONID,DNAPREPID,PCRPRODUCTID
    into CLONEID#,PCRPRIMERID#,PROTEIN_ID#,LIGATIONID#,DNAPREPID#,PCRPRODUCTID#
    from platesboxes pb
    where pb.platesboxesid = plabo_id_in; 
  if CLONEID# is not null then
    return get_name.indexclone(cloneid#);
  elsif PCRPRIMERID# is not null then 
    return 'Primer ' || get_name.primer(PCRPRIMERID#);
  elsif PCRPRODUCTID# is not null then
    return 'PCR ' || to_char(PCRPRODUCTID#);
  elsif PROTEIN_ID# is not null then
    return 'Protein ' || to_char(PROTEIN_ID#);
  elsif LIGATIONID# is not null then
    return 'Ligation ' || to_char(LIGATIONID#);
  elsif DNAPREPID# is not null then
    return 'DNA prep ' || to_char(DNAPREPID#);
  end if;
  return 'Unknown';
end
;
end plabo;
/
CREATE OR REPLACE  PACKAGE SEQ_DNAPEP  
IS

  max_varchar BINARY_INTEGER := 32767;
  sali_pqe30nst NUMBER := 179;
  pqe30nst_seqdnaid NUMBER := 277760;

  FUNCTION fuse_nested_primers(
    inner_primer IN pcrprimer.primerseq%TYPE,outer_primer IN pcrprimer.primerseq%TYPE)
    RETURN VARCHAR2;
  FUNCTION predict_cloneorf_nestedpcr(ligid_in IN ligation.ligationid%TYPE) RETURN VARCHAR2;
  FUNCTION get_pepweight (seq IN CLOB) RETURN NUMBER;
  FUNCTION get_pepweight (seq IN VARCHAR2) RETURN NUMBER;
  /* Calculate Polypeptide (isotopic average) molecular weights
     from sequences. Masses are taken from the table AMINO_ACIDS
     and correspond to data from EMBOSS http://www.embos.org. */

  FUNCTION vector_orf_start(
    vectorid_in IN vectors.vectorid%TYPE, renzyme_in IN restrictionenzymes.enzymeid%TYPE
    ) RETURN VARCHAR2;
  FUNCTION vector_orf_end(
    vectorid_in IN vectors.vectorid%TYPE, renzyme_in IN restrictionenzymes.enzymeid%TYPE
    ) RETURN VARCHAR2;
  FUNCTION predict_cloneorf_insertclone(ligid_in ligation.ligationid%TYPE)
    RETURN VARCHAR2;
  FUNCTION predict_cloneorf(ligid_in ligation.ligationid%TYPE) 
    RETURN VARCHAR2;
 /* Calculate the expected ORF sequence of clones derived from a
    ligation.
    The Ligation is connected to the cloned sequence by the PCR primers.
    Three possibilities for the PCR:
      1) 5' primer = gene-specific primer, 3' primer = gene-specific primer
      2) 5' primer = gene-specific primer, 3' primer = vector primer
      3) 5' primer = vector, 3' primer = gene-specific primer  (not implemented yet!!)
    If the gene-specfic primers (PR-Primers) match to more than one protein/gene
    sequence, we got a problem. The proper sequence has to be inferred from
    an experimental clone sequence or from the size of the insert/expression product.

    Data needed:
    -PCRexpID in Ligation, endprimerid in PCRexperiments, primerseq in pcrprimer
    pcrprid, gbentryid, g_stopbp, pr_stopbp in pcrprot.
    -Sequence for the vector in Ligations
    -DNA (ORF) Sequence for the genbankentry in pcrprot.
    -re1vector, re2vector in ligation.

   example:
    set serveroutput on;
    declare
      s varchar2(32767);
    begin
      s := seq_dnapep.predict_cloneorf(1682);
    end;
*/
  FUNCTION enzymepos(seq_in IN VARCHAR2,
    enzymeid_in IN restrictionenzymes.enzymeid%TYPE)
    RETURN NUMBER;

  FUNCTION enzymepos(seqdnaid_in IN seq_dna.seqdna_id%TYPE,
                     enzymeid_in IN restrictionenzymes.enzymeid%TYPE)
    RETURN NUMBER;
  /* Returns the cleavage position of a restriction enzyme in a DNA sequence. This
     implementation does not understand restriction cleavage sites containing letters
     other than A,C,G,T. */


  FUNCTION stop_inframe (seq IN VARCHAR2, frame IN NUMBER /* 0,1,2 */) RETURN NUMBER;
  /* Returns the first base pair positition of a stop codon in a sequence in one
     of the three reading frames. The frame paramter means:
        0: start looking for stop codons in the codon from bp pos. 1 to 3
        1: start looking for stop codons in the codon from bp pos. 2 to 4
        2: start looking for stop codons in the codon from bp pos. 3 to 5
  */


  FUNCTION insert_pcrprot(
    pcrprid_in pcrprimer.id%TYPE, seqdnaid_in pcrprot.seqdna_id%TYPE)
    RETURN VARCHAR2;
  /* Insert into the table PCRPROT. The primer and the DNA sequence
     of the genbank entry are compared. The position of a restriction
     enzyme on the primer is also taken into account. The resulting
     postion values are written into the table PCRPROT.

     returns error message or NULL if everything is ok

     Example
        declare
          x number;
        begin
          x := seq_dnapep.insert_pcrprot(5,559);
        end;

  */


  FUNCTION primer_fragment(primerid_in pcrprimer.id%TYPE)
    RETURN  pcrprimer.primerseq%TYPE;
  /* Returns the sequence of the primer up to the restriction enzyme cleavage site. For
     lower strand primers, returns the reverse complement sequence up to the cleavage site on
     this sequence. Returns Null if no enzyme is entered in table PCRPRIMER. */



  /* Reverse and complement functions for DNA sequences. */
  FUNCTION reverse_seq(seq_in IN CLOB)
    RETURN CLOB;
  FUNCTION reverse_seq(seq_in IN VARCHAR2)
    RETURN VARCHAR2;
  FUNCTION complement_seq(seq_in IN CLOB)
    RETURN CLOB;
  FUNCTION complement_seq(seq_in IN VARCHAR2)
    RETURN VARCHAR2;
  /* These functions work for VARCHAR2 and CLOB arguments. */


  FUNCTION ins_seqdna(seq_in seq_dna.seq%TYPE)
    RETURN seq_dna.seqdna_id%TYPE;
  FUNCTION ins_seqpep(seq_in seq_pep.seq%TYPE)
    RETURN seq_pep.seqpep_id%TYPE;
  /* These functions insert sequences (CLOBs) into the tables
     SEQ_DNA or SEQ_PEP and return the assigned seqdna_id or
     seqpep_id, respectively. */


  FUNCTION atcg_count (seq_in IN CLOB, startpos_in IN NUMBER, length_in IN NUMBER)
    RETURN NUMBER;
  FUNCTION atcg_count (seq_in IN CLOB, startpos_in IN NUMBER)
    RETURN NUMBER;
  FUNCTION atcg_count (seq_in IN CLOB)
    RETURN NUMBER;
  /* Count the number of A,T,C and Gs in a sequence. */


  FUNCTION count_occurence(text VARCHAR2,searchtext VARCHAR2)
    RETURN NUMBER;
  /* Count the number of times that searchtext appears in text. */

  function ligclone_molweight(cloneid_in in cloneligation.cloneid%TYPE) return number;
END seq_dnapep;
/
CREATE OR REPLACE  PACKAGE BODY SEQ_DNAPEP            
    IS

/* assumes that the right end of outer primer overlaps the beginning of the innter primer seq.
   returns a new sequence from a fusion of the two primers.
*/
FUNCTION fuse_nested_primers(inner_primer IN pcrprimer.primerseq%TYPE,outer_primer IN pcrprimer.primerseq%TYPE)
  RETURN VARCHAR2 IS
  inner_seq VARCHAR2(200);
  outer_seq VARCHAR2(200);
  out_match_in NUMBER; 
  fusion VARCHAR2(200); 
  overlap NUMBER := 10;
  search_seq VARCHAR2(200);
BEGIN
  dbms_output.put_line('fuse_nested_primer inner:' || inner_primer || ' outer:' || outer_primer);
/*  SELECT primerseq INTO inner_seq FROM pcrprimer WHERE id = inner_primer;
  SELECT primerseq INTO outer_seq FROM pcrprimer WHERE id = outer_primer;*/
  inner_seq := inner_primer; outer_seq := outer_primer;
  out_match_in := INSTR(inner_seq,SUBSTR(outer_seq, -overlap, overlap), 1);
  IF out_match_in > 0 THEN
    fusion := CONCAT(outer_seq,SUBSTR(inner_seq,out_match_in + overlap));
    RETURN fusion;
  ELSE
    dbms_output.put_line('fuse_nested_primers: No overlap!');
    RETURN NULL;
  END IF;
END;

/* Predict ORF of a clone generated by nested PCR.
PCR 1 = inner, 1st PCR, Gene-specific primers
PCR 2 = outer, 2nd PCR, template_pcrid is the pcrexperimentid of PCR 1
*/
FUNCTION predict_cloneorf_nestedpcr(ligid_in IN ligation.ligationid%TYPE) RETURN VARCHAR2 IS
  CURSOR nest_cur (ligid_curin IN ligation.ligationid%TYPE) IS
    SELECT
      pcr2.*,
      l.vectorid,l.re1vector,l.re2vector,l.re1insert,l.re2insert,
      pri5.primerseq pri5seq, pri3.primerseq pri3seq
      FROM
        ligation l, pcrexperiment pcr1, pcrexperiment pcr2,
        pcrprimer pri5, pcrprimer pri3
      WHERE pcr2.template_pcrid = pcr1.pcrexperimentid
      AND pri5.id = pcr1.endprimerid5
      AND pri3.id = pcr1.endprimerid3
      AND l.pcrexpid = pcr2.pcrexperimentid
      AND l.ligationid = ligid_curin;
  nest_rec nest_cur%ROWTYPE;
  CURSOR inner_cur (pcrexpid_curin IN pcrexperiment.pcrexperimentid%TYPE) IS
    SELECT * FROM (
        SELECT p1.seqdna_id seqdna_id, pri1.id prid1, pri2.id prid2,
          pcr.pcrexperimentid pcrexpid, 0 l_vectorpri,
          p1.g_stopbp + 1 gb_von, p2.g_stopbp gb_bis,
          pri1.primerseq pri1seq, pri2.primerseq pri2seq
        FROM pcrprot p1, pcrprot p2, pcrprimer pri1, pcrprimer pri2, pcrexperiment pcr
        WHERE p1.pcrprid = pri1.id
        AND p2.pcrprid = pri2.id
        AND pri1.upperlowerstrand = 'U' AND pri2.upperlowerstrand = 'L'
        AND p1.seqdna_id = p2.seqdna_id
        AND pcr.endprimerid5 = pri1.id
        AND pcr.endprimerid3 = pri2.id
        UNION
        SELECT p1.seqdna_id seqdna_id, pri1.id prid1, pri2.id prid2,
          pcr.pcrexperimentid pcrexpid, 1 l_vectorpri,
          p1.g_stopbp + 1 gb_von, 0 gb_bis,
          pri1.primerseq pri1seq, pri2.primerseq pri2seq
        FROM pcrprot p1, pcrprimer pri1, pcrprimer pri2, pcrexperiment pcr
        WHERE p1.pcrprid = pri1.id
        AND pri2.vectorprimer <> 0
        AND pri1.upperlowerstrand = 'U'
        AND pcr.endprimerid5 = pri1.id
        AND pcr.endprimerid3 = pri2.id
        AND pcr.template_pcrid IS NULL
        ) pcr
    WHERE pcr.pcrexpid = pcrexpid_curin;
    inner_rec inner_cur%ROWTYPE;
    nest_exception EXCEPTION;
    inner_exception EXCEPTION;
    fusion_error EXCEPTION;
    vectorseq_error EXCEPTION;
    orf VARCHAR2(32767);
    gbseq CLOB;
    type_# genbankentries.type_%TYPE;
    cdsend# genbankentries.cdsend%TYPE;
    fused_primers VARCHAR2(1000);
    prifragment VARCHAR2(2000);
        stopinframe NUMBER;

BEGIN
  dbms_output.put_line('predict_cloneorf_nestedpcr');
  BEGIN
    OPEN nest_cur(ligid_in);
    FETCH nest_cur INTO nest_rec;
    IF nest_rec.template_pcrid IS NULL THEN
      RAISE nest_exception;
    END IF;
    CLOSE nest_cur;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN BEGIN
      CLOSE nest_cur;
      RAISE nest_exception;
    END;
  END;
  DBMS_OUTPUT.PUT_LINE('nest_cur OK. template_pcrid =' || nest_rec.template_pcrid);
  BEGIN
    OPEN inner_cur(nest_rec.template_pcrid);
    FETCH inner_cur INTO inner_rec;
    IF inner_rec.pcrexpid IS NULL THEN
      RAISE inner_exception;
    END IF;
    CLOSE inner_cur;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN BEGIN
      CLOSE inner_cur;
      RAISE inner_exception;
    END;
  END;
  DBMS_OUTPUT.PUT_LINE('inner_cur Read OK');
      orf := vector_orf_start(nest_rec.vectorid, nest_rec.re1vector);
      IF orf IS NULL THEN
        RAISE vectorseq_error;
      END IF;
      DBMS_OUTPUT.PUT_LINE('added start of ORF from vector. nest_rec.endprimerid5=' ||nest_rec.endprimerid5||'   orf =' || orf);
     prifragment := primer_fragment(nest_rec.endprimerid5) ;
     prifragment := fuse_nested_primers(inner_rec.pri1seq, prifragment);
     IF prifragment IS NULL THEN RAISE fusion_error; END IF;
     orf := orf || prifragment;
      DBMS_OUTPUT.PUT_LINE('added start of ORF from primer. orf =' || orf);
      /* select GenBank sequence info corresponding to primer */
      SELECT s.seq
      INTO gbseq
      FROM seq_dna s
      WHERE s.seqdna_id = inner_rec.seqdna_id;
      /* generate the ORF with two specific primers */
       IF inner_rec.l_vectorpri = 0 THEN
        orf := CONCAT (orf,
          DBMS_LOB.SUBSTR(gbseq,
          inner_rec.gb_bis - inner_rec.gb_von, inner_rec.gb_von));
         /* Hier muss ueberprueft werden, ob primer_fragment(primer_rec.prid2)
        ein stop-codon im Frame enthaelt. */
       prifragment := primer_fragment(nest_rec.endprimerid3);
       prifragment := fuse_nested_primers(
            inner_rec.pri2seq, complement_seq(reverse_seq(prifragment)));
       IF prifragment IS NULL THEN RAISE fusion_error; END IF;     
       prifragment :=  complement_seq(reverse_seq(prifragment));



        orf := CONCAT (orf, prifragment);
        DBMS_OUTPUT.PUT_LINE('primerfragment 2: ' || length(prifragment) ||
          ' bp, orf ' || length(orf) || ' bp'); 
        stopinframe := stop_inframe(orf, 0);
        IF stopinframe > 0 THEN
          DBMS_OUTPUT.PUT_LINE('stop codon in orf prifragment = ' ||
            prifragment || ' stopinframe = ' || stopinframe ||
            ', Length(orf) = ' || LENGTH(orf) ||
            ', MOD(3 - MOD(LENGTH(orf),3),3) = ' || MOD(3 - MOD(LENGTH(orf), 3), 3));
          orf := SUBSTR(orf, 1, stopinframe + 2);
     	  ELSE
          orf := CONCAT (orf, vector_orf_end(nest_rec.vectorid,nest_rec.re2vector));
          DBMS_OUTPUT.PUT_LINE('mit vector end: orf ' || length(orf) || ' bp'); 
        END IF;
      /* generate the ORF with one specific and one vector primer */
      ELSIF inner_rec.l_vectorpri = 1 THEN
        orf := CONCAT (orf, DBMS_LOB.SUBSTR(gbseq, 32767, inner_rec.gb_von));
        stopinframe := stop_inframe(orf, MOD(3 - MOD(LENGTH(orf), 3), 3));
        IF stopinframe > 0 THEN
          orf := SUBSTR(orf, 1, stopinframe + 2);
        ELSE
          orf := '';
          DBMS_OUTPUT.PUT_LINE('cannot predict ORF, because no stop codon found in template sequence.');    
        END IF;
      END IF;
      /* end, print some summary information */      
        DBMS_OUTPUT.PUT_LINE('orfstart = ' || substr(orf,1,60));
        DBMS_OUTPUT.PUT_LINE('orfmiddle = ' || substr(orf,650,100));
        DBMS_OUTPUT.PUT_LINE('Laenge = ' || length(orf) || ' primer_rec.gb_von = '
        || inner_rec.gb_von || ' primer_rec.seqdna_id = ' || inner_rec.seqdna_id);
        DBMS_OUTPUT.PUT_LINE('orfend = ' || substr(orf,-60));
  RETURN orf;
EXCEPTION
  WHEN nest_exception THEN BEGIN
    dbms_output.put_line('Not a nested PCR.');
    RETURN NULL;
  END;
  WHEN inner_exception THEN BEGIN
    dbms_output.put_line('Nested PCR found: not enough data to predict ORF.');
    RETURN NULL;
  END;
    WHEN vectorseq_error THEN BEGIN
      DBMS_OUTPUT.PUT_LINE(
        'Error in function predict_cloneorf_nestedpcr: cannot find sequence of vector. Vector ID =' ||
        nest_rec.vectorid);
      RETURN NULL;
    END;
    WHEN fusion_error THEN BEGIN
      DBMS_OUTPUT.PUT_LINE(
        'Error in function predict_cloneorf_nestedpcr: nested primers do not fit.');
      RETURN NULL;
    END;
END;

FUNCTION predict_cloneorf(ligid_in ligation.ligationid%TYPE)
  RETURN VARCHAR2 IS
    CURSOR primer_cur (ligid_curin ligation.ligationid%TYPE) IS
      SELECT pcr.*, l.vectorid, l.re1vector, l.re2vector FROM
        ligation l, (
        SELECT p1.seqdna_id seqdna_id, pri1.id prid1, pri2.id prid2,
          pcr.pcrexperimentid pcrexpid, 0 l_vectorpri,
          p1.g_stopbp + 1 gb_von, p2.g_stopbp gb_bis
        FROM pcrprot p1, pcrprot p2, pcrprimer pri1, pcrprimer pri2, pcrexperiment pcr
        WHERE p1.pcrprid = pri1.id
        AND p2.pcrprid = pri2.id
        AND pri1.upperlowerstrand = 'U' AND pri2.upperlowerstrand = 'L'
        AND p1.seqdna_id = p2.seqdna_id
        AND pcr.endprimerid5 = pri1.id
        AND pcr.endprimerid3 = pri2.id
        UNION
        SELECT p1.seqdna_id seqdna_id, pri1.id prid1, pri2.id prid2,
          pcr.pcrexperimentid pcrexpid, 1 l_vectorpri,
          p1.g_stopbp + 1 gb_von, 0 gb_bis
        FROM pcrprot p1, pcrprimer pri1, pcrprimer pri2, pcrexperiment pcr
        WHERE p1.pcrprid = pri1.id
        AND pri2.vectorprimer <> 0
        AND pri1.upperlowerstrand = 'U'
        AND pcr.endprimerid5 = pri1.id
        AND pcr.endprimerid3 = pri2.id
        AND pcr.template_pcrid IS NULL
        ) pcr
        WHERE l.pcrexpid = pcr.pcrexpid
        AND l.ligationid = ligid_curin;
    orf VARCHAR2(32767);
    gbseq CLOB;
    type_# genbankentries.type_%TYPE;
    cdsend# genbankentries.cdsend%TYPE;
    primer_rec primer_cur%ROWTYPE;
    primercur_error EXCEPTION;
    vectorseq_error EXCEPTION;
    stopinframe NUMBER;
    prifragment VARCHAR2(32767);
  BEGIN
      /* select Ligation and PCR primer info */
      dbms_output.put_line('Start predict_cloneorf(' || ligid_in || ')');
      BEGIN
        OPEN primer_cur(ligid_in);
        FETCH primer_cur INTO primer_rec;
        dbms_output.put_line('Done fetching primercur. PCR Exp ID = ' || primer_rec.pcrexpid);
        IF primer_rec.pcrexpid is null then
          RAISE primercur_error;
        end if;
      EXCEPTION /* PCR information not sufficient for this ligation */
        WHEN NO_DATA_FOUND OR primercur_error THEN
          CLOSE primer_cur;
          RAISE primercur_error;
      END;
      CLOSE primer_cur;
      /* select vector info */
      orf := vector_orf_start(primer_rec.vectorid, primer_rec.re1vector);
      IF orf IS NULL THEN
        RAISE vectorseq_error;
      END IF;
      orf := orf ||  primer_fragment(primer_rec.prid1) ;

      /* select GenBank sequence info corresponding to primer */
      SELECT s.seq
      INTO gbseq
      FROM seq_dna s
      WHERE s.seqdna_id = primer_rec.seqdna_id;
      /* generate the ORF with two specific primers */
       IF primer_rec.l_vectorpri = 0 THEN
        orf := CONCAT (orf,
          DBMS_LOB.SUBSTR(gbseq,
          primer_rec.gb_bis - primer_rec.gb_von, primer_rec.gb_von));
         /* Hier muss ueberprueft werden, ob primer_fragment(primer_rec.prid2)
        ein stop-codon im Frame enthaelt. */
        prifragment := primer_fragment(primer_rec.prid2);
        orf := CONCAT (orf, prifragment);
        DBMS_OUTPUT.PUT_LINE('primerfragment 2: ' || length(prifragment) ||
          ' bp, orf ' || length(orf) || ' bp'); 
        stopinframe := stop_inframe(orf, 0);
        IF stopinframe > 0 THEN
          DBMS_OUTPUT.PUT_LINE('stop codon in orf prifragment = ' ||
            prifragment || ' stopinframe = ' || stopinframe ||
            ', Length(orf) = ' || LENGTH(orf) ||
            ', MOD(3 - MOD(LENGTH(orf),3),3) = ' || MOD(3 - MOD(LENGTH(orf), 3), 3));
          orf := SUBSTR(orf, 1, stopinframe + 2);
     	  ELSE
          orf := CONCAT (orf, vector_orf_end(primer_rec.vectorid, primer_rec.re2vector));
          DBMS_OUTPUT.PUT_LINE('mit vector end: orf ' || length(orf) || ' bp'); 
        END IF;
      /* generate the ORF with one specific and one vector primer */
      ELSIF primer_rec.l_vectorpri = 1 THEN
        orf := CONCAT (orf, DBMS_LOB.SUBSTR(gbseq, 32767, primer_rec.gb_von));
        stopinframe := stop_inframe(orf, MOD(3 - MOD(LENGTH(orf), 3), 3));
        IF stopinframe > 0 THEN
          orf := SUBSTR(orf, 1, stopinframe + 2);
        ELSE
          orf := '';
          DBMS_OUTPUT.PUT_LINE('cannot predict ORF, because no stop codon found in template sequence.');    
        END IF;
      END IF;
      /* end, print some summary information */
        DBMS_OUTPUT.PUT_LINE('orfstart = ' || substr(orf,1,60));
        DBMS_OUTPUT.PUT_LINE('orfmiddle = ' || substr(orf,650,100));
        DBMS_OUTPUT.PUT_LINE('Laenge = ' || length(orf) || ' primer_rec.gb_von = '
        || primer_rec.gb_von || ' primer_rec.seqdna_id = ' || primer_rec.seqdna_id);
        DBMS_OUTPUT.PUT_LINE('orfend = ' || substr(orf,-60));
        RETURN orf;
  EXCEPTION
    WHEN primercur_error THEN BEGIN
      DBMS_OUTPUT.PUT_LINE(
        'Error in function SEQ_DNAPEP2.predict_cloneorf: cannot select the primer information.');
      orf := predict_cloneorf_insertclone(ligid_in);
      IF orf IS NOT NULL THEN
        RETURN orf;
      END IF;
      orf := predict_cloneorf_nestedpcr(ligid_in);
      IF orf IS NOT NULL THEN
        RETURN orf;
      END IF;
      RETURN '';
    END;
    WHEN vectorseq_error THEN BEGIN
      DBMS_OUTPUT.PUT_LINE(
        'Error in function predict_cloneorf: cannot find sequence of vector. Vector ID =' ||
        primer_rec.vectorid);
      RETURN '';
    END;
END;



  FUNCTION predict_cloneorf_insertclone(ligid_in ligation.ligationid%TYPE)
    RETURN VARCHAR2 IS
    /* a cursor that makes sure we have all the necessary information from the ligation entry.*/
    CURSOR insertclone_cur (ligid_curin ligation.ligationid%TYPE) IS
      SELECT
        l.insertcloneid, l.vectorid, l.re1vector, l.re2vector, l.re1insert, l.re2insert
      FROM
        ligation l
      WHERE l.insertcloneid IS NOT NULL
      AND l.vectorid IS NOT NULL
      AND l.re1vector IS NOT NULL
      AND l.re2vector IS NOT NULL
      AND l.re1insert IS NOT NULL
      AND l.re2insert IS NOT NULL
      AND l.ligationid = ligid_curin;
    insertclone_rec insertclone_cur%ROWTYPE;
    iclone_cur_error EXCEPTION;
    vectorseq_error EXCEPTION;
    orf VARCHAR2(32767);
    vec_orfend VARCHAR2(32767);
    insert_lig ligation.ligationid%TYPE;
    insertclone_orf VARCHAR2(32767);
    insclone_orf_digest VARCHAR2(32767);
    insclone_lig ligation.ligationid%TYPE;
    ins_re1_pos NUMBER(6);
    ins_re2_pos NUMBER(6);
    site# restrictionenzymes.sitesequence%TYPE;
    cutupperstrand# restrictionenzymes.cutupperstrand%TYPE;
BEGIN
      /* select Ligation and insert_clone info */
      dbms_output.put_line('Start predict_cloneorf_insertclone(' || ligid_in || ') !');
      BEGIN
        OPEN insertclone_cur(ligid_in);
        FETCH insertclone_cur INTO insertclone_rec;
        CLOSE insertclone_cur;
        IF insertclone_rec.insertcloneid is null then
          raise iclone_cur_error;
        ELSE
          dbms_output.put_line('Done fetching insertclone_cur. clone ID = ' || insertclone_rec.insertcloneid);
        end if;
      EXCEPTION /* insert clone information not sufficient for this ligation */
        WHEN NO_DATA_FOUND THEN
        BEGIN
          RAISE iclone_cur_error;
        END;
      END;
      /* Try to get the predicted ORF of the insert clone */
      insclone_lig := get_lig_clonlig(insertclone_rec.insertcloneid, 0);
      dbms_output.put_line('ligation of insertclone ' ||
        insertclone_rec.insertcloneid || ':' ||  insclone_lig);
      insertclone_orf := predict_cloneorf(insclone_lig);
      IF insertclone_orf IS NULL THEN
          DBMS_OUTPUT.PUT_LINE(
            'Error in function predict_cloneorf_insertclone: cannot get predicted orf for insert clone.');
          RETURN NULL;
      END IF;
      /* digest the ORF sequences with the enzymes re1insert and re2insert. */
      SELECT r.sitesequence, r.cutupperstrand INTO site#, cutupperstrand#
      FROM restrictionenzymes r WHERE r.enzymeid = insertclone_rec.re2insert;
      ins_re2_pos := INSTR(insertclone_orf, site#, 1, 1); /* first occurance of re2 site */
      IF ins_re2_pos > 0 THEN
        ins_re2_pos := ins_re2_pos + cutupperstrand#;
        insclone_orf_digest := SUBSTR(insertclone_orf,1,ins_re2_pos - 1);
      ELSE
        insclone_orf_digest := insertclone_orf;
      END IF;
      SELECT r.sitesequence, r.cutupperstrand INTO site#, cutupperstrand#
      FROM restrictionenzymes r WHERE r.enzymeid = insertclone_rec.re1insert;
      ins_re1_pos := INSTR(insertclone_orf,site#,-1,1); /* last occurance of re1 site */
      IF ins_re1_pos > 0 THEN
        ins_re1_pos := ins_re1_pos + cutupperstrand#;
        insclone_orf_digest := SUBSTR(insclone_orf_digest,ins_re1_pos);
      END IF;
      dbms_output.put_line('ins_re1_pos=' || ins_re1_pos || ' ins_re2_pos=' || ins_re2_pos ||
        ' length insclone_orf_digest = ' || length(insclone_orf_digest));
      IF ins_re1_pos > 0 AND ins_re2_pos > 0 AND ins_re1_pos >= ins_re2_pos THEN
        DBMS_OUTPUT.PUT_LINE(
        'Error in function predict_cloneorf_insertclone: position of 1stRE > position of 2nd RE');
        RETURN NULL;
      END IF;
      /* if the ORF was cut with re1insert, we need to get the ORF start from the vector.*/
      IF ins_re1_pos > 0 THEN
            /* select vector info */
        orf := vector_orf_start(insertclone_rec.vectorid, insertclone_rec.re1vector);
        IF orf IS NULL THEN
          RAISE vectorseq_error;
        END IF;
        orf := CONCAT(orf, insclone_orf_digest);
      ELSE
        orf := insclone_orf_digest;
      END IF;
      /* if the ORF was cut with re2insert, we need to get the ORF end from the vector.*/
      IF ins_re2_pos > 0 THEN
        vec_orfend := vector_orf_end(insertclone_rec.vectorid, insertclone_rec.re2vector);
        IF vec_orfend IS NULL THEN
          DBMS_OUTPUT.PUT_LINE(
            'Error in function predict_cloneorf_insertclone: cannot generate end of ORF from vector.');
          RETURN NULL;
        END IF;
        orf := CONCAT (orf, vec_orfend);
      END IF;
      /* orf is ready to be returned.*/
  RETURN orf;
EXCEPTION
  WHEN iclone_cur_error THEN BEGIN
      DBMS_OUTPUT.PUT_LINE(
        'Error in function predict_cloneorf_insertclone: cannot select the insert clone information.');
      RETURN NULL;
  END;
  WHEN vectorseq_error THEN BEGIN
      DBMS_OUTPUT.PUT_LINE(
        'Error in function predict_cloneorf_insertclone: cannot find sequence of vector. Vector ID =' ||
        insertclone_rec.vectorid);
      RETURN NULL;
  END;
END;


FUNCTION vector_orf_start(
    vectorid_in IN vectors.vectorid%TYPE, renzyme_in IN restrictionenzymes.enzymeid%TYPE
    ) RETURN VARCHAR2 IS
    orf VARCHAR2(32767);
    enzymepos# NUMBER(6);
     startcodonpos# NUMBER(6);
    seq# CLOB;
BEGIN
      BEGIN
      DBMS_OUTPUT.PUT_LINE('looking for ORF start on vector ' || vectorid_in);
        SELECT
          enzymepos(s.seqdna_id, renzyme_in),
          v.startcodon_pos,
          s.seq
      INTO
        enzymepos#, startcodonpos#, seq#
      FROM seq_dna s, vectors v
      WHERE s.seqdna_id = v.seqdna_id
      AND v.vectorid = vectorid_in;
      orf := DBMS_LOB.SUBSTR(
          seq#, enzymepos# - startcodonpos#, startcodonpos#);
         /* This piece of DNA is constant for a
      certain vector/enzyme combination and should be remembered in a look-up table. */
      RETURN orf;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN RETURN NULL;
    END;
END;


FUNCTION vector_orf_end(
    vectorid_in IN vectors.vectorid%TYPE, renzyme_in IN restrictionenzymes.enzymeid%TYPE
    ) RETURN VARCHAR2 IS
    orf_piece VARCHAR2(32767);
    enzymepos# NUMBER(6);
    stopcodonpos# NUMBER(6);
    seq# CLOB;
BEGIN
          SELECT
            enzymepos(s.seqdna_id, renzyme_in) enzymepos,
            v.stopcodon_pos, s.seq
          INTO enzymepos#, stopcodonpos#, seq#
          FROM seq_dna s, vectors v
          WHERE s.seqdna_id = v.seqdna_id
          AND v.vectorid = vectorid_in;
/*DBMS_LOB.SUBSTR (lob_loc, amount, offset) RETURN VARCHAR2 */
          orf_piece := DBMS_LOB.SUBSTR(
              seq#,
              (stopcodonpos# + 2) - enzymepos# + 1,
              enzymepos#
            );
          RETURN orf_piece;
EXCEPTION
      WHEN NO_DATA_FOUND THEN RETURN NULL;

END;

FUNCTION get_pepweight (seq IN CLOB) RETURN NUMBER is
  aa varchar2(1);
  avg_weight# number;
  w number := 0;
begin
  for i in 1 .. dbms_lob.getlength(seq) loop
    aa := dbms_lob.substr(seq, 1, i);
    select avg_weight into avg_weight#
    from amino_acids
    where one_letter = aa;
    w := w + avg_weight#;
  end loop;
  return w - ((dbms_lob.getlength(seq) - 1) * 18.0153);
end;

FUNCTION get_pepweight (seq IN VARCHAR2) RETURN NUMBER is
  clobmich CLOB;
  w number;
begin
  dbms_lob.createtemporary(clobmich, TRUE, dbms_lob.session);
  dbms_lob.writeappend(clobmich,length(seq),seq);
  w := get_pepweight(clobmich);
  return w;
end;

FUNCTION stop_inframe (seq IN VARCHAR2, frame IN NUMBER) RETURN NUMBER IS
    codon VARCHAR2(3);
    inframe_seq VARCHAR2(32767);
  BEGIN
    inframe_seq := substr(seq, frame + 1);
    FOR i IN 0..FLOOR(LENGTH(seq)/3) LOOP
      codon := UPPER(SUBSTR(inframe_seq, i * 3 + 1, 3));
      IF codon IN ('TGA', 'TAA', 'TAG') THEN
        RETURN i * 3 + 1 + frame;
      END IF;
    END LOOP;
    RETURN 0;
  END;

/*
FUNCTION primer_fragment(primerid_in pcrprimer.id%TYPE)
    RETURN pcrprimer.primerseq%TYPE IS
    pseq pcrprimer.primerseq%TYPE;
    primerseq_rc pcrprimer.primerseq%TYPE;
    CURSOR prire_cur (primerid_curin pcrprimer.id%TYPE) IS
      SELECT
          p.primerseq,
          p.enzymeid
      FROM pcrprimer p
      WHERE p.id = primerid_in
      AND p.enzymeid IS NOT NULL;
      prire_rec prire_cur%ROWTYPE;
  BEGIN
    OPEN prire_cur (primerid_in);
    FETCH prire_cur INTO prire_rec;
    CLOSE prire_cur;
    pseq := primer_fragment(prire_rec.primerseq, prire_rec.enzymeid);
    DBMS_OUTPUT.PUT_LINE('primer seq ' || pseq);
    RETURN pseq;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN NULL;
END;
*/
FUNCTION primer_fragment(primerid_in pcrprimer.id%TYPE)
    RETURN pcrprimer.primerseq%TYPE IS
    pseq pcrprimer.primerseq%TYPE;
    primerseq_rc pcrprimer.primerseq%TYPE;
    CURSOR prire_cur (primerid_curin pcrprimer.id%TYPE) IS
      SELECT
          p.primerseq,
          p.upperlowerstrand,
          p.enzymeid,
          r.cutupperstrand,
          r.sitesequence
      FROM pcrprimer p, restrictionenzymes r
      WHERE p.enzymeid = r.enzymeid AND p.id = primerid_in;
      prire_rec prire_cur%ROWTYPE;
      enz_pos NUMBER;
  BEGIN
    OPEN prire_cur (primerid_in);
    FETCH prire_cur INTO prire_rec;
    IF prire_rec.upperlowerstrand LIKE 'L' THEN
      primerseq_rc := reverse_seq(complement_seq(prire_rec.primerseq));
      enz_pos := enzymepos(primerseq_rc, prire_rec.enzymeid);
      pseq := SUBSTR(primerseq_rc, 1, enz_pos - 1);
/*              prire_rec.cutupperstrand + INSTR(primerseq_rc, prire_rec.sitesequence) - 1);*/
    ELSE
      DBMS_OUTPUT.PUT_LINE('primer seq. cutupperstrand ' || prire_rec.cutupperstrand);
      DBMS_OUTPUT.PUT_LINE('primer seq. pos ' || INSTR(prire_rec.primerseq, prire_rec.sitesequence));
      enz_pos := enzymepos(prire_rec.primerseq, prire_rec.enzymeid);
      pseq := SUBSTR(prire_rec.primerseq, enz_pos);
    END IF;
    CLOSE prire_cur;
    DBMS_OUTPUT.PUT_LINE('primer seq ' || pseq);
    RETURN pseq;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    BEGIN
      SELECT p.primerseq
      INTO pseq
      FROM pcrprimer p
      WHERE p.id = primerid_in;
      RETURN pseq;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN RETURN NULL;
    END;
END;


/* determines the cut site position of the primer. Returns the DNA piece downstream of the cut */
  FUNCTION enzymepos(seq_in IN VARCHAR2,
    enzymeid_in IN restrictionenzymes.enzymeid%TYPE)
    RETURN NUMBER IS
      revseq VARCHAR2(30000);
      site# restrictionenzymes.sitesequence%TYPE;
      pos_site NUMBER; cutupperstrand# NUMBER;
      return_pos NUMBER;
    BEGIN
      SELECT r.sitesequence, r.cutupperstrand INTO site#, cutupperstrand#
      FROM restrictionenzymes r
        WHERE r.enzymeid = enzymeid_in;
      /* check that there is only one occurance of re-site in this sequence */
      IF INSTR(seq_in, site#,1,2) = 0 THEN
        pos_site := INSTR(seq_in, site#,1,1);
        IF pos_site > 0 THEN
          return_pos := cutupperstrand# + pos_site;
         ELSE
          /* try the reverse strand */
          revseq := reverse_seq(complement_seq(seq_in));
          pos_site := INSTR(revseq, site#, 1, 1);
          IF pos_site > 0 THEN
            return_pos := LENGTH(seq_in) - (pos_site + cutupperstrand# - 2);
              /* || ' pos_site ' || pos_site
              || ' cutupperstrand# ' || cutupperstrand# || ' len ' || length(primerseq_in) ;*/
          END IF;
  END IF;
         DBMS_OUTPUT.PUT_LINE('enzyme ' || enzymeid_in || ': pos of DNA seq ' || seq_in || ' is: ' || return_pos);
        RETURN return_pos;
      ELSE
        RETURN 0;
      END IF;
END;



FUNCTION enzymepos(seqdnaid_in IN seq_dna.seqdna_id%TYPE,
                     enzymeid_in IN restrictionenzymes.enzymeid%TYPE)
    RETURN NUMBER IS
      seq# CLOB; revseq CLOB;
      site# restrictionenzymes.sitesequence%TYPE;
      pos_site NUMBER; cutupperstrand# NUMBER;
      return_pos NUMBER;
    BEGIN
      SELECT s.seq INTO seq# FROM seq_dna s WHERE s.seqdna_id = seqdnaid_in;
      SELECT r.sitesequence, r.cutupperstrand INTO site#, cutupperstrand#
      FROM restrictionenzymes r
        WHERE r.enzymeid = enzymeid_in;
      /* check that there is only one occurance of re-site in this sequence */
      IF DBMS_LOB.INSTR(seq#, site#,1,2) = 0 THEN
        pos_site := DBMS_LOB.INSTR(seq#, site#,1,1);
        IF pos_site > 0 THEN
          return_pos := cutupperstrand# + pos_site;
         ELSE
          /* try the reverse strand */
          revseq := reverse_seq(complement_seq(seq#));
          pos_site := DBMS_LOB.INSTR(revseq, site#, 1, 1);
          IF pos_site > 0 THEN
            return_pos := DBMS_LOB.GETLENGTH(seq#) - (pos_site + cutupperstrand# - 2);
              /* || ' pos_site ' || pos_site
              || ' cutupperstrand# ' || cutupperstrand# || ' len ' || length(primerseq_in) ;*/
          END IF;
  END IF;
        DBMS_OUTPUT.PUT_LINE('enzyme ' || enzymeid_in || ': pos of DNA seq ' || seqdnaid_in || ' is: ' || return_pos);
        RETURN return_pos;
      ELSE
        RETURN 0;
      END IF;
END;





FUNCTION reverse_seq(seq_in IN CLOB)
    RETURN CLOB IS
      seq_rev CLOB;
      slen NUMBER;
    BEGIN
      dbms_lob.createtemporary(seq_rev, TRUE, dbms_lob.session);
      slen := DBMS_LOB.GETLENGTH(seq_in);
      FOR pos IN 1..slen
      LOOP
        DBMS_LOB.COPY(seq_rev, seq_in, 1, pos, slen - pos + 1  );
      END LOOP;
      RETURN seq_rev;
END;

FUNCTION reverse_seq(seq_in IN VARCHAR2)
    RETURN VARCHAR2 IS
      seq_rev VARCHAR2(32767);
      slen NUMBER;
    BEGIN
      slen := LENGTH(seq_in);
      FOR pos IN 1..slen
      LOOP
        seq_rev := CONCAT(seq_rev, substr(seq_in, slen - pos + 1, 1));
      END LOOP;
      RETURN seq_rev;
END;

FUNCTION complement_seq(seq_in IN CLOB)
    RETURN CLOB IS
      seq_rev CLOB; seq_temp CLOB;
      seqstr VARCHAR2(32767);
    BEGIN
      DBMS_LOB.CREATETEMPORARY(seq_rev, TRUE, dbms_lob.session);
      FOR i IN 0..FLOOR(DBMS_LOB.GETLENGTH(seq_in)/32767) LOOP
        seqstr := complement_seq(
          DBMS_LOB.SUBSTR(seq_in, 32767, (i * 32767) + 1 ));
        DBMS_LOB.WRITE(seq_rev, LENGTH(seqstr), (i * 32767) + 1, seqstr);
      END LOOP;
      RETURN seq_rev;
END;

FUNCTION complement_seq(seq_in IN VARCHAR2)
    RETURN VARCHAR2 IS BEGIN
      RETURN TRANSLATE(UPPER(seq_in),'ACGT','TGCA');
END;
      /* Ref. G.D. Watson and F.H.C. Crick, 1953, Nature 4356 */

FUNCTION insert_pcrprot(
    pcrprid_in pcrprimer.id%TYPE, seqdnaid_in pcrprot.seqdna_id%TYPE)
    RETURN VARCHAR2
    IS
    primerseq# pcrprimer.primerseq%TYPE;
    upperlowerstrand# pcrprimer.upperlowerstrand%TYPE;
    seq# CLOB;
    no_ulinfo EXCEPTION;
    pcrprimer_nodata EXCEPTION;
    seqdna_nodata EXCEPTION;
    instr_error EXCEPTION;
    seqdna_other EXCEPTION;
    no_protein_seq EXCEPTION;
    enzstart NUMBER; enzend NUMBER;
    seqmatchstart NUMBER;
    seqmatchend NUMBER;
    instr_result NUMBER;
    primatchstart NUMBER;
    primatchend NUMBER;
    seqdna_id# seq_dna.seqdna_id%TYPE;
    type_# genbankentries.type_%TYPE;
    BEGIN
      BEGIN
        SELECT p.primerseq, p.upperlowerstrand,
          INSTR(p.primerseq,r.sitesequence),
          (INSTR(p.primerseq,r.sitesequence) + LENGTH(r.sitesequence) - 1)
          INTO primerseq#, upperlowerstrand#,enzstart,enzend
          FROM pcrprimer p, restrictionenzymes r
          WHERE p.id = pcrprid_in
          AND p.enzymeid = r.enzymeid (+);
        IF enzstart IS NULL or enzend IS NULL  THEN
          enzstart := 1;
          enzend := 1;
        END IF;
      EXCEPTION WHEN NO_DATA_FOUND THEN
        RAISE pcrprimer_nodata; END;
      BEGIN
        IF upperlowerstrand# LIKE 'U' THEN
          SELECT s.seqdna_id, s.seq, g.type_
            INTO seqdna_id#, seq#, type_#
            FROM genbankentries g, seq_dna s
            WHERE g.seqdna_id = s.seqdna_id
            AND g.seqdna_id = seqdnaid_in;
        ELSIF upperlowerstrand# LIKE 'L' THEN
          SELECT s.seqdna_id, complement_seq(reverse_seq(s.seq)), g.type_
            INTO seqdna_id#, seq#, type_#
            FROM genbankentries g, seq_dna s
            WHERE g.seqdna_id = s.seqdna_id
            AND g.seqdna_id = seqdnaid_in;
        ELSE
          RAISE no_ulinfo;
        END IF;
        IF NOT type_# like 'protein' THEN
          NULL;
          /*RAISE no_protein_seq;*/
        END IF;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN RAISE seqdna_nodata;
        WHEN OTHERS THEN RAISE seqdna_other;
      END;
        FOR offset IN 1..(length(primerseq#)-enzend-10) LOOP /* This finds also the dovetail enzymes. */
          /*DBMS_OUTPUT.PUT_LINE('Suche in der Sequenz seqdna_id = ' || seqdna_id#  ||
            ' mit der Sequenz ' || SUBSTR(primerseq#,enzend+offset) );*/
            instr_result := DBMS_LOB.INSTR(
            seq#,SUBSTR(primerseq#,enzend+offset));
          IF instr_result > 0 THEN
            IF upperlowerstrand# LIKE 'U' THEN
              seqmatchstart := instr_result;
              seqmatchend := seqmatchstart + (LENGTH(primerseq#) - (enzend + offset - 1)) - 1;
            ELSIF upperlowerstrand# LIKE 'L' THEN
              seqmatchstart := DBMS_LOB.GETLENGTH(seq#) - instr_result + 1;
              seqmatchend := seqmatchstart - (LENGTH(primerseq#) - (enzend + offset - 1)) + 1;
            END IF;
            primatchstart := enzend+offset;
            primatchend := LENGTH(primerseq#);
            EXIT;
          ELSIF instr_result IS NULL THEN RAISE instr_error;
          END IF;
        END LOOP;
        IF instr_result = 0 THEN RAISE instr_error; END IF;
        DBMS_OUTPUT.PUT_LINE(' instr_result = ' || instr_result || ' seqmatchstart = ' || seqmatchstart ||
          ' seqmatchend = ' || seqmatchend || ' primatchstart = ' ||
          primatchstart || ' primatchend = ' || primatchend);
        INSERT INTO
          pcrprot (pcrprid,seqdna_id,pr_startbp,pr_stopbp,g_startbp,g_stopbp)
        VALUES
            (pcrprid_in,seqdnaid_in,primatchstart,primatchend,seqmatchstart,seqmatchend);
        RETURN 'Data was successfully entered!';
    EXCEPTION
      WHEN no_ulinfo THEN BEGIN
        RETURN 'Missing strand information for primer.';
      END;
      WHEN pcrprimer_nodata THEN BEGIN
        RETURN 'Could not find primer in database.';
      END;
      WHEN seqdna_nodata THEN BEGIN
        RETURN 'Genbank entry: not in database or no sequence information.';
      END;
      WHEN instr_error THEN BEGIN
        RETURN 'Could not match primer and gene sequence.';
      END;
      WHEN seqdna_other THEN BEGIN
        RETURN 'Missing strand information for primer';
      END;
      WHEN no_protein_seq THEN BEGIN
        RETURN 'Unspecified problem reading GenBank Entry.';
      END;

END;  /*update_pcrprot*/

FUNCTION atcg_count (seq_in IN CLOB, startpos_in IN NUMBER, length_in IN NUMBER)
  RETURN NUMBER IS
    startpos NUMBER;
    seq VARCHAR2(32767);
  BEGIN
    seq := UPPER(DBMS_LOB.SUBSTR(seq_in, length_in, startpos_in));
    RETURN
      count_occurence(seq,'A') +
      count_occurence(seq,'C') +
      count_occurence(seq,'G') +
      count_occurence(seq,'T');
END;

FUNCTION atcg_count (seq_in IN CLOB, startpos_in IN NUMBER)
  RETURN NUMBER IS
  BEGIN
    RETURN atcg_count(seq_in, startpos_in, 32767);
  END;

  FUNCTION atcg_count (seq_in IN CLOB)
  RETURN NUMBER IS
  BEGIN
    RETURN atcg_count(seq_in, 1, 32767);
  END;

FUNCTION count_occurence(text VARCHAR2,searchtext VARCHAR2)
    RETURN NUMBER
  IS
  BEGIN
    RETURN LENGTH(text) - LENGTH(REPLACE(text,searchtext));
END;


FUNCTION ins_seqdna(seq_in seq_dna.seq%TYPE)
  RETURN seq_dna.seqdna_id%TYPE
  IS
    new_seqid seq_dna.seqdna_id%TYPE;
  BEGIN
    IF seq_in IS NULL THEN
      RETURN NULL;
    END IF;
    SELECT big_seq.NEXTVAL INTO new_seqid FROM DUAL;
    INSERT INTO seq_dna (seqdna_id, seq)
    VALUES (new_seqid, seq_in);
    RETURN new_seqid;
END;

FUNCTION ins_seqpep(seq_in seq_pep.seq%TYPE)
  RETURN seq_pep.seqpep_id%TYPE
  IS
    new_seqid seq_pep.seqpep_id%TYPE;
    no_insert EXCEPTION;
    PRAGMA EXCEPTION_INIT(no_insert, -20001);
  BEGIN
    /*IF seq_in IS NULL THEN
      RETURN NULL;
    END IF;*/
    SELECT big_seq.NEXTVAL INTO new_seqid FROM DUAL;
    INSERT INTO seq_pep (seqpep_id, seq)
    VALUES (new_seqid, seq_in);
    RETURN new_seqid;
  EXCEPTION
    WHEN no_insert THEN BEGIN
      DBMS_OUTPUT.PUT_LINE('FEHLER beim schreiben');
      RETURN NULL;
    END;
    WHEN OTHERS THEN BEGIN
      DBMS_OUTPUT.PUT_LINE('Fehler in ins_seqpep.');
      RETURN NULL;
    END;

END;


  function ligclone_molweight(cloneid_in in cloneligation.cloneid%TYPE) return number
  is
    cursor mw_cur is
    select PREDICTEDPEPTIDESIZE
    from cloneligation, ligation l, transformations t
    where cloneid = cloneid_in
    and t.trafo_id = cloneligation.trafo_id
		and l.ligationid = t.ligation_id;
    mw_rec mw_cur%rowtype;
  begin
     open mw_cur;
     fetch mw_cur into mw_rec;
     close mw_cur;
     if not mw_rec.PREDICTEDPEPTIDESIZE is null then
      return mw_rec.PREDICTEDPEPTIDESIZE;
     else
      return null;
     end if;
  exception
    when no_data_found then
    begin
      return null;
    end;
  end;


END SEQ_DNAPEP;