首页 > 代码库 > SQLPlus Error handle
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 GPDEMOsqlplus system/manager@GPODTE @gpdemo.sql >> DROPUSER.log:: RELOAD dump file to GPDEMOif 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)
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。