Re: please help = pl/sql

Apparently, your problem is the results of the PL/SQL procedure is not displayed. Don't forget to use SET SERVEROUTPUT ON before the PL/SQL block (it is an option of SQL/Plus). See the doc for more information.

unknown wrote:

> 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 Fri Jun 27 2003 - 00:28:25 CDT