SQLPlus Error Handle

SQLPlus directive "whenever SQLERROR EXIT 1" would 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 corres Pondence code.

Below is a example, Gpdemo.sql is a normal SQL file, it'll be execute by Sqlplus called from Main.bat.

The Main.bat would 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 Gpdemosqlplus System/[email Pro tected] @gpdemo. SQL >> DROPUSER.log:: RELOAD dump file to gpdemoif ERRORLEVEL 1 (    ECHO "Failed to Drop user, Che CK the DROPUSER.log for detail error message ") Else (    ECHO Drop User successfully, would continute to do import"    I MP System/[email protected] fromuser=gpcomp1 touser=gpdemo file=gpdemo.dmp log=gpdemo.log statistics=none)