Re: pl/sql qestion

Well, David, after I read this post and your later post, I understand more of what you're trying to do. BTW, I messed up on the earlier code I gave you. I used the fetch statement instead of just assigning my_var to my_rec.seq_ids.

declare my_var=' '

declare
  cursor my_cur is
    select replace(seq_ids,' ',',') -- replace spaces with commas     from table1;
begin
  for my_rec in my_cur loop
    my_var := my_rec.seq_ids;

    insert into table3
    select id, dna_seq
    from table2
    where id in (&my_var); --my_var looks like this: 4635,3647,8572   end loop;
end;

Hope this helps.

-Eric

David Doll wrote:

> 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 Wed Jun 17 1998 - 11:03:39 CDT