SQLPlus Error handle

SQLPlus directive "WHENEVER SQLERROR EXIT 1" will return a specified code when any SQL error throwed when run a sql file.

Then we can catch the return code from the main bat file (where we call the sqlplus) to test it and then jump to a  correspondence code.

Below is a example, gpdemo.sql is a normal sql file, it will be execute by sqlplus called from main.bat.

The main.bat will test the return code to know whether any sql error occurred when execute the gpdemo.sql.

gpdemo.sql

WHENEVER SQLERROR EXIT 1;

DECLARE
  V_USER_EXISTED INT;
BEGIN
    SELECT COUNT(1) INTO V_USER_EXISTED FROM DBA_USERS WHERE USERNAME='GPDEMO';
    
    IF V_USER_EXISTED >= 1 THEN
            EXECUTE IMMEDIATE 'DROP USER GPDEMO CASCADE';
    END IF;

    EXECUTE IMMEDIATE 'CREATE USER GPDEMO IDENTIFIED BY SUNGARD01';
    
    EXECUTE IMMEDIATE 'GRANT CREATE TABLE TO GPDEMO';
    
    EXECUTE IMMEDIATE 'GRANT CREATE VIEW TO GPDEMO';
    
    EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE TO GPDEMO';
    
    EXECUTE IMMEDIATE 'GRANT CREATE SEQUENCE TO GPDEMO';
    
    EXECUTE IMMEDIATE 'GRANT CREATE SYNONYM TO GPDEMO';
    
    EXECUTE IMMEDIATE 'GRANT CREATE SESSION TO GPDEMO';
    
    EXECUTE IMMEDIATE 'GRANT GETPAID_ROLE TO GPDEMO';
    
    EXECUTE IMMEDIATE 'ALTER USER GPDEMO DEFAULT TABLESPACE GP_DATA';
    
    EXECUTE IMMEDIATE 'ALTER USER GPDEMO TEMPORARY TABLESPACE TEMP';
    
    EXECUTE IMMEDIATE 'ALTER USER GPDEMO QUOTA UNLIMITED ON GP_DATA';
    
    EXECUTE IMMEDIATE 'ALTER USER GPDEMO QUOTA UNLIMITED ON GP_INDX';
END;
/

EXIT 0

main.bat

:: Clear the ERRORLEVEL environment if it had defined.
set ERRORLEVEL =
:: Drop and recreate GPDEMO
sqlplus system/manager@GPODTE @gpdemo.sql >> DROPUSER.log
:: RELOAD dump file to GPDEMO
if ERRORLEVEL 1 (
    ECHO "Failed to Drop user, check the DROPUSER.log for detail error message"
) else (
    ECHO "Drop User Successfully, will continute to do import"
    imp system/manager@GPODTE fromuser=gpcomp1 touser=GPDEMO file=gpdemo.dmp log=gpdemo.log statistics=none
)