please help = pl/sql

From: JDC <tradeNOSPAM_at_localnet.com>
Date: Tue, 24 Jun 2003 22:34:15 -0400
Message-ID: <vfi2e8h8dv8ba3@corp.supernews.com>

Hello all,
I hope this newsgroup is the correct one for my question....

essentially, I am trying to use PL/SQL to: search a table, and find records that are missing from the sequential order...
*there could be double records, but there cannot be gaps** we need the value of the gaps printed to the screen for example, the records could be
14002
14003
14003
14003
14004
14004
>> (this should be anything from 14004 - 14009)
14010
14011
14012

---

so the output should be something like:
14002
14003
14003
14003
14004
14004
 >> *A gap was found: length of gap = 5
14010
14011
14012

What I am trying to do is print to the screen (and later a dummy table) the range thats missing from the list...We are trying to number the records sequentially, but as you can see, there could be gaps. So, from the above records, I would need to find the difference between the record 14004 and 14010

here is what I have so far:
I load the records into the cursor, but am having trouble finding the next sequential record to compare to which ever one the loop is at...

thanks in advance!!!!!!

---

DECLARE

v_invoice_no   invoice_detail.invoice_no%TYPE;  -- this is the record
v_invoice_tmp  invoice_detail.invoice_no%TYPE;  -- temp storage
v_compare_value       invoice_detail.invoice_no%TYPE;
v_compare_value2      invoice_detail.invoice_no%TYPE;
v_invoice_date invoice_detail.invoice_date%TYPE; --date goes with record
v_original_value      invoice_detail.invoice_no%TYPE;
v_new_value           invoice_detail.invoice_no%TYPE;
v_result              invoice_detail.invoice_no%TYPE;
v_counter int := 0;   /* loop counter  */

/********************************************************************/

CURSOR c_invoice IS

   SELECT DISTINCT invoice_no, invoice_date

     FROM invoice_detail
       WHERE invoice_no > 1000609 AND invoice_no < 1000620
         ORDER BY 1;
/********************************************************************/

BEGIN
   OPEN c_invoice;

   DBMS_OUTPUT.PUT_LINE('records processed: ' ||  v_counter);
   DBMS_OUTPUT.PUT_LINE('Invoice#           Date');
   DBMS_OUTPUT.PUT_LINE('=======================');
   LOOP
       FETCH c_invoice INTO v_invoice_no, v_invoice_date;
       v_counter := v_counter + 1;
       v_original_value := v_invoice_no;
       DBMS_OUTPUT.PUT_LINE(v_invoice_no || '  -  ' || v_invoice_date);
       IF v_invoice_tmp = v_original_value THEN
           DBMS_OUTPUT.PUT_LINE('test okay for: ' || v_invoice_tmp);
       ELSE
           DBMS_OUTPUT.PUT_LINE('missing record: ' || v_invoice_no);
           DBMS_OUTPUT.NEW_LINE;
           v_result :=  v_original_value - v_new_value;
           DBMS_OUTPUT.PUT_LINE('result: ' || v_result);
       END IF;
       EXIT WHEN c_invoice%NOTFOUND;

   END LOOP;    DBMS_OUTPUT.PUT_LINE('records processed: ' || v_counter);    CLOSE c_invoice;
END;(See attached file: johnsScripts06-24-03.txt) Received on Tue Jun 24 2003 - 21:34:15 CDT