Need help

Bilal Khan · **Joined:** Sat Mar 13, 2010 7:03 am **Posts:** 18

I want to delete the duplicated record using following methods, it delete the records if all the record are matched according to condition, but if all records are not matched then it display error messages. so plz help me in this regards.

Code:

SQL> SELECT * FROM XYZ;
 
NAME           FNAME                ADDRESS              JOB 
-------------- -------------------- -------------------- -----------------------------------------
Bilal Khan     Wali ur rehman name  district abbottabad  student 
Bilal Khan     Wali ur rehman name  district abbottaba   student
Bilal Kh       Wali ur rehma name   district abbotta     studen 
Bilal K        Wali ur rehm name    district abbotta     studen 
Bilal Khan     Wali ur rehman name  istrict abbottaba  tudent
lal Khan       i ur rehman name     strict abbottaba     tudent
 
 
6 rows selected.
 
SQL> ED
Wrote file afiedt.buf
 
  1   declare
  2      type typ_name is table of xyz%rowtype;
  3      tmp_xyz typ_name;
  4      match_count PLS_INTEGER :=0;
  5      begin
  6      SELECT * BULK COLLECT INTO tmp_xyz FROM xyz;
  7      FOR i in 1..tmp_xyz.LAST LOOP
  8      FOR j in i+1..tmp_xyz.COUNT LOOP
  9       match_count:=0;
 10       IF utl_match.edit_distance_similarity(tmp_xyz(i).name,tmp_xyz(j).name)>50 THEN --used 50% ,change it.
 11         match_count:=match_count+1;
 12       END IF;
 13       IF utl_match.edit_distance_similarity(tmp_xyz(i).fname,tmp_xyz(j).fname)>50 THEN
 14         match_count:=match_count+1;
 15       END IF;
 16       IF utl_match.edit_distance_similarity(tmp_xyz(i).address,tmp_xyz(j).address)>50 THEN
 17         match_count:=match_count+1;
 18       END IF;
 19       IF utl_match.edit_distance_similarity(tmp_xyz(i).job,tmp_xyz(j).job)>50 THEN
 20         match_count:=match_count+1;
 21       END IF;
 22       IF match_count >=3 THEN -- If 3 cols are >50%, change it as per need.
 23       dbms_output.put_line('Deleting: '||tmp_xyz(j).name);
 24       --You can place your delete statement here to dele from original table.
 25        tmp_xyz.DELETE(j);
 26       END IF;
 27      END LOOP;
 28     END LOOP;
 29      dbms_output.new_line;
 30      dbms_output.put_line('****************************');
 31      dbms_output.put_line('Printing Remaing Records');
 32      dbms_output.put_line('****************************');   
 33     FOR i in 1..tmp_xyz.COUNT LOOP
 34      IF tmp_xyz.EXISTS(i) THEN
 35      dbms_output.put_line(tmp_xyz(i).name);
 36      dbms_output.put_line(tmp_xyz(i).fname);
 37      dbms_output.put_line(tmp_xyz(i).address);
 38      dbms_output.put_line(tmp_xyz(i).job);
 39      dbms_output.put_line('---------------------------');
 40      END IF;
 41     END LOOP;
 42*    END;
 
 
43  / 
Deleting: Bilal Khan
Deleting: Bilal Kh
Deleting: Bilal K
Deleting: Bilal Khan
Deleting: lal Khan
****************************
Printing Remaing Records
****************************
Bilal Khan
Wali ur rehman name
district abbottabad 
student 
--------------------------- 
 
PL/SQL procedure successfully completed.
 
SQL> ED
Wrote file afiedt.buf
 
  1   declare
  2      type typ_name is table of xyz%rowtype;
  3      tmp_xyz typ_name;
  4      match_count PLS_INTEGER :=0;
  5      begin
  6      SELECT * BULK COLLECT INTO tmp_xyz FROM xyz;
  7      FOR i in 1..tmp_xyz.LAST LOOP
  8      FOR j in i+1..tmp_xyz.COUNT LOOP
  9       match_count:=0;
 10       IF utl_match.edit_distance_similarity(tmp_xyz(i).name,tmp_xyz(j).name)>90 THEN --used 90% 
 11         match_count:=match_count+1;
 12       END IF;
 13       IF utl_match.edit_distance_similarity(tmp_xyz(i).fname,tmp_xyz(j).fname)>90 THEN
 14         match_count:=match_count+1;
 15       END IF;
 16       IF utl_match.edit_distance_similarity(tmp_xyz(i).address,tmp_xyz(j).address)>90 THEN
 17         match_count:=match_count+1;
 18       END IF;
 19       IF utl_match.edit_distance_similarity(tmp_xyz(i).job,tmp_xyz(j).job)>90 THEN
 20         match_count:=match_count+1;
 21       END IF;
 22       IF match_count >=3 THEN -- If 3 cols are >90%, change it as per need.
 23       dbms_output.put_line('Deleting: '||tmp_xyz(j).name);
 24       --You can place your delete statement here to dele from original table.
 25        tmp_xyz.DELETE(j);
 26       END IF;
 27      END LOOP;
 28     END LOOP;
 29      dbms_output.new_line;
 30      dbms_output.put_line('****************************');
 31      dbms_output.put_line('Printing Remaing Records');
 32      dbms_output.put_line('****************************');   
 33     FOR i in 1..tmp_xyz.COUNT LOOP
 34      IF tmp_xyz.EXISTS(i) THEN
 35      dbms_output.put_line(tmp_xyz(i).name);
 36      dbms_output.put_line(tmp_xyz(i).fname);
 37      dbms_output.put_line(tmp_xyz(i).address);
 38      dbms_output.put_line(tmp_xyz(i).job);
 39      dbms_output.put_line('---------------------------');
 40      END IF;
 41     END LOOP;
 42*    END;
SQL> / 
 
Deleting: Bilal Khan
Deleting: Bilal Khan
declare
*
ERROR at line 1:
ORA-01403: no data found 
ORA-06512: at line 8 

Thanks

usman · **Joined:** Fri Feb 13, 2009 11:37 am **Posts:** 118

Hi Bilal,
As you are deleting the element of the collection prior the visit of the loop so it find the element as null so it raises exception.
WorkAround is that create another collection and delete items from that and wrap the second loop within BEGIN and EXCEPTION, END so that the outer loop continues.
Let me know if you find any trouble.

Code:

/* Formatted on 21/05/2010 09:13:08 (QP5 v5.115.810.9015) */
DECLARE
   TYPE typ_name IS TABLE OF emp%ROWTYPE;

   tmp_emp       typ_name;
   tmp_emp2       typ_name;
   
   match_count   PLS_INTEGER := 0;
   errors        PLS_INTEGER;

   dml_errors EXCEPTION;
   PRAGMA EXCEPTION_INIT (dml_errors, -24381);
   str           VARCHAR2 (100);
   
   x number;
BEGIN
     SELECT   *
       BULK   COLLECT
       INTO   tmp_emp
       FROM   emp
   ORDER BY   ename;
     
     SELECT   *
       BULK   COLLECT
       INTO   tmp_emp2
       FROM   emp
   ORDER BY   ename;
   
x:=tmp_emp.count;

   FOR i IN 1 .. tmp_emp.LAST
   LOOP
   begin
      FOR j IN i + 1 .. tmp_emp2.COUNT
      LOOP
         match_count := 0;

         IF utl_match.edit_distance_similarity (tmp_emp (i).ename,
                                                tmp_emp (j).ename) > 50
         THEN                                           --used 50% ,change it.
            match_count := match_count + 1;
         END IF;


         IF match_count >= 1
         THEN                    -- if 3 cols are >50%, change it as per need.
            DBMS_OUTPUT.put_line (
               tmp_emp (i).ename || ' is like ' || tmp_emp (j).ename
            );
            --you can place your delete statement here to dele from original table.
            str := tmp_emp (j).ename;
            tmp_emp.delete(j);
           
            DBMS_OUTPUT.put_line ('Deleting :' || str);
         END IF;
      END LOOP;
      exception
      when no_data_found then
      DBMS_OUTPUT.put_line (sqlerrm);
      end;
   END LOOP;

   DBMS_OUTPUT.new_line;
   DBMS_OUTPUT.put_line ('****************************');
   DBMS_OUTPUT.put_line ('Printing Remaing Records');
   DBMS_OUTPUT.put_line ('****************************');
FOR i IN 1 .. tmp_emp.COUNT
 LOOP
    IF tmp_emp.EXISTS (i)
    THEN
       DBMS_OUTPUT.put_line (tmp_emp2 (i).ename);
       
    END IF;
    
 END LOOP;
DBMS_OUTPUT.put_line ('---------------------------');
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END;

JAMES is like JONES
Deleting :JONES
ORA-01403: no data found
****************************
Printing Remaing Records
****************************
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
---------------------------

No JONES in the list

Need help

Who is online