Re: Basic PL/SQL script needed

From: SteveE <me_at_no.where.com>
Date: Tue, 17 Feb 2004 01:35:15 GMT
Message-ID: <ndeYb.744$xf5.7260202@news-text.cableinet.net>

Jack Overhiser wrote:

> I have taken over management of a data warehouse running on
> Oracle/Windows. I would like to add some code to the daily load
> processing scripts that would check that a table has been loaded, that
> is, has more than 0 records. I have .bat scripts that call SQL
> scripts. I'd like the SQL scripts to do a record count and if it is
> zero end with a non-zero return code. I think what I want to use is a
> PL/SQL block in the SQL script. I've come up with the following basic
> process. Can someone fill in the correct code to make it work?
> Thanks.
>
> DECLARE
> recs number := 0;
> BEGIN
> SELECT count(*) INTO recs FROM WarehouseTable;
> IF recs = 0 THEN
> EXIT FAILURE;
> END IF;
> END;
> /

I haven't tried this, but it should work:

WHENEVER SQLERROR EXIT FAILURE DECLARE
recs number := 0;
BEGIN

     SELECT COUNT(*) INTO recs FROM WarehouseTable WHERE ROWNUM=1;
     IF recs = 0 THEN
	RAISE_APPLICATION_ERROR(-20001,'Table empty');
     END IF;

END;
/

Steve Received on Mon Feb 16 2004 - 19:35:15 CST