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

<pre>
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;
/</pre>

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:

<pre>
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');</pre>

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.