SQLPlus Error handle

浏览数：31 / 时间：2015年06月12日

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
)

SQLPlus Error handle,古老的榕树,5-wow.com

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

SQLPlus Error handle