Re: more pl/sql question

From: Rohrbacher, Ing. Boris <rohbo_at_sbox.tu-graz.ac.at>
Date: Thu, 18 Jun 1998 20:57:14 +0200
Message-ID: <3589630A.655CA0B6@sbox.tu-graz.ac.at>

Hi,
 seq_ids violates first normal form, but as you sayed it's not your fault and you have to get along with it.

select dna_squence from table2 t2, table1 t1 where instr(' '||t1.seq_ids||' ', ' '||to_char(t2.id)||' ') != 0 ;

Will work BUT poor performance because Oracle cannot use an index if you use a function on a column
to_char is needed if t2.id is a number. Although Oracle would convert t2.id automatically its good style to point out. So I would go for splitting table1 in a master - detail and writing a simple pl/sql programm that fills it.

What happens is that instr searche each id in table2 in the seq_ids string of each line of table 1 for
instance if id is 61417 instr will look for ' 61417 ' in ' 61417 6298 123 98705 33489 43265 48495 ' and find it. Spaces are needed for not to find 6, 61, 614 and 6141 also.

Depending on the enviroment where this statement is needed you can also think of dbms_sql.
If it's in PL/SQL you could construct your pl/SQL statement dynamically like

procedure dyn_solution is
 cursor c1 is select seq_ids from table1;  cseq_id varchar2(500?) ;

 cdna_squence        varchar2(2000) ;
 cc             integer;
 ignore      integer ;

begin

  open c1;
  loop

   fetch c1 into cseq_id ;

   cc := dbms_sql.open_cursor ;
  dbms_sql.parse( cc, ' select dna_sequence from table2 where id in ( '|| replace(cseq_id,' ',',')||' ) ' , dbms_sql.v7 ) ;   dbms_sql.define_column( cc, 1 , cdna_sequence ) ;   ignore := dbms_sql.execute( cc );
  while dbms_sql.fetch_rows( cc ) > 0 loop    dbms_sql.column_value( cc, 1 , cdna_sequence ) ;

• do whatever you have to do with cdna_sequence ; mutate it perhaps
• or write it to output buffer dbms_output.put_line( cdna_sequence ) ; end loop ; dbms_sql.close_cursor( cc );

end ;

Althoug this would overcome performance impact is in first statement it will still be slow because you have to open and execute a cursor on each row of table1;

Regards robo

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 Thu Jun 18 1998 - 13:57:14 CDT