Re: please help = pl/sql

From: unknown <uga861_at_hotmail.com>
Date: 26 Jun 2003 12:05:03 -0700
Message-ID: <3aa3cc5b.0306261105.52591ca1@posting.google.com>

SQL> describe tmp_invoice;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 INVOICE                                            NUMBER(9)

SQL>
SQL> select * from tmp_invoice;

   INVOICE

---

         1
         2
         2
         5
         6
         9
        11
        12

8 rows selected.

SQL>
SQL> DECLARE X INTEGER;
  2
  3 BEGIN
  4
  5 -- let the database do the work of identifying every invoice number with   6 -- gaps preceding it...
  7
  8 FOR iRec IN (
  9 select DISTINCT

 10         invoice,
 11         num_missing
 12    from (
 13         select a.invoice,
 14                b.invoice previous_invoice,
 15                a.invoice - ( select max( b.invoice )
 16                                from tmp_invoice b
 17                               where b.invoice < a.invoice ) - 1 num_missing
 18           from tmp_invoice a,
 19                tmp_invoice b
 20          where a.invoice-1 = b.invoice(+)
 21        )

 22 where previous_invoice is null
 23 and invoice > ( select min( invoice )

 24                        from tmp_invoice )

 25 )
 26 LOOP
 27

 28      -- now loop thru and display the missing numbers.
 29      
 30      X := iRec.num_missing;
 31
 32      LOOP
 33
 34          EXIT WHEN X = 0;
 35
 36          DBMS_OUTPUT.PUT_LINE( iRec.invoice - X );
 37
 38          X := X-1;
 39
 40      END LOOP;

 41
 42 END LOOP;
 43
 44 END;
 45 /
3
4
7
8
10

PL/SQL procedure successfully completed. Received on Thu Jun 26 2003 - 14:05:03 CDT