Re: Need help with basic PL/SQL block

"Jack Overhiser" <overhisj_at_ewashtenaw.org> a écrit dans le message de news:2a66f459.0402160809.7705403c_at_posting.google.com...
> 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

whenever sqlerror exit failure;
declare
...
begin
...
if recs = 0 then raise_application_error (-20000,'No record'); end if; end;
/

Regards
Michel Cadot Received on Mon Feb 16 2004 - 12:18:23 CST