Re: pl/sql qestion

From: Gitte Tøgersen <g.togersen_at_sondagsavisen.dk>
Date: Tue, 16 Jun 1998 09:00:18 +0200
Message-ID: <6m55ae$hjr$1@news.uni-c.dk>

Hi David !

First of all, I don't know why You have to have 2 tables, You, as far as I can see, only need the table foo2. Buut...if you have to have both tables you can do this:

procedure get_contig (contig_id in out varchar2,

                                       dnaseq    in out varchar2)

cursor c1 is

   select a.contig_id, b.dnaseq
   from foo1 a, foo2b
   where a.contig_id = b.contig_id;

begin

   .
   .
   .

end;

Hope this will help You a little bit.......

Kind regards

Gitte Tøgersen
Denmark
Email: danskdata_at_hotmail.com
--
David Doll skrev i meddelelsen <7fqsol6bjj9.fsf_at_saul5.u.washington.edu>...
>
>Hello,
>
>First sytstem info:
> oracle server (Oracle7 Server Release 7.2.3.0.0 -
> Production Release With the distributed and replication options
> PL/SQL Release 2.2.3.0.0 - Production) on a sun box
> and the SQL*Plus: Release 8.0.3.0.0 running off of a wintel box (NT4.0,
sp3)
> thats on my desk.
>
>Now for the question:
>
> I have a table (foo1) with a column of data:
> contig_ids varchar2(2000)
>and I have another table (foo2) that has these two columns:
> contig_id varchar2(15)
> dnaseq varchar2(2000)
>
>Note: both tables have more info that what I have listed.
>
>Here is an example of what could be in contig_ids:
>64161 63464 62513 62422 62234 61625 61192
>Note: there will be at least one id here and upto a hundred or so...
>
>these are contig_id's that are in foo2. The dnaseq in foo2 has stuff like:
>TGNTTTACTAAAAGTCATCCCTCCAAACCTTTCTAA...etc.
>Note: this could be short ~100 char or it could be really long...I might
even
>have to make this a different field that a varchar2(2000); I haven't
crossed
>that bridge yet.
>
>Now I'm trying to build a summary table with the contig_ids and the
associated
>dnaseq with that contig_id, so for example I would have:
>
>summary table
>--------------
>contig_id : 64161
>dnaseq: AAAAGTCATCCCTCCAAACCT
>and other admin info like which library it came from, etc.
>contig_id : 4314
>dnaseq: GCACAAGACAGGTAGGG
>and other admin info like which library it came from, etc.
>
>The problem I'm having is how to parse out the different ids from
contig_ids
>so that I can get the dnaseq for that given id. At a high level I assume
you
>have to create a cursor to hold the contig_ids and then create another
>cursor(?) to use those ids to obtain the dnaseq for that given id but I
>haven't been able to see how to put together the PL/SQL code for this.
>I would appreciate any help, suggestions, etc. that anyone could offer.
>Thanks.
>
>David
>djd_at_u.washington.edu
>
Received on Tue Jun 16 1998 - 02:00:18 CDT