-
PL/SQL procedures and functions
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
-
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
-
Bye the way, I did my PhD in Molecular Genetics. This takes me back a bit
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|