PL/SQL procedures and functions

[balabioinfo]

sir,

i have 3 doubts regarding pl/sql procedures. i will be ever greatful to you if you clarify my doubts .
--------------------------------------------------------------------

First 1 is: How can I find charecters presented in a DNAsequence (column name) inserted in a table(varchar2 or long )(A,T's) using PL/SQL sub programme?

i.e Suppose a sequence is like this : ATTCACGTAGGCGATCACTGTAC etc. (column data)
In this sequence to find out how many A,G,T,Cs are present.
----------------------------------------------------------------------

Second 1 is: how can i find the size of a DNA sequence, inserted in a table, using PL/SQL function (without using Length() function)?

i.e Suppose a sequence is like this : ATTCACGTAGGCGATCACTGTAC etc. (column data)
In this sequence to find out total length of the sequence without using the Length() function.
----------------------------------------------------------------------
Thrid 1 is: How can i find total number of charecters in a DNA sequence, inserted in a table, using PL/SQL Trigger?

i.e Suppose a sequence is like this : ATTCACGTAGGCGATCACTGTAC etc. (column data). In this sequence to find out the total number of characters(A,G,T,Cs) are present or number of characters in the sequence.

---------------------------------------------------------------------

please clarify my doubts .

waiting for yur reply...

thanking yu,

yur faithfully,

Bala prasad

[TimHall]

Hi.

First off, don't use LONG datatypes. They suck big-time. Use CLOBs instead and like will be good

1) You could do something like one of these two examples (both using LONGs in 9i):

CREATE TABLE test1 (col1 LONG);

INSERT INTO test1 VALUES ('ATTCACGTAGGCGATCACTGTAC');



SET SERVEROUTPUT ON

DECLARE

  l_seq  LONG;

  l_a    NUMBER := 0;

  l_t    NUMBER := 0;

  l_c    NUMBER := 0;

  l_g    NUMBER := 0;

BEGIN

  SELECT col1

  INTO   l_seq

  FROM   test1

  WHERE  rownum =1;

  

  l_a := TO_NUMBER(LENGTH(REPLACE(REPLACE(REPLACE(l_seq, 'T', ''), 'C', ''), 'G', '')));

  l_t := TO_NUMBER(LENGTH(REPLACE(REPLACE(REPLACE(l_seq, 'A', ''), 'C', ''), 'G', '')));

  l_c := TO_NUMBER(LENGTH(REPLACE(REPLACE(REPLACE(l_seq, 'A', ''), 'T', ''), 'G', '')));

  l_g := TO_NUMBER(LENGTH(REPLACE(REPLACE(REPLACE(l_seq, 'A', ''), 'T', ''), 'C', '')));



  DBMS_OUTPUT.put_line('A:' || l_a || ' T:' || l_t || ' C:' || l_c || ' G:' || l_g);

END;

/



SET SERVEROUTPUT ON

DECLARE

  l_seq  LONG;

  l_a    NUMBER := 0;

  l_t    NUMBER := 0;

  l_c    NUMBER := 0;

  l_g    NUMBER := 0;

BEGIN

  SELECT col1

  INTO   l_seq

  FROM   test1

  WHERE  rownum =1;



  FOR i IN 1 .. LENGTH(l_seq) LOOP

    CASE SUBSTR(l_seq, i, 1)

      WHEN 'A' THEN l_a := l_a + 1;

      WHEN 'T' THEN l_t := l_t + 1;

      WHEN 'C' THEN l_c := l_c + 1;

      WHEN 'G' THEN l_g := l_g + 1;

    END CASE;

  END LOOP;



  DBMS_OUTPUT.put_line('A:' || l_a || ' T:' || l_t || ' C:' || l_c || ' G:' || l_g);

END;

/

2) What's the reason for not using the LENGTH function? That's what it's for!

3) You can't reference a LONG in a trigger so you can only do this with a VARCHAR2 or a CLOB. Here's an example with a CLOB:

DROP TABLE test1;

CREATE TABLE test1 (col1 CLOB);



CREATE OR REPLACE TRIGGER test1_aiur

AFTER INSERT OR UPDATE ON test1

FOR EACH ROW

DECLARE

  l_a    NUMBER := 0;

  l_t    NUMBER := 0;

  l_c    NUMBER := 0;

  l_g    NUMBER := 0;

BEGIN

  l_a := TO_NUMBER(LENGTH(REPLACE(REPLACE(REPLACE(:new.col1, 'T', ''), 'C', ''), 'G', '')));

  l_t := TO_NUMBER(LENGTH(REPLACE(REPLACE(REPLACE(:new.col1, 'A', ''), 'C', ''), 'G', '')));

  l_c := TO_NUMBER(LENGTH(REPLACE(REPLACE(REPLACE(:new.col1, 'A', ''), 'T', ''), 'G', '')));

  l_g := TO_NUMBER(LENGTH(REPLACE(REPLACE(REPLACE(:new.col1, 'A', ''), 'T', ''), 'C', '')));



  DBMS_OUTPUT.put_line('A:' || l_a || ' T:' || l_t || ' C:' || l_c || ' G:' || l_g);

END;

/



SET SERVEROUTPUT ON

INSERT INTO test1 VALUES ('ATTCACGTAGGCGATCACTGTAC');

Remember, it's always better to put code into packaged procedures and call them from triggers rather than putting the codein a trigger directly.

Cheers

[TimHall]

Bye the way, I did my PhD in Molecular Genetics. This takes me back a bit

[slimdave]

Originally posted by TimHall
Remember, it's always better to put code into packaged procedures and call them from triggers rather than putting the codein a trigger directly.

And better then PL/SQL is to use SQL functions -- you avoid a costly context switch by doing so.