Re: more pl/sql question

From: Eric Lyders-Gustafson <ericlg_at_homemail.com>
Date: Wed, 17 Jun 1998 10:51:44 -0500
Message-ID: <3587E60F.F640ABD2@homemail.com>

If you're doing this within sqlplus, try this:

define my_var=' '

declare
  cursor my_cur is
    select replace(seq_ids,' ',',') seq_ids -- replace the spaces with commas

    from table1;
begin
  for my_rec in my_cur loop
    fetch my_rec.seq_ids into my_var;

    select dna_sequence
    into my_dna
    from table2
    where id in (&my_var); --&my_var will look like this: 6547,3645,8674,4756

--
--do something with my_dna
--

  end loop;
end;
/

Now, if you're just selecting one row from table1 at a time, then the cursor is not needed. Otherwise, you need some way of 'looping' through each row returned.

I wasn't sure if you had to select each row individually from table2, or if you could use 'IN' to select the group of rows.

-Eric

David Doll wrote:

> I've been told my previous message didn't make much sense; sorry, to much
> work and not enough of sleep :-)
>
> I'll try again: the question is, is it possible to parse out multiple
> values of a value returned in a pl/sql select statement. For example:
>
> select seq_ids
> from table1
>
> returns these:
>
> 61417 6298 123 98705 33489 43265 48495
>
> So the field seq_ids contains one or more groups of id numbers - I
> didn't set this up, just using something someone else wrote.
>
> Each of these numbers are then needed for a different select statment,
> something like:
>
> select dna_sequence
> from table2
> where id = 61417;
>
> which would return something like:
>
> GTAGACTTTCTAAAATAAATAC
>
> So the pl/sql code that has to be written would take the seq_ids
> (indivual id's, not the group of id's), and its dna_sequence, along
> with some admin info, and insert that info into a summary table. Any
> suggestions as to how, if at all possible, to parse out the indivual
> ids and use them in another select would be greatly
> appreciated. Thanks.
>
> David
Received on Wed Jun 17 1998 - 10:51:44 CDT