Jenkins
Space shortcuts
Child pages
  • SQLPlus Script Runner Plugin

Due to some maintenance issues, this service has been switched in read-only mode, you can find more information about the why

and how to migrate your plugin documentation in this blogpost

Skip to end of metadata
  • Created by Unknown User (boaglio), last modified on Oct 03, 2018
Go to start of metadata

Plugin Information

View SQLPlus Script Runner on the plugin site for more information.

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

Releases

  • 1.x - For Jenkins 1.x
  • 2.x - For mainly Jenkins 2.x, credentials plugins implemented and pipeline support


Migration Guide

To migrate your workspace from version 2 is very simple, all you need to do is:

  1. Create a credential for your user and password;
  2. Edit you job configuration and select one credential;
  3. Save your changes and you are good to go!



Summary

This plugin enables you run Oracle SQL*Plus scripts on your Jenkins jobs ( Oracle SQL*Plus installation required! ).

Configuration

Global

All you have to do is check the auto detect ORACLE_HOME option ( or a valid ORACLE_HOME ) and you are in business:



If you are having problems try to enable Debug info to see what is going on.

By Job

You can run a script inside your workspace or a user defined for every job:


Version 1.x:


 


Version 2.x :


Environment variables


Pay attention to what version you are using in order to work with variables:


variableVersion 1.xVersion 2.x
user  okreplaced by credentials
password okreplaced by credentials
instanceokok



You can use global variables for user and instance, first creating it in Jenkins→ configuration :


You user global variable can hold username and password as well like this (username/password):



And then using it:

Running

You can check later all SQL*Plus output inside your build output:


Pipeline


User defined script


node {
   echo 'SQLPlusRunner running user define script for system@xe'
  
 step([$class: 'SQLPlusRunnerBuilder',credentialsId:'system', 
instance:'xe',scriptType:'userDefined', script: '',scriptContent: 
'select * from v$version'])
}



File script


node {
   echo 'SQLPlusRunner running file script for system@xe'
  
 step([$class: 'SQLPlusRunnerBuilder',credentialsId:'system', 
instance:'xe',scriptType:'file', script: 'start.sql',scriptContent: ''])
}



Optional pipeline parameters


  • customOracleHome
  • customSQLPlusHome
  • customTNSAdmin



Download the last release and give it a try!

Reported problems


my script takes forever to execute...


Windows users sometimes get a running script stuck on build, even though they run everything on Oracle.

In that case you should use Build Time Out plugin together to get everything working fine.


I have an error ORA-????


You should try running manually SQL*Plus before use Jenkins.

Sometimes on Linux Jenkins has his own Operation System user with his own variables.


Every time I get this error: cannot find sqlplus


Jenkins plugin can't find SQL*Plus executable file.

You should do in this order:

  1. Stop Jenkins;
  2. Find where are sqlplus executable file (usually at $ORACLE_HOME);
  3. Check if they have proper permissions (chmod 755 *);

  4. Create a global environment variable ORACLE_HOME like this:
    export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1/    or   export ORACLE_HOME=<your-sqlplus-directory>

  5. Test your environment with:  sqlplus -v    , should return SQL*Plus version like SQL*Plus: Release 12.1.0.1.0 Production

  6. Start Jenkins;

  7. Use the try to detect ORACLE_HOME option;

  8. Retry your SQL*Plus operation.


Every time I get this error: libsqlplus.so: cannot open shared object file: No such file or directory or SP2-0667: Message file sp1.msb not found


SQL*Plus need its library and its message file to work properly.

You should do in this order:

  1. Stop Jenkins;
  2. Find where are libsqlplus.so files (usually at $ORACLE_HOME\lib );
  3. Check if they have proper permissions (chmod 755 *);

  4. Create a global environment variable LD_LIBRARY_PATH like this:
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH    or   export LD_LIBRARY_PATH=<your-libsqlplus-directory>:$LD_LIBRARY_PATH

  5. Test your environment with:  sqlplus -v    , should return SQL*Plus version like SQL*Plus: Release 12.1.0.1.0 Production

  6. Start Jenkins;

  7. Use the try to detect ORACLE_HOME option;

  8. Retry your SQL*Plus operation.


I want to hide my user / password from Console Output


  1. Install Mask Passwords Plugin;

  2. Setup to hide everything you want.




I have a bug to report

Please open a new issue and inform:

  • Jenkins server Operation System;
  • Jenkins version ;
  • Where SQLPlus Script Runner is running (local machine or slave machine);
  • Slave machine Operation System (if applicable);
  • Oracle Database version;
  • Oracle SQL*Plus version;
  • Build log with debug info enabled.

 

73 Comments

  1. Unknown User (dotbombjoe)

    This seems to assume the jobs will run on the master, which I wouldn't think is a common implementation approach.  Am I wrong?  I just don't see any way change the directory that the plugin seems to be using when executing the job -- it isn't the workspace directory on the slave, it is assuming the job path on the master.  I'll peek at the code and see if I can suggest/make the change but maybe I'm just missing something.

    I really like the idea though as my current approach of piping in SQL statements via a shell script is ugly -- this plugin will make the scripts much easier to understand.

    1. Unknown User (lkouts)

      Have you had any luck with your research? I've just installed this plugin and noticed that same dilemma.

    2. Unknown User (boaglio)

      Hi, the release 1.0.2 run scripts on slave too.

      1. Unknown User (uketkar)

        How do you do that in 1.0.2?

        1. Unknown User (boaglio)

          You can try with this plugin: https://wiki.jenkins-ci.org/display/JENKINS/NodeLabel+Parameter+Plugin

          1. Unknown User (uketkar)

            This works like a charm. Thanks.

  2. Unknown User (chitownalex)

    Is there a way to pull in a build parameter for the file script?

    1. Unknown User (boaglio)

      Sorry, no.

    2. Unknown User (arip)

      My workaround for this is to use Ant's replacetoken function.

  3. Unknown User (maxi4)

    hi!

    how to manipulate exit code of plugin?

    for example, i need:

    SELECT *  FROM MENU
    EXCEPTION WHEN NO_DATA_FOUND THEN exit 1;

    exit;

    This script returned 0, but must return 1 for Failed job status

    1. Unknown User (boaglio)

      Sorry, no way to manipulate exit code of plugin

      1. Unknown User (maxi4)

        Hi Fernando, thank you for your answer!

        so, what is appropriate way to know the result of sql request in the job status?

        1. Unknown User (boaglio)

          Maybe print some stuff with prompt and use a Groovy plugin to read the result and fail the job.

  4. Unknown User (aaa123)

    Hi,

    Sorry, is it me or this plugin is not available from Jenkins "Manage Plugins -->Available" for me to install it?

    thanks

    H.

    1. Unknown User (boaglio)

      This plugin is 1.625.2 based, maybe if you have an older version of Jenkins that could explain it.

  5. Unknown User (jay8127)

    Hi,

    If I want to execute sql scripts filtered from bitbucket/git, can I use this plugin for it? If yes, how?

    Also, can I use this plugin just for Oracle or any Database like Teradata too?

    Thanks and Regards,

    Jaimin Gandhi.

    1. Unknown User (boaglio)

      Yes you can execute scripts from any version-control system (SVN,Git,Github,Bitbucket...) , just use a relative path to inform your script directory. You can use sql\update.sql  and then put your scripts inside this directory in your VCS.

      This plugin is Oracle only.

      (thumbs up)

  6. Unknown User (nigam_indian)

    How can we pass the Jenkins Parameters to SqlPlus section?

    I have various choice parameters that I need to pass as variable to sqlplus and I am using "User Defined Script".

    Thanks,

    Anoop

    1. Unknown User (boaglio)

      I don't follow, please give me a couple of examples of what you need.

      1. Unknown User (nigam_indian)

        I have a parameterized build, in which I am passing few paraters in Jenkins job run ( mostly drop-down/choice parameters). In the job, I need to use these parameter values in the "where" clause of the "select" query.

        E.g. I have a drop-down called Country with values UK, USA, INDIA, Belgium.

        The query I need to run in "SQLPlus Script Runner" section is "select cities from my_table where country = <option selected from the drop-down while triggering build>"

        How can I get this value captured in "SQLPlus Script Runner" section?

        Thanks & Regards,

        Anoop

        1. Unknown User (boaglio)

          Ok, I understand it, but using a parameter probably won't work, sorry.

  7. Unknown User (rsc)

    I have an SQL script that spools around 500KB of data. While it works if I run the the tmp-script that SQLPlus Script Runner generates manually by calling sqlplus from a command line, it does not work when running through Script Runner. The Job console Screen hangs forever and the spool file stops at exactly 72KB all the time. If I kill the slave JRE process it'll spool the rest of the data. Is there a reason why Script Runner waits at 72KB? Should I create a JIRA issue for that?

    1. Unknown User (boaglio)

      please use new version 1.0.11

  8. Unknown User (sweetarpit2003)

    when I am trying to run a select statement using this plugin the build keeps on running for infinite amount of time.

    the logs shows:

    then the select query is printed to one of the file in C:\Windows\Temp folder and it never gives a result.

    Any help if I am configuration the plugin correctly? 

  9. Unknown User (boaglio)

    Have you tested running your script directly with SQL*Plus ?

    Maybe some statement got an error and the SQL*Plus hangs.

    1. Unknown User (sweetarpit2003)

      Yes, I tested with SQL*Plus and it is giving expected output I am just running a select query to start with

      Please find below the logs from Jenkins, it is not moving ahead this point:

       

      Started by user Arpit Khandelwal
      Building in workspace E:\workspace\ST01 Row Count
      --------------------------------------------------------------------------
      Getting ORACLE_HOME...
      --------------------------------------------------------------------------
      Using global ORACLE_HOME
      --------------------------------------------------------------------------
      Using ORACLE_HOME =C:\Oracle\11\product\11.2.0\clientx64
      --------------------------------------------------------------------------
      Getting SQLPlus version
      Process exited with status 0

      SQL*Plus: Release 11.2.0.4.0 Production

      --------------------------------------------------------------------------
      --------------------------------------------------------------------------
      Using ORACLE_HOME =C:\Oracle\11\product\11.2.0\clientx64
      --------------------------------------------------------------------------
      Running defined script on z869365[ALFACP]/********@alfapft.world
      Temp script:C:\WINDOWS\TEMP\temp-script-14937180968032921288542573770985.sql
      --------------------------------------------------------------------------

  10. Unknown User (boaglio)

    Maybe there is something about your password.

    This plugin uses this syntax:  sqlplus user/password@database script.sql

    You should try running a user defined script as well.

    1. Unknown User (sweetarpit2003)

      I am able to run user defined script now, but when I run file based script it is not showing any results and build keeps on running forever. Logs:

       

      --------------------------------------------------------------------------
Using ORACLE_HOME =C:\Oracle\11\product\11.2.0\clientx64
--------------------------------------------------------------------------
Running script E:\workspace\ST01 Row Count\check_all_results.sql em z869365[ALFACP]/********@alfapc1t.world

SQL*Plus: Release 11.2.0.4.0 Production

--------------------------------------------------------------------------

      1. Unknown User (boaglio)

        Unknown User (sweetarpit2003) I don't know what is going on , please open a ticket providing all required info: 

        • Jenkins server Operation System;
        • Jenkins version ;
        • Where SQLPlus Script Runner is running (local machine or slave machine);
        • Slave machine Operation System (if applicable);
        • Oracle Database version;
        • Oracle SQL*Plus version.

        https://github.com/jenkinsci/sqlplus-script-runner-plugin/issues/new

  11. Unknown User (andryi)

    Hello

    I have a problem, when I run " select sysdate from dual" the log says:

     

    Getting ORACLE_HOME...
--------------------------------------------------------------------------
Using custom ORACLE_HOME
--------------------------------------------------------------------------
Using ORACLE_HOME =C:\oracle\product\11.2.0\client_1\sqlplus.exe
--------------------------------------------------------------------------
Getting SQLPlus version
Error:Cannot run program "C:\oracle\product\11.2.0\client_1\sqlplus.exe\bin\sqlplus.exe": CreateProcess error=2, El sistema no puede encontrar el archivo especificado
java.lang.RuntimeException: java.io.IOException: Cannot run program "C:\oracle\product\11.2.0\client_1\sqlplus.exe\bin\sqlplus.exe": CreateProcess error=2, El sistema no puede encontrar el archivo especificado
	at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunner.runGetSQLPLusVersion(SQLPlusRunner.java:302)
	at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunner.invoke(SQLPlusRunner.java:139)
	at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunner.invoke(SQLPlusRunner.java:16)
	at hudson.FilePath.act(FilePath.java:996)
	at hudson.FilePath.act(FilePath.java:974)
	at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunnerBuilder.perform(SQLPlusRunnerBuilder.java:98)
	at hudson.tasks.BuildStepMonitor$1.perform(BuildStepMonitor.java:20)
	at hudson.model.AbstractBuild$AbstractBuildExecution.perform(AbstractBuild.java:779)
	at hudson.model.Build$BuildExecution.build(Build.java:206)
	at hudson.model.Build$BuildExecution.doRun(Build.java:163)
	at hudson.model.AbstractBuild$AbstractBuildExecution.run(AbstractBuild.java:534)
	at hudson.model.Run.execute(Run.java:1728)
	at hudson.model.FreeStyleBuild.run(FreeStyleBuild.java:43)
	at hudson.model.ResourceController.execute(ResourceController.java:98)
	at hudson.model.Executor.run(Executor.java:405)
Caused by: java.io.IOException: Cannot run program "C:\oracle\product\11.2.0\client_1\sqlplus.exe\bin\sqlplus.exe": CreateProcess error=2, El sistema no puede encontrar el archivo especificado
	at java.lang.ProcessBuilder.start(Unknown Source)
	at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunner.runGetSQLPLusVersion(SQLPlusRunner.java:290)
	... 14 more

     

    So my question is, why the tool is searching in 

    "C:\oracle\product\11.2.0\client_1\sqlplus.exe\bin\sqlplus.exe"

    If I put "

    C:\oracle\product\11.2.0\client_1\sqlplus.exe"

  12. Unknown User (boaglio)

    Unknown User (andryi)

    ORACLE_HOME is the PATH where Oracle Client is installed, you should inform only  C:\oracle\product\11.2.0\client_1\

  13. Unknown User (andryi)

    Hi Unknown User (boaglio)

    Thank's for your reply, I tried again but I have this message

    Getting ORACLE_HOME...
--------------------------------------------------------------------------
Using custom ORACLE_HOME
--------------------------------------------------------------------------
Using ORACLE_HOME =C:\oracle\product\11.2.0\client_1
--------------------------------------------------------------------------
Getting SQLPlus version
Error:Cannot run program "C:\oracle\product\11.2.0\client_1\bin\sqlplus.exe": CreateProcess error=2, El sistema no puede encontrar el archivo especificado
java.lang.RuntimeException: java.io.IOException: Cannot run program "C:\oracle\product\11.2.0\client_1\bin\sqlplus.exe": CreateProcess error=2, El sistema no puede encontrar el archivo especificado
	at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunner.runGetSQLPLusVersion(SQLPlusRunner.java:302)
	at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunner.invoke(SQLPlusRunner.java:139)
	at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunner.invoke(SQLPlusRunner.java:16)
	at hudson.FilePath.act(FilePath.java:996)
	at hudson.FilePath.act(FilePath.java:974)
	at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunnerBuilder.perform(SQLPlusRunnerBuilder.java:98)
	at hudson.tasks.BuildStepMonitor$1.perform(BuildStepMonitor.java:20)
	at hudson.model.AbstractBuild$AbstractBuildExecution.perform(AbstractBuild.java:779)
	at hudson.model.Build$BuildExecution.build(Build.java:206)
	at hudson.model.Build$BuildExecution.doRun(Build.java:163)
	at hudson.model.AbstractBuild$AbstractBuildExecution.run(AbstractBuild.java:534)
	at hudson.model.Run.execute(Run.java:1728)
	at hudson.model.FreeStyleBuild.run(FreeStyleBuild.java:43)
	at hudson.model.ResourceController.execute(ResourceController.java:98)
	at hudson.model.Executor.run(Executor.java:405)
Caused by: java.io.IOException: Cannot run program "C:\oracle\product\11.2.0\client_1\bin\sqlplus.exe": CreateProcess error=2, El sistema no puede encontrar el archivo especificado
	at java.lang.ProcessBuilder.start(Unknown Source)
	at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunner.runGetSQLPLusVersion(SQLPlusRunner.java:290)
	... 14 more
Caused by: java.io.IOException: CreateProcess error=2, El sistema no puede encontrar el archivo especificado
	at java.lang.ProcessImpl.create(Native Method)
	at java.lang.ProcessImpl.<init>(Unknown Source)
	at java.lang.ProcessImpl.start(Unknown Source)
	... 16 more
ERROR: java.io.IOException: Cannot run program "C:\oracle\product\11.2.0\client_1\bin\sqlplus.exe": CreateProcess error=2, El sistema no puede encontrar el archivo especificado
Finished: FAILURE

     

    Because, in my PC the sqlplus.exe is in "C:\oracle\product\11.2.0\client_1\" not in "C:\oracle\product\11.2.0\client_1\bin\", what can I do?  

    1. Unknown User (boaglio)

      Well, doesn't look like a standard Oracle Installation ...

      Can you create a directory C:\oracle\product\11.2.0\client_1\bin\ and copy sqlplus.exe inside it ?

      1. Unknown User (andryi)

        Hi Unknown User (boaglio), sorry for the delay

        Now I have this message

         

        Using custom ORACLE_HOME
--------------------------------------------------------------------------
Using ORACLE_HOME =C:\oracle\product\11.2.0\client_1
--------------------------------------------------------------------------
Getting SQLPlus version
Process exited with status 0

SQL*Plus: Release 11.2.0.1.0 Production

--------------------------------------------------------------------------
java.lang.RuntimeException: Unable to read SQL script [C:\Program Files (x86)\Jenkins\workspace\test02]  !
	at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunner.invoke(SQLPlusRunner.java:152)
	at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunner.invoke(SQLPlusRunner.java:16)
	at hudson.FilePath.act(FilePath.java:996)
	at hudson.FilePath.act(FilePath.java:974)
	at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunnerBuilder.perform(SQLPlusRunnerBuilder.java:98)
	at hudson.tasks.BuildStepMonitor$1.perform(BuildStepMonitor.java:20)
	at hudson.model.AbstractBuild$AbstractBuildExecution.perform(AbstractBuild.java:779)
	at hudson.model.Build$BuildExecution.build(Build.java:206)
	at hudson.model.Build$BuildExecution.doRun(Build.java:163)
	at hudson.model.AbstractBuild$AbstractBuildExecution.run(AbstractBuild.java:534)
	at hudson.model.Run.execute(Run.java:1728)
	at hudson.model.FreeStyleBuild.run(FreeStyleBuild.java:43)
	at hudson.model.ResourceController.execute(ResourceController.java:98)
	at hudson.model.Executor.run(Executor.java:405)
ERROR: Unable to read SQL script [C:\Program Files (x86)\Jenkins\workspace\test02]  !
Finished: FAILURE

         

        r

         

        1. Unknown User (andryi)

          Oh my bad, I should choose "User defined script", and with that, it works!!!! =D

          thank's Unknown User (boaglio)

      2. Unknown User (tiffanyfb)

        I'm having the same problem.  It's standard for Oracle client to not put sqlplus under bin.  I tried copying everything under the client directory to bin, but now I'm getting this error.  

        09:42:09 Using ORACLE_HOME =/opt/app/oracle/product/12.1.0.2/client
09:42:09 /opt/app/oracle/product/12.1.0.2/client/bin/sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

        I've tried various ways to set LD_LIBRARY_PATH, but the variable appears to get wiped when this plugin runs.

         

        1. Unknown User (boaglio)

          Hi Tiffany,

          Maybe your Jenkins is running on a different user that you are testing on.

          LD_LIBRARY_PATH it should be $ORACLE_HOME\lib , where have files like libsqlplus.so .

          Can you please give me a list of all your files inside /opt/app/oracle/product/12.1.0.2/client ?

           

          1. Unknown User (tiffanyfb)

            The file is present.  The plugin just can't seem to find it.

            [oracle@webjenkins client]$ ll /opt/app/oracle/product/12.1.0.2/client
total 193340
-rwxrwxr-x 1 oracle oinstall     29404 Jul  7  2014 adrci
-rw-rw-r-- 1 oracle oinstall       440 Jul  7  2014 BASIC_README
drwxr-xr-x 2 oracle oinstall      4096 Jun 21 12:36 bin
-rwxrwxr-x 1 oracle oinstall     43944 Jul  7  2014 genezi
-r-xr-xr-x 1 oracle oinstall       342 Jul  7  2014 glogin.sql
-rwxrwxr-x 1 oracle oinstall   6990875 Jul  7  2014 libclntshcore.so.12.1
lrwxrwxrwx 1 oracle oinstall        17 Jun 20 10:03 libclntsh.so -> libclntsh.so.12.1
-rwxrwxr-x 1 oracle oinstall  58793741 Jul  7  2014 libclntsh.so.12.1
-r-xr-xr-x 1 oracle oinstall   1768370 Jul  7  2014 libipc1.so
-r-xr-xr-x 1 oracle oinstall    544150 Jul  7  2014 libmql1.so
-r-xr-xr-x 1 oracle oinstall   6213011 Jul  7  2014 libnnz12.so
lrwxrwxrwx 1 oracle oinstall        15 Jun 20 10:03 libocci.so -> libocci.so.12.1
-rwxrwxr-x 1 oracle oinstall   2576030 Jul  7  2014 libocci.so.12.1
-rwxrwxr-x 1 oracle oinstall 109549133 Jul  7  2014 libociei.so
-r-xr-xr-x 1 oracle oinstall    156353 Jul  7  2014 libocijdbc12.so
-r-xr-xr-x 1 oracle oinstall    337137 Jul  7  2014 libons.so
-rwxrwxr-x 1 oracle oinstall    118491 Jul  7  2014 liboramysql12.so
-r-xr-xr-x 1 oracle oinstall   1564082 Jul  7  2014 libsqlplusic.so
-r-xr-xr-x 1 oracle oinstall   1546540 Jul  7  2014 libsqlplus.so
-r--r--r-- 1 oracle oinstall   3692096 Jul  7  2014 ojdbc6.jar
-r--r--r-- 1 oracle oinstall   3698857 Jul  7  2014 ojdbc7.jar
-r-xr-xr-x 1 oracle oinstall      9581 Jul  7  2014 sqlplus
-rw-rw-r-- 1 oracle oinstall       444 Jul  7  2014 SQLPLUS_README
-rwxrwxr-x 1 oracle oinstall    227410 Jul  7  2014 uidrvci
-rw-rw-r-- 1 oracle oinstall     71202 Jul  7  2014 xstreams.jar

            1. Unknown User (pedro_c83)

              Hi Unknown User (boaglio),

              I have the same issue as Tiffany:
              error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

              Files in correct place, correct user, correct permissions...

              Can you please help?

              1. Unknown User (boaglio)

                Please take a look at our updated Reported Problems guide at the top.

  14. Unknown User (andrei_sar)


    As I rescued the results of the script I run through the function file script, since when I run jenkins tells me that I worked perfectly but internally the script gave me error

    1. Unknown User (boaglio)

      Jenkins tells you what Oracle SQL*Plus tells him, that your script was executed successfully.

  15. Unknown User (sameek_mishra)

    I have to run sql script in remote oracle database using jenkins.I have install jenkins plugin "SQLPlus Script Runner(1.0.9)" and configured. it work well when script file contains less line of sql statement but on my case it's 12000+ line of script to run. it executed few table script then running forever nothing is happening on database.

    Console Log:

    19:06:46 Building on master in workspace C:\Jenkinserver\workspace\NAPF_PRODB
19:06:46 --------------------------------------------------------------------------
19:06:46 Getting ORACLE_HOME...
19:06:46 custom ORACLE_HOME selected
19:06:46 --------------------------------------------------------------------------
19:06:46 Using custom ORACLE_HOME
19:06:46 --------------------------------------------------------------------------
19:06:46 Using ORACLE_HOME =D:\tools\oracleClient
19:06:46 --------------------------------------------------------------------------
19:06:46 Getting SQLPlus version
19:06:46 ORACLE_HOME = D:\tools\oracleClient
19:06:46 LD_LIBRARY_PATH = D:\tools\oracleClient\lib
19:06:46 SQL*Plus exec file = sqlplus.exe
19:06:46 found SQL*Plus on D:\tools\oracleClient
19:06:46 --------------------------------------------------------------------------
19:06:46 Statement:
19:06:46 D:\tools\oracleClient\\sqlplus.exe -v  
19:06:46 --------------------------------------------------------------------------
19:06:46 Process exited with status 0
19:06:46 --------------------------------------------------------------------------
19:06:46 testing directory D:\tools\oracleClient
19:06:46 
19:06:46 SQL*Plus: Release 12.1.0.2.0 Production
19:06:46 
19:06:46 --------------------------------------------------------------------------
19:06:46 Using ORACLE_HOME =D:\tools\oracleClient
19:06:46 --------------------------------------------------------------------------
19:06:46 Running script C:\Jenkinserver\workspace\NAPF_PRODB\NAPF_SERVER_SOURCE\databasescript\DDL.sql em ZENKIN_USER1/********@10.254.6.198:1521/NAPFDB
19:06:46 testing script C:\Jenkinserver\workspace\NAPF_PRODB\NAPF_SERVER_SOURCE\databasescript\DDL.sql
19:06:46 --------------------------------------------------------------------------
19:06:46 ORACLE_HOME = D:\tools\oracleClient
19:06:46 LD_LIBRARY_PATH = D:\tools\oracleClient\lib;D:\tools\oracleClient
19:06:46 found TNSNAMES.ORA on D:\tools\oracleClient\network\admin
19:06:46 TNS_ADMIN = D:\tools\oracleClient\network\admin
19:06:46 Work Directory = C:\Jenkinserver\workspace\NAPF_PRODB
19:06:46 SQL*Plus exec file = sqlplus.exe
19:06:46 found SQL*Plus on D:\tools\oracleClient
19:06:46  Statement: 
19:06:46 --------------------------------------------------------------------------
19:06:46 D:\tools\oracleClient\\sqlplus.exe -L ZENKIN_USER1/ZENKIN_USER1@10.254.6.198:1521/NAPFDB @C:\Jenkinserver\workspace\NAPF_PRODB\NAPF_SERVER_SOURCE\databasescript\DDL.sql --------------------------------------------------------------------------
19:06:46

    Configuration 

    Also i have added "exit;" at the end of sql file but thing is not work.please suggest.

  16. Unknown User (mrinalsan)

    Dear Team,

    I am trying to execute PL/SQL procedure using SQLPlus Script runner , while the same script is getting executed it is going into long running session and it is never coming out.

    Please find below the Script Details is

    ------------------------------------------------------------------

    CREATE OR REPLACE PROCEDURE PR_TRUNCATE_DRA (P_TAB IN VARCHAR2) IS    P_SQL_CODE   VARCHAR2 (30);    P_SQL_ERRM   VARCHAR2 (1000);    V_QUERY      VARCHAR2 (1000); BEGIN    V_QUERY := 'truncate table ' || 'ORBIT_DRA.' || P_TAB || ' DROP STORAGE';

       EXECUTE IMMEDIATE       'truncate table ' || 'ORBIT_DRA.' || P_TAB || ' DROP STORAGE';

       DBMS_OUTPUT.PUT_LINE (V_QUERY || ' is truncated.'); EXCEPTION    WHEN OTHERS    THEN       RAISE; END PR_TRUNCATE_DRA

    ----------------------------------------------------------------------------

     

    Please can you suggest how we can execute / deploy pl/sql package,procedure / function using Jenkins...

     

    Thanks,

    Mrinal Sannyasi

    1. Unknown User (mrinalsan)

      Please Can you help to resolve my problem...

       

      Thanks,

      Mrinal

      1. Unknown User (boaglio)

        I think it's fixed, please use new version 1.0.11.

        If you are in a hurry, you can always pay for CloudBees support.

         

        1. Unknown User (mrinalsan)

          Thank you very much for your response..

          Currently my Jenkins version is Jenkins ver. 2.103 so will this work in the same version (Jenkins ver. 2.103)..

          Or you are talking about any else version here..

          Please respond, Thanks again..

           

          Mrinal

           

          1. Unknown User (boaglio)

            Yes, it will work fine. I was talking about the new SQLPlus Script Runner Plugin version, not the Jenkins version.

            1. Unknown User (mrinalsan)

              Dear Fernando,

               

              After upgradation of SQLPlus Cript Runner Plugin (1.0.11) , we are getting the below error

              It was running fine with older version , please can you suggest what is the reason here...

              Is it because of Java version , IF yes then what is required java version (we are using java version "1.8.0_141")

               

              ===============================================================

              Getting ORACLE_HOME...

              Using custom SQL*Plus location

              SQL*Plus >>>

              Using custom TNS_ADMIN location

              TNS_ADMIN >>>

              [DEBUG] Global ORACLE_HOME selected

              --------------------------------------------------------------------------

              Using global ORACLE_HOME

               detected host = cbl06698dat02

               slave machine ? false

              --------------------------------------------------------------------------

              Using ORACLE_HOME =/dboracle/orabase/product/10.2.0/

              --------------------------------------------------------------------------

              Getting SQLPlus version

              [DEBUG] ORACLE_HOME = /dboracle/orabase/product/10.2.0/

              [DEBUG] LD_LIBRARY_PATH = /dboracle/orabase/product/10.2.0//lib

              SQL*Plus exec file = sqlplus

              --------------------------------------------------------------------------

              [DEBUG] Statement:

               -v 

              --------------------------------------------------------------------------

              [Orbit] $  -v

              Error:Cannot run program "" (in directory "/orbit_serial/jenkins/home/workspace/Orbit"): error=2, No such file or directory

              java.lang.RuntimeException: java.io.IOException: Cannot run program "" (in directory "/orbit_serial/jenkins/home/workspace/Orbit"): error=2, No such file or directory

                      at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunner.runGetSQLPLusVersion(SQLPlusRunner.java:486)

                      at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunner.run(SQLPlusRunner.java:188)

                      at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunnerBuilder.perform(SQLPlusRunnerBuilder.java:112)

                      at hudson.tasks.BuildStepMonitor$1.perform(BuildStepMonitor.java:20)

                      at hudson.model.AbstractBuild$AbstractBuildExecution.perform(AbstractBuild.java:744)

                      at hudson.model.Build$BuildExecution.build(Build.java:206)

                      at hudson.model.Build$BuildExecution.doRun(Build.java:163)

                      at hudson.model.AbstractBuild$AbstractBuildExecution.run(AbstractBuild.java:504)

                      at hudson.model.Run.execute(Run.java:1727)

                      at hudson.model.FreeStyleBuild.run(FreeStyleBuild.java:43)

                      at hudson.model.ResourceController.execute(ResourceController.java:97)

                      at hudson.model.Executor.run(Executor.java:429)

              Caused by: java.io.IOException: Cannot run program "" (in directory "/orbit_serial/jenkins/home/workspace/Orbit"): error=2, No such file or directory

                      at java.lang.ProcessBuilder.start(ProcessBuilder.java:1048)

                      at hudson.Proc$LocalProc.<init>(Proc.java:249)

                      at hudson.Proc$LocalProc.<init>(Proc.java:218)

                      at hudson.Launcher$LocalLauncher.launch(Launcher.java:929)

                      at hudson.Launcher$ProcStarter.start(Launcher.java:449)

                      at hudson.Launcher$ProcStarter.join(Launcher.java:460)

                      at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunner.runGetSQLPLusVersion(SQLPlusRunner.java:477)

                      ... 11 more

              Caused by: java.io.IOException: error=2, No such file or directory

                      at java.lang.UNIXProcess.forkAndExec(Native Method)

                      at java.lang.UNIXProcess.<init>(UNIXProcess.java:247)

                      at java.lang.ProcessImpl.start(ProcessImpl.java:134)

                      at java.lang.ProcessBuilder.start(ProcessBuilder.java:1029)

                      ... 17 more

              ERROR: java.io.IOException: Cannot run program "" (in directory "/orbit_serial/jenkins/home/workspace/Orbit"): error=2, No such file or directory

              SSH: Current build result is [FAILURE], not going to run.

              Finished: FAILURE

              ==========================================================================

              1. Unknown User (mrinalsan)

                Dear Fernando Boaglio,

                After getting this error, I have updated custom oracle home , sqlplus location and TNS_ADMIN path ...

                Then I am getting below error, it is unable to find the Oracle Home path though it is provided... While earlier version was running smoothly...

                PFB the error details...

                 

                ========================================================

                -------------------------------------------------------------------------- Getting ORACLE_HOME... Using custom SQL*Plus location SQL*Plus >>> /dboracle/orabase/product/10.2.0/bin/sqlplus Using custom TNS_ADMIN location TNS_ADMIN >>> /dboracle/orabase/product/10.2.0/network/admin [DEBUG] Using custom ORACLE_HOME -------------------------------------------------------------------------- Using custom ORACLE_HOME  detected host = cbl06698dat02  slave machine ? false -------------------------------------------------------------------------- Using ORACLE_HOME =/dboracle/orabase/product/10.2.0 -------------------------------------------------------------------------- Getting SQLPlus version [DEBUG] ORACLE_HOME = /dboracle/orabase/product/10.2.0 [DEBUG] LD_LIBRARY_PATH = /dboracle/orabase/product/10.2.0/lib SQL*Plus exec file = sqlplus -------------------------------------------------------------------------- [DEBUG] Statement: /dboracle/orabase/product/10.2.0/bin/sqlplus -v  -------------------------------------------------------------------------- [Orbit] $ /dboracle/orabase/product/10.2.0/bin/sqlplus -v Error 6 initializing SQL*Plus Message file sp1<lang>.msb not found SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory Process exited with status 1 --------------------------------------------------------------------------  detected host = cbl06698dat02 -------------------------------------------------------------------------- Using ORACLE_HOME =/dboracle/orabase/product/10.2.0 -------------------------------------------------------------------------- Running defined script on silbxx/********@orbdw_any_scan Temp script: file:/orbit_serial/jenkins/home/workspace/Orbit/temp-script-15180746804457289186717442500254.sql -------------------------------------------------------------------------- [DEBUG] ORACLE_HOME = /dboracle/orabase/product/10.2.0 [DEBUG] LD_LIBRARY_PATH = /dboracle/orabase/product/10.2.0/lib:/dboracle/orabase/product/10.2.0 Work Directory = /orbit_serial/jenkins/home/workspace/Orbit [DEBUG]  Statement: -------------------------------------------------------------------------- /dboracle/orabase/product/10.2.0/bin/sqlplus -L silbxx/Jas0N_B0u4n3@orbdw_any_scan @/orbit_serial/jenkins/home/workspace/Orbit/temp-script-15180746804457289186717442500254.sql --------------------------------------------------------------------------   [Orbit] $ /dboracle/orabase/product/10.2.0/bin/sqlplus -L silbxx/Jas0N_B0u4n3@orbdw_any_scan @/orbit_serial/jenkins/home/workspace/Orbit/temp-script-15180746804457289186717442500254.sql Error 6 initializing SQL*Plus Message file sp1<lang>.msb not found SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory Process exited with status 1 -------------------------------------------------------------------------- Exit code: 1 -------------------------------------------------------------------------- java.lang.RuntimeException: Process exited with error  at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunner.run(SQLPlusRunner.java:385)  at org.jenkinsci.plugins.sqlplusscriptrunner.SQLPlusRunnerBuilder.perform(SQLPlusRunnerBuilder.java:112)  at hudson.tasks.BuildStepMonitor$1.perform(BuildStepMonitor.java:20)  at hudson.model.AbstractBuild$AbstractBuildExecution.perform(AbstractBuild.java:744)  at hudson.model.Build$BuildExecution.build(Build.java:206)  at hudson.model.Build$BuildExecution.doRun(Build.java:163)  at hudson.model.AbstractBuild$AbstractBuildExecution.run(AbstractBuild.java:504)  at hudson.model.Run.execute(Run.java:1727)  at hudson.model.FreeStyleBuild.run(FreeStyleBuild.java:43)  at hudson.model.ResourceController.execute(ResourceController.java:97)  at hudson.model.Executor.run(Executor.java:429) ERROR: Process exited with error SSH: Current build result is [FAILURE], not going to run. Finished: FAILURE

                 

                ==================================================================

                Please can you guide what details needs to be updated in this latest plugin and where it would be.

                 

                Thanks,

                Mrinal

                1. Unknown User (boaglio)

                  Your initial setup before upgrade should work.

                  Is your Oracle installed on the same machine of Jenkins ?

                  This directory exists /dboracle/orabase/product/10.2.0/lib ?

                  1. Unknown User (mrinalsan)

                    Thanks for your response .....

                    Your initial setup before upgrade should work.

                    Answer– Yes it was working fine

                    Is your Oracle installed on the same machine of Jenkins ?

                    Answer – Yes both are one same machine ...cbl06698dat02

                    This directory exists /dboracle/orabase/product/10.2.0/lib ?

                    Answer – Yes that directory exists in cbl06698dat02

                    prod@cbl06698dat02>cd /dboracle/orabase/product/10.2.0/lib prod@cbl06698dat02>pwd /dboracle/orabase/product/10.2.0/lib prod@cbl06698dat02>

                     

                    Thanks,

                    Mrinal

                    1. Unknown User (mrinalsan)

                      Please can you guide us ...

                      1. Unknown User (boaglio)

                        1. Define ORACLE_HOME inside .bashrc file from the same user you run Jenkins;
                        2. Reboot your machine;
                        3. Use option  Try to detect ORACLE_HOME from Jenkins general configuration;
                        4. Remove any custom configuration from you job;
                        5. Try to run a build again.

                         

                        1. Unknown User (mrinalsan)

                          Many Thanks Boagilo,

                          This solution is working , now we are able to oracle database from Jenkins..

                           

                          But Unfortunately Jenkins is not transferring  the actual scipt (which I am trying to execute ) .sql file ...

                          Jenkins is converting the .sql file into only one semicolon...

                          PFB actual script and transferred one...

                          Actual .sql file : test.sql

                          select sysdate from dual

                          /

                          show user

                          /

                           

                          Jenkins Transferred:

                          prod@cbl06698dat02>pwd

                          /orbit_serial/jenkins/home/workspace/Orbit/Code_Base/DB_Scripts/Schema

                          prod@cbl06698dat02>ls -lrt

                          total 0 -rw-r----- 1 prod release 8 Mar  8 13:25 test.sql prod@cbl06698dat02>cat test.sql

                          ; exit;

                           

                          prod@cbl06698dat02>

                           

                          1. Unknown User (boaglio)

                            Your file is missing, that's why this is happening.

                            The transfer responsibility is not handled by my SQLPlus script runner plugin, it should be Git or something else you are using.

                            1. Unknown User (mrinalsan)

                              Hi ,

                              I have cross-checked file is in place and I am using SVN..

                              While script is executing from SVN then Jenkins is unable to read the actual lines inside the script..

                               

                              Thanks,

                              Mrinal

                              1. Unknown User (mrinalsan)

                                Can I go for a screen share session with you ..

                                Many thanks ..

                                1. Unknown User (mrinalsan)

                                  Please can you help us?

                              2. Unknown User (boaglio)

                                "While script is executing from SVN then Jenkins is unable to read the actual lines inside the script.."

                                 

                                I don't get this.... you handle SVN outside Jenkins ?

                                 

                                1. Unknown User (mrinalsan)

                                  I am using Subversion which is being access by Jenkins and same Subversion is working fine for shell script (using Publish over ssh).

                                   

                                  Here Jenkins is unable to read .sql file from Subversion, Jenkins is taking whole script as a semi colon only ...

                                  Please can you give me 5 mins to show you the problem or a quick call will be fine ..

                                   

                                  Many thanks ,

                                  Mrinal Sannyasi

                                  Mob : 0091 8336900905

                                  1. Unknown User (mrinalsan)

                                    Hi ,

                                    Please can you help me out ..

                                    Thanks..

                                  2. Unknown User (boaglio)

                                    Have you tried to run with user defined script?

                                    1. Unknown User (mrinalsan)

                                      Yes , User defined Script is working fine..

                                      1. Unknown User (mrinalsan)

                                        Hi Fernando,

                                        We are able to execute DDL script (Create Table ) successfully using SQL Plus Script runner plugin .

                                        It happened by putting an "exit" at very last of script..

                                        But we are unable to execute stored procedure , it is going into hung state...

                                        What should we use at very last of stored procedure...

                                         

                                        Thanks,

                                        Mrinal

                                        1. Unknown User (boaglio)

                                          If your script has a problem, you should test it from command line before running on Jenkins.

                                          If a script doesn't exit from SQL*Plus, it will hung.

                                          1. Unknown User (boaglio)

                                            If you are in a hurry you should contact for Commercial Support , this is an open source project which is coded in spare time. If your company uses Oracle, probably has enough money to pay for it.

  17. Unknown User (kchand1)

    Hi Fernando Boaglio,

    I have a requirement to compile pl/sql code through Jenkins. The triggering point would be as soon as pl/sql code is pushed to Github it should be compiled through Jenkins.

    I have been able to integrate Github with Jenkins and build jenkins job as soon as a code is pushed (after commit) to Github.

    Do you know if I can use SQLPlus Script Runner plugin to achieve that? If yes can you pls let me know how?

    Thanks,

    Krishna

    1. Unknown User (boaglio)

      I think you do, just create one script (run.sql)  that call all other scripts and call run.sql from your Jenkins job.

      1. Unknown User (kchand1)

        How would run.sql know which package has been pushed to github? Let say package A has been pushed to one of the branch (Development Branch) in Git, I want that package to compile in Development Instance. 

        1. Unknown User (boaglio)

          In this scenario you should have a run.sql like this:

            @ A.sql

           

          If anyone commits a new A2.sql file you should change run.sql to:

            @ A.sql

            @ A2.sql

           

          Maybe you can create a script to build a new run.sql for every commit.

        2. Unknown User (nagapraveen)

          Please help what is the approach followed for this requirement. Many Thanks!

  18. Unknown User (nagapraveen)

    Unknown User (kchand1)we have got the similar requirement, could you pls how you have implemented the approach that Unknown User (boaglio)had proposed. Many Thanks!