pl/sql qestion

From: David Doll <djd_at_saul5.u.washington.edu>
Date: 15 Jun 1998 11:35:06 -0700
Message-ID: <7fqsol6bjj9.fsf@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 Mon Jun 15 1998 - 13:35:06 CDT