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 have multiple issues:
You cannot ROLLBACK a DDL statement as all DDL statements implicitly COMMIT before and after the statement so there never going to be anything to ROLLBACK - you will still get the exit code on an error but you will not revert any changes.
The filter WHERE upper(TABLE_NAME)= 'employee' and upper(COLUMN_NAME) = 'employee_id' is never going to match anything as you are comparing upper-case to lower-case literals.
You want to use WHERE TABLE_NAME = 'EMPLOYEE' and COLUMN_NAME = 'EMPLOYEE_ID'
select NULLABLE
INTO phone_number_nullable
FROM all_tab_columns
WHERE upper(TABLE_NAME)= 'employee'
and upper(COLUMN_NAME) = 'phone_number';
Is going to fail due to the case mismatch in the WHERE filters which then causes zero rows to be matched and the SELECT ... INTO ... raises a NO_DATA_FOUND exception.
SQL*Plus is helpful and can silently consume NO_DATA_FOUND errors (as having no data is a typical result for some queries). However, in your case this can act to hide the error.
You are selecting from ALL_TAB_COLUMNS and not filtering on owner.
Fix the errors:
DECLARE
id_exists PLS_INTEGER;
phone_number_not_nullable PLS_INTEGER;
BEGIN
select count(*)
INTO id_exists
FROM user_tab_cols
WHERE TABLE_NAME = 'EMPLOYEE' and COLUMN_NAME = 'EMPLOYEE_ID';
IF id_exists = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE employee ADD employee_id NUMBER(13)';
END IF;
SELECT COUNT(*)
INTO phone_number_not_nullable
FROM user_tab_columns
WHERE TABLE_NAME = 'EMPLOYEE'
AND COLUMN_NAME = 'PHONE_NUMBER'
AND NULLABLE = 'N';
IF phone_number_not_nullable = 1 THEN
EXECUTE IMMEDIATE 'ALTER TABLE enployee MODIFY phone_number NULL';
END IF;
END;
/
And it raises the error:
ORA-00942: table or view does not exist
ORA-06512: at line 21
(However, it still will not ROLLBACK the added column because that is something that cannot be rolled back.)