SQL & PL/SQL

Hello together,

i have two question. The first question is: I want to iterate over all distinct identifiers from one table. So in my first step i select distinct this identifiers and put these in a map:

  i:=0;
  FOR data_rec IN (SELECT distinct identifier from table) LOOP
    map(i):=data_rec.map;
    i:=i+1;
    num:=num+1;
  END LOOP; 

Then i want to get all the distinct tuples of the entry in map with two for statements. For reducing the complexity i want to iterate only over the upper triangular matrix with that code below:

 for i in 0..map.count-1 loop 
    for z in (i+1)..map.count-1 loop
      INSERT STATEMENT
    end loop;
 end loop;

The script runs for my little test sets but over all identifier it crashes with insert a duplicate key in the new table. In my new table the primary key contains the two identifier.

So my questions are: Is there an failure in the script which i didn't see and how can i handle exceptions in plsql so that if an exception is occurred the script will skip that insert statement and try the next tuple of two identifiers.

My typical set is about of 6 million identifiers so i think an select statement to check if the primary key exists could be slow down my script.

I hope you could help and unterstand what i mean.

With best

Edited by: bladepit on 08.04.2013 23:10

Edited by: bladepit on 08.04.2013 23:11