PL/SQL procedures and functions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: PL/SQL procedures and functions

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

    Bala prasad

  2. #2
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  3. #3
    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
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: www.oracle-base.com
    My blog: www.oracle-base.com/blog

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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