SQL script called from Jenkins Pipeline does not output error or correct exit code

I am trying to run a SQL script from within a shell script to be used in a Jenkins pipeline in order to automate updates to my database schema. I am mostly trying to run ALTER TABLE commands that are saved in SQL files in a Git repository. The shell script searches all .sql files within a folder and runs them. If multiple commands are found within a file, the program should rollback all changes made by ALTER TABLE commands in that same file and exit with code 10 if it comes across an error. Currently, this looks as follows:

SQL file:

WHENEVER SQLERROR EXIT 10 ROLLBACK;

ALTER TABLE employee ADD employee_id NUMBER(13);
ALTER TABLE enployee MODIFY phone_number NULL;

WHENEVER SQLERROR EXIT 10;

Jenkins pipeline script:

pipeline {
    stage('Run scripts') {
        steps {
            script {
                dir("sample_dir") {
                    def filename_all_scripts = "all_scripts.txt"
                    def filecontent_all_scripts = readFile filename_all_scripts
                    def all_scripts = filecontent_all_scripts.trim().split("\\r?\\n")
                    
                    def DBSID = "xxx"
                    def DBSERVER = "xxx"
                    def DBPORT = "xxx"
                    def DB_USER = "xxx"
                    def DB_PASSWORD = "xxx"

                    def failed = false
                    def result = 0

                    all_scripts.each { script ->
                        stage(script) {
                            def result = sh(script: """
                            cd ..
                            "$ORACLE_HOME$SQLPLUS_BIN" -L $DB_USER/$DB_PASSWORD@$DBSERVER:$DBPORT/$DBSID @$script""", returnStatus: true)
                            println "Result: " + result

                            if (result != 0) {
                                failed = true
                            } 
                        }
                    }
                    if (failed) {
                        error("One or more scripts failed")
                    }
                }
            }
        }
    }
}

So far, this works as expected: The first command is executed, the script runs into an error due to the typo in employee in the second command, then rolls back the first change and outputs "Result: 10" and "One or more scripts failed".

Here comes the bit that doesn’t work:
Because I might have to run the script again after the ALTER TABLE commands have already been run, I’m trying to include a check if the they have already been executed to not run into problems with duplicates and errors. The SQL then looks as follows:

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;

In this case, the script does not return an error when the command has already been executed and also manages to rollback the whole script when running into the typo error again. However, the exit code 10 is never output, result always returns 0 and there is no evidence that an error occurred besides the fact that the previous operation was rolled back.

I tried every configuration of WHENEVER SQLERROR I could think of, I tried an EXCEPTION block in the SQL script where I tried to insert the error into a table, I tried surrounding the shell script that calls the SQL script with try and catch, nothing works. The script always runs as if it never encountered a problem. As far as I can tell, there is an issue with the begin/end block but I haven’t figured out another way to include the checks so it seems I am dependent on it. For some reason, using dbms_output.put_line() inside the block also doesn’t work (tried using set serveroutput on, set echo on, etc. and adding returnStdout: true to the executing shell script).

You need to sign in to view this answers