Run SQL as JENKINS_UI_job by SQLplus script runner plugin

Run SQL as JENKINS_UI_job by SQLplus script runner plugin

To perform SQL operations connecting to Remote Oracle DB without SSH connection.

This article uses the Jenkins master node to run/execute the SQL.

▪️ SQL plus Script Runner plugin is required to run the SQL code from Jenkins job.

▪️ The Oracle Client software location (ORACLE_HOME) and SQL Plus exe-file location is earlier configured with SQL Plus Script runner in Jenkins tool to run the SQL.

▪️ The SQL/Query will access/fetch the database on Remote Oracle DB Server

▪️ SQL code is executed directly from Master node (using TNS entry of the remote DB)

NOTE:

▪️ We don’t require the SSH to connect/access the remote DB.

▪️ We will use only the TNS entry to connect/access the remote DB.

System Configuration details: 

IMPORTANT NOTE: 

▪️ The SQL code can also be executed from SLAVE node as well, provided the Oracle Client software to be installed in slave machine.

▪️ In this article we have used Master node to execute the SQL code.

Tools required:

▪️ Oracle client software (in Master node)

▪️ SQL Runner plugin (in Jenkins)

Install Oracle Database client (Install Steps not covered)

Configure the TNS connection in Oracle Client home:

Test connection to Remote Oracle Database (Using Client):

SQL plus Script Runner:

▪️ This plugin enables you run Oracle SQL*Plus scripts on your Jenkins jobs (user defined scripts or a script inside a workspace).

▪️ Oracle SQL*Plus installation requires Java version 11.

Java version Dependency Chart: for Plugin versions

plugin version	Java version	description
3.x	11	Java 11 +
2.x	8	For mainly Jenkins 2.x, credentials plugins implemented and pipeline support.
1.x	7	– For Jenkins 1.x

 

 

 

 

 

 

SQLPLUS runner à Installation

Configure SQL plus Script Runner (GLOBAL default settings for any job, where the path is not mentioned)

▪️ The Jenkins job will pick this GLOBAL default settings for SQL plus exe file and the ORACLE_HOME location for client.

▪️ The Jenkins job can also have customized settings/location for SQL plus exe file and ORACLE_HOME location.

▪️ In such case the customized setting will override the GLOBAL settings during the job execution.

GLOBAL setting for any jobs – SQL Plus Runner:

Running the SQL script – as freestyle job from Jenkins UI A) Script type: File script

👉 Configuration of Jenkins job: where to run.

👉 Configuration of Source code management:

👉 Configuration of SQL plus script Runner: ORACLE_HOME and SQL plus exe file location

👉 Configure – continue.

👉 SQL file Script placed at Workspace location in Jenkins server:

👉 Build the job:

👉 Console output of the Job

👉 Console output: continued

B) Script type: User defined script

👉 Configuration of SQL Script runner: User defined script (Script type)

👉 Console output of job: user defined script job

Running the SQL script – as pipeline job from UI

Note: Here we have used the scripted pipeline in UI

👉 Configure the job:

👉 Place the SQL script file in the Workspace location/jobname:

//Pipeline code (scripted pipe) for UI //

 

👉 Configure the (above code) pipeline script in UI (provide the SQL runner step in the code):

👉 Run the job and check the console output.

👉 Console output: 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