Jenkins Pipeline调用SQL脚本未返回指定错误退出码10问题

问题背景 #

我在Jenkins流水线的Shell脚本中执行Git仓库中的SQL文件，用于自动化更新数据库Schema，要求单个SQL文件内多命令执行出错时，回滚该文件所有ALTER TABLE变更并以退出码10终止。

最初直接执行ALTER命令的脚本功能正常：当第二条命令因表名拼写错误（enployee）触发错误时，会回滚第一条变更，返回退出码10并提示脚本失败。但为避免重复执行ALTER命令报错，我添加了PL/SQL前置检查块，修改后的脚本出现以下问题：

1. 遇到拼写错误时，虽然会回滚变更，但始终返回退出码0，未触发预期的退出码10
2. PL/SQL块内的dbms_output.put_line()无法输出内容（已尝试set serveroutput on和returnStdout: true配置）

修改后的SQL脚本如下：

WHENEVER SQLERROR EXIT 10 ROLLBACK;

DECLARE
    id_exists VARCHAR(1);
    phone_number_nullable VARCHAR(1);
    
BEGIN
    select count(*) INTO id_exists FROM user_tab_cols WHERE upper(TABLE_NAME)= 'employee' and upper(COLUMN_NAME) = 'employee_id';
    IF id_exists = 0 THEN
        EXECUTE IMMEDIATE 'ALTER TABLE employee ADD employee_id NUMBER(13)';
    END IF;
    select NULLABLE INTO phone_number_nullable FROM all_tab_columns WHERE upper(TABLE_NAME)= 'employee' and upper(COLUMN_NAME) = 'phone_number';
    IF phone_number_nullable = 'N' THEN
        EXECUTE IMMEDIATE 'ALTER TABLE enployee MODIFY phone_number NULL';
    END IF;
END;

WHENEVER SQLERROR EXIT 10;

原因分析 #

1. WHENEVER SQLERROR的作用范围限制：该命令仅对PL/SQL块外部的SQL语句生效，PL/SQL块内部的错误会被PL/SQL自身的异常处理机制捕获，不会传递到外部的WHENEVER逻辑。即使EXECUTE IMMEDIATE执行出错，只会触发PL/SQL异常，默认无EXCEPTION块时Oracle会抛出错误，但不会触发外部的EXIT命令，脚本会继续执行最终返回0。
2. dbms_output无输出：默认需要在PL/SQL块执行前设置set serveroutput on，且若PL/SQL块因异常终止，未捕获异常的情况下dbms_output内容不会被输出。

解决方案 #

方案1：在PL/SQL块中添加EXCEPTION块，手动触发退出码 #

修改PL/SQL块，添加EXCEPTION部分捕获异常，通过RAISE_APPLICATION_ERROR抛出可被WHENEVER SQLERROR捕获的自定义错误：

SET SERVEROUTPUT ON;
WHENEVER SQLERROR EXIT 10 ROLLBACK;

DECLARE
    id_exists NUMBER; -- 修正类型：count(*)返回数字，避免隐式转换问题
    phone_number_nullable VARCHAR(1);
    
BEGIN
    SELECT COUNT(*) INTO id_exists 
    FROM user_tab_cols 
    WHERE upper(TABLE_NAME) = 'EMPLOYEE' 
      AND upper(COLUMN_NAME) = 'EMPLOYEE_ID';
      
    IF id_exists = 0 THEN
        EXECUTE IMMEDIATE 'ALTER TABLE employee ADD employee_id NUMBER(13)';
        DBMS_OUTPUT.PUT_LINE('已为employee表添加employee_id列');
    END IF;
    
    SELECT NULLABLE INTO phone_number_nullable 
    FROM all_tab_columns 
    WHERE upper(TABLE_NAME) = 'EMPLOYEE' 
      AND upper(COLUMN_NAME) = 'PHONE_NUMBER';
      
    IF phone_number_nullable = 'N' THEN
        EXECUTE IMMEDIATE 'ALTER TABLE enployee MODIFY phone_number NULL'; -- 拼写错误测试用例
        DBMS_OUTPUT.PUT_LINE('已将employee表的phone_number列设为可空');
    END IF;
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('执行错误：' || SQLERRM);
        -- 抛出自定义错误，触发外部WHENEVER SQLERROR逻辑
        RAISE_APPLICATION_ERROR(-20001, '脚本执行失败：' || SQLERRM);
END;
/

WHENEVER SQLERROR EXIT 10;

关键要点：

• 使用RAISE_APPLICATION_ERROR抛出异常，确保外部WHENEVER SQLERROR能捕获并触发EXIT 10
• 修正id_exists的数据类型为NUMBER，避免隐式转换导致的潜在问题
• 开头添加SET SERVEROUTPUT ON;确保输出能被捕获

方案2：PL/SQL块执行后检查SQLCODE手动退出 #

若不想使用EXCEPTION块，可在PL/SQL块执行后检查SQLCODE，非0则手动退出：

SET SERVEROUTPUT ON;
WHENEVER SQLERROR EXIT 10 ROLLBACK;

DECLARE
    id_exists NUMBER;
    phone_number_nullable VARCHAR(1);
    
BEGIN
    SELECT COUNT(*) INTO id_exists 
    FROM user_tab_cols 
    WHERE upper(TABLE_NAME) = 'EMPLOYEE' 
      AND upper(COLUMN_NAME) = 'EMPLOYEE_ID';
      
    IF id_exists = 0 THEN
        EXECUTE IMMEDIATE 'ALTER TABLE employee ADD employee_id NUMBER(13)';
        DBMS_OUTPUT.PUT_LINE('已添加employee_id列');
    END IF;
    
    SELECT NULLABLE INTO phone_number_nullable 
    FROM all_tab_columns 
    WHERE upper(TABLE_NAME) = 'EMPLOYEE' 
      AND upper(COLUMN_NAME) = 'PHONE_NUMBER';
      
    IF phone_number_nullable = 'N' THEN
        EXECUTE IMMEDIATE 'ALTER TABLE enployee MODIFY phone_number NULL';
        DBMS_OUTPUT.PUT_LINE('已修改phone_number列可空属性');
    END IF;
END;
/

-- 检查PL/SQL块执行状态，出错则退出
IF SQLCODE != 0 THEN
    EXIT 10;
END IF;

WHENEVER SQLERROR EXIT 10;

关于dbms_output输出的补充说明 #

确保满足以下条件：

1. 脚本开头必须添加SET SERVEROUTPUT ON;（注意末尾分号）
2. 若通过sqlplus执行，确保Jenkins的Shell步骤配置returnStdout: true以捕获输出
3. 异常场景下，需在EXCEPTION块中调用DBMS_OUTPUT.PUT_LINE才能输出错误信息

验证方式 #

执行脚本后，在Shell中通过echo $?查看退出码，确认是否为10；Jenkins流水线可通过捕获该退出码判断执行结果。

内容的提问来源于stack exchange，提问作者Effi T