# Thread: PL/SQL procedures and functions

1. Junior Member
Join Date
Sep 2003
Location
india
Posts
5

## 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,

Join Date
Dec 2001
Location
UK
Posts
1,684
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

Join Date
Dec 2001
Location
UK
Posts
1,684
Bye the way, I did my PhD in Molecular Genetics. This takes me back a bit

4. Foreign Script Kiddie
Join Date
Aug 2002
Location
Posts
5,253
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
•

Click Here to Expand Forum to Full Width