471,050 Members | 1,442 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,050 software developers and data experts.

Basic PL/SQL script needed

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;
/
Jul 19 '05 #1
1 9156
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
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Edwin Rozie | last post: by
7 posts views Thread by Michael Foord | last post: by
1 post views Thread by Tom Rahav | last post: by
9 posts views Thread by Malcolm | last post: by
1 post views Thread by Jack Overhiser | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.