Run SQL as JENKINS_PIPELINE_job by SQL plus script runner plugin.
Running the SQL script – As Scripted pipeline job from Jenkins file in GIT repo
Note: the SQL script placed in the same location as Jenkins file
|
1 2 3 4 5 6 7 8 9 10 11 12 |
//Pipeline code (scripted pipe) in Jenkins file in GIT repo // node ('master') { stage('Code_checkout') { git url: 'https://github.com/mshan0181/java-sample-code.git' } stage('SQL_script_runner') { echo 'SQLPlusRunner running user define script for shandba@client_access' step([$class: 'SQLPlusRunnerBuilder',credentialsId:'shandb-priv-id', instance:'client_access',scriptType:'userDefined', script: '',scriptContent: 'select * from v$version']) step([$class: 'SQLPlusRunnerBuilder',credentialsId:'shandb-priv-id', instance:'client_access',scriptType:'userDefined', script: '',scriptContent: 'select name from v$database']) step([$class: 'SQLPlusRunnerBuilder',credentialsId:'shandb-priv-id', instance:'client_access',scriptType:'file', script: 'switchover_status.sql',scriptContent: '']) } } |
👉 Create the (above code) scripted pipeline in Jenkins file in GIT repo. 
👉 SQL file also placed in the same GIT repo as Jenkins file.
👉 Configure the pipeline from SCM (GIT repo) as Jenkins file.
👉 Jenkins file location is provided as part of the configuration:
👉 Build the job.
👉 Check the console output:
👉 Console output: continued
Running the SQL script – As Declarative pipeline job from Jenkins file in GIT repo
Note: The SQL script placed in the different Repo-location and different BRANCH and not same as Jenkins file
▪️ Create the Jenkins file with Declarative pipeline code.
▪️ The pipeline code has the credential ID step—in GIT checkout code stage.
▪️ The GitHub token (Token) has to be created and has to be used for creation of Credential ID in the Jenkins
▪️ The GitHub token will be used as PASSWORD (for username/password kind) to create the Credential ID.
Note: GitHub token creation is not covered in this document.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
//Pipeline code (Declarative pipe) in Jenkins file in GIT repo // pipeline { agent { label 'master' } stages { stage('Git Checkout') { steps { script { git branch: 'main', credentialsId: 'github_user_credential', url: 'https://github.com/mshan0181/SQL_files.git' } } } stage('SQL-script-runner') { steps { step([$class: 'SQLPlusRunnerBuilder',credentialsId:'shandb-priv-id', instance:'client_access',scriptType:'userDefined', script: '',scriptContent: 'select * from v$version']) step([$class: 'SQLPlusRunnerBuilder',credentialsId:'shandb-priv-id', instance:'client_access',scriptType:'userDefined', script: '',scriptContent: 'select name from v$database']) step([$class: 'SQLPlusRunnerBuilder',credentialsId:'shandb-priv-id', instance:'client_access',scriptType:'file', script: 'tablespace_usage_listing.sql',scriptContent: '']) } } } } |
👉 Create the Pipeline script (above code) in Jenkins file in GitHub REPO location:
👉 SQL script file stored in different location and different Branch (master)
👉 Build the job:
👉 Check the console output:
👉 Console output: continued
👉 Console output: continued
Jenkins job status showing success –even SQL failed with error inside SQL plus.
▪️ The Jenkins job show the job status as SUCCESS even when the SQL error is thrown.
▪️ We need to make Jenkins job to FAIL if there is any SQL error message is thrown inside SQLPLUS runner.
▪️ We need to add a piece of code at the start of the SQL file
WHENEVER SQLERROR EXIT SQL.SQLCODE
▪️ Start the fresh job after adding this code in the start of the SQL file.
▪️ The Jenkins job will FAIL caused due to SQL error.
-
▪️ By this we can avoid the FALSE success message from the Jenkins console.
Example code:
|
1 |
WHENEVER SQLERROR EXIT SQL.SQLCODEUPDATE EMP_DETAILS_VIEW SET SALARY = SALARY*1.1; |
👉 Configure the SQL plus Runner job – for correct SQL with syntax issue
👉Build the SQL Plus runner job.
👉Console output of job
👉Console output of job: continued
Author : Venkat Vinod Kumar Siram
LinkedIn : https://www.linkedin.com/in/vinodsiram/
Assisted by Shanmugavel
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates.
KTExperts is always active on social media platforms.
Facebook : https://www.facebook.com/ktexperts
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Note: Please test scripts in Non Prod before trying in Production.
Loading...
