-1

In my jenkinsfile when i executing the below sql statement and returning the returnStatus , whether sql statement pass or fail it always returns "0"

def user_code= sh(script: """sqlplus "a/b/c/test.sql" """, returnStatus: true)
if ( "${user_code}" != "0") {
    error("sql statement failed with status code ${user_code}")
} else {
 println "successfull"
}
}

I referred to this page Sql*plus always returns exit code 0? where it suggest to handle this in SQL Query but i have limitation, I have to implement it in Jenkinsfile.

  1. I have tried returnStdout: true as well but it throw junk of output.

Can someone please suggest how i can handle this in jenkinsfile.

bamishr
  • 410
  • 1
  • 6
  • 23

1 Answers1

0

You may try to generate new sql with lines below in the header to in the beginning of your test.sql

WHENEVER SQLERROR EXIT SQL.SQLCODE
WHENEVER OSERROR EXIT

sh(script: """sqlplus "a/b/c/test_with_header.sql" """, returnStatus: true)
Arkon88
  • 180
  • 5
  • Thank you for your response, but as I previously stated in my question, I do not own the database files. As a result, I'm unable to add any SQL statements or files. – bamishr Oct 14 '21 at 21:01
  • Below is how it works in our system, as to help i would like to get details of your team implementation 1. Jenkins build runs using docker (with all installed programs and test set of data) 2. We download and deploy all src files on the docker instance using Maven+Liquebase 3. Run all tests for DB To run them we have functions where we are passing sqls only To these sqls we are adding set of the statements for sqlplus and running modified query. Function looks like db_call('select * from dual') and gives output + error code. So access to file system you should have – Arkon88 Oct 15 '21 at 04:12