Newbie PL/SQL question

This is a long question, so thanks in advance if you are able to take time to help. I'm learning PL/SQL at the same time as I'm trying to create a solution for identifying rows with duplicate values.

I have a table called SAMPLE in which some rows have duplicate values in all except the PK (ID) column. For example:

ID DNUMBER YEAR NUM DTE

---------- ---------- ---------- ---------- --------------------
    50000    4561           1997          2         07-DEC-41
    50001    4561           1997          1         07-DEC-41
    50003    4692           1996          1         16-SEP-96
    50005    4692           1996          2         07-DEC-41
    50007    4692           1997          1         07-DEC-41
    50009    4692           1997          1         07-DEC-41
    50010    4701           1998          1         12-MAY-98
    50012    4701           1996          1         16-SEP-96
    50014    4701           1996          2         07-DEC-41
    50016    4701           1996          2         07-DEC-41

I am writing a program to return the IDs of every row where the values for DNUMBER, YEAR, NUM and DTE are duplicates of another row. In the records above, I would want to return ID#s 50007, 50009, 50014, 50016. Duplicate values will not always directly follow the values they repeat. I also want this program to be generalizable to other tables in the future.

My current program (has errors) looks like this:

SET serveroutput on;
DECLARE
   CURSOR sample_curs IS
 select DNUMBER, YEAR, NUM, DTE from SAMPLE;    row_var sample_curs%ROWTYPE;
   count_var number(2);
   id_var SAMPLE.ID%TYPE;

BEGIN    OPEN sample_curs;
   FETCH sample_curs INTO row_var;
   WHILE (sample_curs%FOUND) LOOP

 SELECT count (*) into count_var from SAMPLE s where --How many matches?
 s.DNUMBER||s.YEAR||s.NUM||s.DTE =
 row_var.DNUMBER||row_var.YEAR||row_var.NUM||row_var.DTE;  IF count_var > 1 THEN

    SELECT s.ID into ID_var from SAMPLE s where --if > 1, get SAMPLE.ID of dups

    s.DNUMBER||s.YEAR||s.NUM||s.DTE =

         row_var.DNUMBER||row_var.YEAR||row_var.NUM||row_var.DTE;     dbms_output.put_line('Record '||ID_var||' is duplicate.');     END IF;
   FETCH sample_curs into row_var;
   END LOOP;
     CLOSE sample_curs;
   END;
/

The error message I am currently getting is:

DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 18

What's the best way to output the IDs of the rows with dup columns? I've tried a number of
things and currently am low on inspiration. Or is there a better way to approach this? Thanks for any suggestions,

Anne Received on Tue Oct 05 1999 - 00:00:00 CDT