Saturday, April 18, 2015

Simultaneous CI on Windows and Linux

In my previous post "CI with SQL*Developer, Jenkins, and GNU make", I demonstrated a working build server using these items:
For my development environment, I use these additional items:
  • Subversion for Source Code Management (SCM)
  • Windows (with Cygwin) for development
  • Linux (Lubuntu) on Amazon for a build server.
Because I use both Windows and Ubuntu, my builds must function on both platforms.  Here are a few tips/tricks I found.

Use SQL*Plus Scripts

SQL*Plus works the same on Windows and Linux.  Maximize use of SQL*Plus scripts.

GNU Tools and POSIX API for Windows

For the scripting that can't be done with SQL*Plus, install GNU Tools and POSIX API on Windows.  I am using Cygwin (  It is not identical to Linux, but is close.  In my previous post I had 2 shell scripts that isolated these differences.

I use the "" script to run the SQL*Developer Command Line Interface.  Here is a portion of it:
# Go to Oracle SQL*Developer binaries
#cd "/opt/sqldeveloper/sqldeveloper/bin"                     # Linux
cd "/cygdrive/c/users/duane/sqldeveloper/sqldeveloper/bin"  # Cygwin
The SQL*Developer Command Line Interface must be run from its installed directory.  This portion of the script resolves the problem that SQL*Developer is installed in different directories on different servers.

Here is another portion of the "" script:
# Run the SQL*Developer Command Line Interface
#./sdcli "${@}"           # Linux
cmd /C sdcli "${@}"      # Cygwin
The SQL*Developer Command Line Interface requires a Windows command shell to properly run in Windows.  Since Cygwin is running a "bash" shell, the Windows command shell must be called in Windows.  This script is not checked into SCM because it must be configured for each server.

The above script ( runs the SQL*Developer Command Line from its installation directory.  It passes all command line parameters to sdcli (using "${0}").  However, there is a problem.  Since the script executes from a different directory, any parameter with a file name needs to include a path back to the current directory.  That where this script ( helps.  This script returns the current directory as a full path.  However, it also makes an adjustment for Cygwin.
if pwd -P | grep -q '^/[Cc][Yy][Gg][Dd][Rr][Ii][Vv][Ee]/'
   pwd -P | sed -e 's#^/[Cc][Yy][Gg][Dd][Rr][Ii][Vv][Ee]/##1' \
                -e 's#/#:/#1'
   pwd -P
The adjustment made for Cygwin is based on the idea that full paths to disk files in Cygwin start with "/cygdrive/".  This script checks for that string at the beginning of the full path.  If it finds the strings, it substitutes the correct drive letter at the front of the path.  Remember, this is in preparation for running "sdcli".  "sdcli" must have Windows paths, not Linux paths.  This script can be checked into SCM because it works for both Cygwin and Linux.

Everything else listed in these posts should run on Windows and Linux without problem.  Cheers!

Saturday, April 11, 2015

CI with SQL*Developer, Jenkins, and GNU make

In a previous post (Integrating Jenkins and SQL*Developer's Unit Test), I showed how JUnit ouptut can be created from SQL*Developer and captured by Jenkins. In this post, I will show how to use GNU "make" as the build engine with Jenkins and SQL*Developer.

(UPDATE 17-Apr-2015: Fixed formatting errors)

GNU make

GNU "make" is included with the GNU software that is part of Linux.  I found GNU "make" an easy to use and reliable build engine.  I originally wrote my build scripts in "bash".  Switching to GNU "make" greatly simplified the build scripting and provided much more functionality than my "bash" scripts.  More information about GNU "make" can be found at

What does "make" do?  It reads the "Makefile" to determine what recipes must be used to update targets that may have dependencies.  Specifically, the "Makefile" in the ZIP file does this:
  1. Create/Update all the needed database objects and data in a database.  In this example, the database object are created from an SQL*Developer Cart created for this purpose.
  2. Create/Update all the needed tests.  In this example, the tests are created from an SQL*Developer Unit Test Suite export created for this purpose.
  3. Execute the Unit Test Suite and create a JUnit output for Jenkins.  In this example, the JUnit output is created from an SQL*Developer Report created for this purpose.
  4. Only execute the necessary pieces when something changes.  In this example, dependency/target relationships are handled with the built-in dependency capability of "make" using the "Makefile" created for this purpose.
Supporting Scripts

All of the files/scripts used for this example are in this file.  The list of files in the ZIP file is described in the README.txt that is also in the ZIP.

Some things to consider:

In order to accurately update database objects, the supporting scripts will delete database objects so they can be re-created.  In particular, the "drop_all.sql" script will drop all objects in a schema.  Since this is run on a newly created schema, it should not be a problem to remove all objects.

Also, the "delete_utr.sql" script will remove all data associated with a Test Suite.  This includes the results from previous runs of the Test Suite.  On a build server, this would not be a problem, but may be unexpected on a workstation.  Again, the installation procedure creates a new Unit Test Repository for this example, so it should not be a problem to remove all data.

Additional Prerequisites:

  • GNU utilities like make, sed, grep  (I am using Cygwin on Windows7)
  • Oracle database (I am using Oracle11g Express Edition)
  • SQL*Developer  (I am using 4.0.3)
  • Jenkins (I am using "java -jar jenkins.war --httpPort=8081" so it doesn't interfere with Oracle APEX on port 8080)

Before Running Jenkins:

It is important to get the build working before using it with Jenkins.  For Cygwin on Windows7, the "make" command should be run in a "Command Prompt" window, not a "Cygwin Terminal".  The "make" utility and accompanying scripts should properly isolate the differences between Windows and POSIX (Linux).
  1. Unzip the ZIP file into any directory
  2. Review the README.txt file
  3. Follow the "Installation" instructions
  4. Follow the "Testing" instructions
Running Jenkins:

1) Open the local Jenkins website (My URL is HTTP:// and create a new job.

2) Select Freestyle and Enter a name.

3) We won't be using Source Code Management in this example

4) Add a Build Step

5) Select "Execute Windows batch command" (Note: I am using Cygwin on Windows7.  For Linux, use "Execute shell")

6) Enter "cd" to confirm where the blog1 workspace is located. (Note: I am using Cygwin on Windows7.  For Linux, use "pwd -P")

7) "Save" the project, click "Build Now", click the down arrow next to "#1", then click "Console Output"

8) The Console Output reveals the location of the job workspace (C:\Users\Duane\.jenkins\jobs\blog1\workspace)

9) Copy the working set of unzipped files into the blog1 workspace.  Remove the "*.done" files (blog1_cart.done, import_utr.done, and runtest.done) to reset the build process.  This step would normally be accomplished automatically by Source Code Management like Subversion.  The "*.done" files would not be included in the SCM Repository.

10) In the browser, click "Back to Project", click "Workspace" and confirm the files are in place.  (All these files are not necessary, as this is an example listing.)

11) Click "Configure" and change "cd" to "make all".

Note: When running in Linux, environment settings for Java and Oracle XE may be needed.  I did not need these for Cygwin on Windows7.
export JAVA_HOME=/usr/lib/jvm/java-7-oracle export PATH=$JAVA_HOME/bin:$PATH export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/xe export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export PATH=$ORACLE_HOME/bin:$PATH export NLS_LANG=`$ORACLE_HOME/bin/` export ORACLE_SID=XE

12) Click "Add post-build action"

13) Select "Publish JUnit test result report"

14) Add "junit_report*.xml" for "Test report XMLs".  This will properly capture the "junit_report_FIT_blog1.xml" output from our testing.

15) Save these changes.  The, as in step 7, click "Build Now", then "Console Output".  The test should end with "Recording test results" and "Finished: FAILURE".  As indicated in the README.txt, there is an error included in the testing.  So, the test will show as failed.

16) To see if the test results were properly captured, click the down arrow next to build number like in Step 7.  The menu should have an extra item "Test Result" that was not in Step 7.  Click it.

17) Test Results from that build are displayed:

Under "All Failed Tests", the "Test Name" that failed can be broken down to the following into the following components:
  • PL/SQL Package being tested: EMP
  • PL/SQL Procedure begin tested: REORG
  • SQL*Developer Unit Test Name: Reorganization Test
  • Unit Test Implementation Name: Test Implementation 1
  • Implementation Sequence: 2_RUNTEST
Implementation Sequence "2_RUNTEST" is 2 of 4 Implementation Sequences:
  • 3_VALIDATE01 - Where 01 is the Validation Sequence

NOTE: Under "All Tests", the package "SQL*Developer_Test_Suite" does not actual exist as a PL/SQL package.  This package name is used to collect the "Test_Suite_STARTUP" and "Test_Suite_TEARDOWN" results that are run at the Test Suite level.  The "Class" for this package is the name of the Test Suite.  Otherwise, as a general rule, "Class" is the name of the procedure/function being tested.

18) Click on the failed test "EMP.REORG.Reorganization Test:Test Implementation 1:2_RUNTEST"

The "Stacktrace" information is particularly useful here.  The error indicates a foreign key constraint violation on "BLOG1.BOSS".  In fact, the "I_BOSS" value of 22 is incorrect.  To fix this test, change that value to any "EMPNO" value from the "EMPLOYEE" table (like 7654).

Hopefully, this was a successful start to Continuous Integration with SQL*Developer, Jenkins, and GNU "make".  Thank you for reading,