Re: Need help with basic PL/SQL block

Jack Overhiser wrote:

> I have taken over management of a data warehouse running on
> Oracle 9.2 & Windows 2000 Server. 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;
> /
>
>
> When I run it I get:
> EXIT FAILURE;
> *
> ERROR at line 6:
> ORA-06550: line 6, column 12:
> PLS-00201: identifier 'FAILURE' must be declared
> ORA-06550: line 6, column 7:
> PL/SQL: Statement ignored

Let me suggest an alternative.

1. Create a stored procedure to take the count and insert it into a table.
2. If the count is zero have it use UTL_SMTP to email you an alert
3. Run the stored procedure as part of your load or with DBMS_JOB

Working with bat files is fraught will issues. One of which you bumped into.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)