How to run custom SQL query in Jenkins?

Prerequisites

1. Install SQL Plus Script Runner Plugin
2. Database/Schema to connect through Jenkins

Install SQL PLUS Script Runner Plugin

In Plugin's Go to "Available"

Search for "sqlplus script runner"

Click check box and select "Install without restart". You should see

On Jenkins home screen click on "New Item" or "Create new jobs"

Enter an item name - In my case "Test SQL custom script" and choose "Freestyle project"

Click "OK"

We should see below screen. Go to "Build" tab.

Click on "Add build step" under Build and Select "SQLPLUS Script Runner"

Click on "Add" and "Jenkins" to add Credentials.

We need to provide Username, Password, Id and Description and Click "Add"

Now we should see added credentials. Now fill rest of the fields 

Instance
Script Type to run - User Defined Script
User Defined Script - select name from v$database
Custom ORACLE_HOME - C:\app\oracle\product\19.0.0\client_1
Custom SQL*Plus location (sqlplus.exe or sqlplus) - C:\app\oracle\product\19.0.0\client_1\bin\sqlplus.exe
Custom TNS_ADMIN - C:\app\oracle\product\19.0.0\client_1\network\admin

Click "Save"

Now click on "Build Now" from left hand side panel.

Now click on "Last Build (#2) under "Permalinks". In my case I ran build w times so #2

Click on "Console Output"

Build succeeded with output "ORCL1" with status "SUCCESS".

[code] Running as SYSTEM Building in workspace C:\Program Files (x86)\Jenkins\workspace\Test SQL custom script -------------------------------------------------------------------------- Getting ORACLE_HOME... Using custom SQL*Plus location SQL*Plus >>> C:\app\oracle\product\19.0.0\client_1\bin\sqlplus.exe Using custom TNS_ADMIN location TNS_ADMIN >>> C:\app\oracle\product\19.0.0\client_1\network\admin -------------------------------------------------------------------------- Using custom ORACLE_HOME -------------------------------------------------------------------------- Using ORACLE_HOME =C:\app\oracle\product\19.0.0\client_1 -------------------------------------------------------------------------- Getting SQLPlus version [Test SQL custom script] $ C:\app\oracle\product\19.0.0\client_1\bin\sqlplus.exe -v SQL*Plus: Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Process exited with status 0 -------------------------------------------------------------------------- -------------------------------------------------------------------------- Using ORACLE_HOME =C:\app\oracle\product\19.0.0\client_1 -------------------------------------------------------------------------- Running defined script on system/********@orcl11 Temp script: file:/C:/Windows/TEMP/temp-script-15804813977375511313287591200340.sql -------------------------------------------------------------------------- [Test SQL custom script] $ C:\app\oracle\product\19.0.0\client_1\bin\sqlplus.exe -L system/oracle@orcl11 @C:\Windows\TEMP\temp-script-15804813977375511313287591200340.sql SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 31 08:36:37 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Fri Jan 31 2020 07:49:46 -06:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 NAME --------- ORCL1 1* select name from v$database Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 Process exited with status 0 -------------------------------------------------------------------------- Finished: SUCCESS [/code]