Saturday, March 28, 2015

Integrating Jenkins and SQL*Developer's Unit Test

The following links are SQL*Developer reports that will produce JUnit formatted output. They are specifically designed and tested to work with Jenkins.

junit_suite_report.xml - Report for a single Test Suite (UPDATED 11-Apr-2015)
junit_test_report.xml - Report for a single Unit Test (UPDATED 11-Apr-2015)

Download the above XML files and load them into SQL*Developer like this:
  1. View -> Reports
  2. Right Click on "User Defined Reports"
  3. Select "Open Report"
  4. Select the XML file and Click "Open"
These reports DO NOT create working HTML reports. More about how to use the reports below.

You can execute tests and reports from the command line.

To Run a Unit Test

sdcli unittest -run -test -name "TEST_NAME" -repo "REPO_CONNECTION" -db "DB_CONNECTION"

Create JUnit Report for a Unit Test

sdcli reports generate -report junit_test_report -db "REPO_CONNECTION" -file "REPORT_FILENAME" -bind "test_name=TEST_NAME"

("junit_test_report" will select that latest run of the Unit Test named in the "test_name" bind variable.)

Run a Test Suite

sdcli unittest -run -suite -name "SUITE_NAME" -repo "REPO_CONNECTION" -db "DB_CONNECTION"

Create JUnit Report for a Test Suite

sdcli reports generate -report junit_suite_report -db "REPO_CONNECTION" -file "REPORT_FILENAME" -bind "suite_name=SUITE_NAME"

("junit_suite_report" will select that latest run of the Test Suite named in the "suite_name" bind variable.)

TEST_NAME - Name of a unit test in SQL*Developer
SUITE_NAME - Name of a test suite in SQL*Developer
REPO_CONNECTION - SQL*Developer connection name for the unit test repository.
DB_CONNECTION - SQL*Developer connection name that will run the unit test.
REPORT_FILENAME - File name for output of the SQL*Developer report.

More information about using the SQL*Developer command line is at
http://www.thatjeffsmith.com/archive/2013/07/sql-developer-4-0-and-the-command-line-interface-cli/.

IMPORTANT
These reports create an HTML file called "REPORT_FILENAME".html.  The file must be stripped of the HTML tags and saved as an XML file.  The following "sed" command will perform the conversion:

sed -e '1,/^<pre> Remove this line and above$/d' -e '/^Remove this line and below <\/pre>$/,$d' < REPORT_FILENAME.html > REPORT_FILENAME.xml

After the conversion, the XML file should look like an XML file.  Jenkins will show errors in the "build console" if something is wrong with the XML file.

To tell Jenkins how to capture the JUnit XML output:

  1. Click on the Build
  2. Click "Configure"
  3. Scroll down to "Add Build Step"
  4. Select "Publish JUnit test result report"
  5. Enter the location of the XML files in "Test report XMLs"

This is an example of JUnit output from SQL*Developer captured by Jenkins from several Test Suites combined into one build.


This is an example of an error captured by Jenkins from SQL*Developer JUnit output.


More information about JUnit XML format for Jenkins is at
http://nelsonwells.net/2012/09/how-jenkins-ci-parses-and-displays-junit-output/

Additional JUnit XML format information is at
http://help.catchsoftware.com/display/ET/JUnit+Format

21 comments:

  1. Thanks for posting the steps here. It really helped me to configure and get the report rolling in Jenkins.

    ReplyDelete
  2. Hi, when i try to excecute sdcli reports generate -report junit_test_report -db "REPO_CONNECTION" -file "REPORT_FILENAME" -bind "test_name=TEST_NAME", receive a null pointer exception on "junit_test_report". There is some plugin or extra? Thanks

    ReplyDelete
    Replies
    1. Nothing extra. I will take a closer look in 4 hours.

      Delete
  3. Did you replace these strings with the needed values?

    -) REPO_CONNECTION - SQL*Developer connection name for the unit test repository.
    -) REPORT_FILENAME - File name for output of the SQL*Developer report.
    -) TEST_NAME - Name of a unit test in SQL*Developer

    For instance, one of my environments looks like this:

    sdcli reports generate -report junit_test_report -db dtg_utr -file ./junit_report_FIT_User1 -bind "test_name=FIT_User1"

    because my

    -) REPO_CONNECTION is dtg_utr
    -) REPORT_FILENAME is ./junit_report_FIT_User1
    -) TEST_NAME is FIT_User1

    I hope that helps.

    ReplyDelete
    Replies
    1. Duane, before change the SQL*Dev version (to older version), this works.

      Thanks for you answer and your time.

      Regards

      Delete
    2. Hi, which version of SQL Developer is the one that works?

      Delete
  4. Hi Horacio. The 4.1.5 works fine (with minimal issues on linux).

    Regards

    ReplyDelete
  5. Hi, can you please share the xml files you have shared for creating the junit reports. it says the files are not found.
    Thanks

    ReplyDelete
    Replies
    1. I can't find a problem. Please let me know if you still receive "files are not found".

      Delete
    2. thanks Duane, I was behind a firewall, which caused this issue

      Delete
  6. The 2 files that create the junit reports are are "junit_suite_report.xml" and "junit_test_report.xml". The links are working in Chrome, Chrome Incognito, and IE. I also had a co-worker check them in Firefox/Mozilla.

    The URLs with HTTP will also work:

    http://drive.google.com/file/d/0B4Qp7jjwAFDUbm10RGhCZ0NnZlU/view
    http://drive.google.com/file/d/0B4Qp7jjwAFDUY19pSnVwWlRKeTQ/view

    ReplyDelete
  7. Hi Duane, it works fine.i was behind firewall hence the issue

    ReplyDelete
  8. When i try to run the report command, i am getting the following error. please help

    Command failed:
    java.lang.IllegalArgumentException: null connection not allowed
    at oracle.dbtools.db.DefaultConnectionIdentifier.(DefaultConnectionIdentifier.java:29)
    at oracle.dbtools.db.DefaultConnectionIdentifier.createIdentifier(DefaultConnectionIdentifier.java:21)
    at oracle.dbtools.raptor.metadata.AbstractDisplayModel.getQuery(AbstractDisplayModel.java:393)
    at oracle.dbtools.raptor.metadata.AbstractDisplayModel.getQuery(AbstractDisplayModel.java:377)
    at oracle.dbtools.raptor.controls.display.html.PLSQLHtmlGenerator.generateHtml(PLSQLHtmlGenerator.java:38)
    at oracle.dbtools.raptor.controls.display.DisplayHtml.generateStyleSpecificHtml(DisplayHtml.java:391)
    at oracle.dbtools.raptor.controls.display.DisplayHtml.generateModelHtml(DisplayHtml.java:325)
    at oracle.dbtools.raptor.controls.display.DisplayHtml.generateHtml(DisplayHtml.java:261)
    at oracle.dbtools.raptor.controls.display.DisplayHtml.generate(DisplayHtml.java:202)
    at oracle.dbtools.raptor.report.headless.ReportsProcessor$GenerateReportTask.doWork(ReportsProcessor.java:101)
    at oracle.dbtools.raptor.report.headless.ReportsProcessor$GenerateReportTask.doWork(ReportsProcessor.java:39)
    at oracle.dbtools.raptor.backgroundTask.RaptorTask.call(RaptorTask.java:193)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at oracle.dbtools.raptor.backgroundTask.RaptorTaskManager$RaptorFutureTask.run(RaptorTaskManager.java:629)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

    ReplyDelete
    Replies
    1. I see "null connection not allowed" in your error listing. I am unable to duplicate that error. I assume you are running the "sdcli reports generate" command. I tried connecting to an incorrect REPO_CONNECTION and received a "connection 'REPO_CONNECTION' not found" error. I tried connecting with a bad password in REPO_CONNECTION and recieved a "ORA-01017: invalid username/password" error.

      There is some mention of version issues in other comments in this post. I successfully created a "REPORT_FILENAME.html" file using version 4.1.5.21. I know I created these reports and ran them on earlier versions.

      I hope that helps.

      Delete
  9. i have an basic testcase for award_bonus and got success case.when i execute the sdcli junitreport to genrate htm report it shows me empty html file saying remove the Remove this line above and Remove this line and below

    ReplyDelete
  10. It appears the report can't find any data. I assume you are running "junit_test_report.xml". Use your "REPO_CONNECTION" and "TEST_NAME" values to confirm test results are stored in the SQL*Developer unit test repository:

    1) Open SQL*Developer and open your "REPO_CONNECTION"
    2) Run this query: select * from ut_test_results where name = "TEST_NAME";

    When I do these steps I open the "dtg_utr" connection and query for the name "FIT_User1"

    If there is no data, something is not connected. You will need to determine why the results of your unit tests are not being stored in the repository.

    If there is data in the "UT_TEST_RESULTS" table, ... ???

    ReplyDelete
  11. Hi Duane,
    Does this method should work for sqldeveloper Version 19.1.0.094?
    For now I can see:
    Command failed:
    java.sql.SQLException: ORA-06550: line 7, column 14:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 4, column 7:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 39, column 19:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 34, column 7:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 49, column 19:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 43, column 7:
    PL/SQL: SQL Statement ignored
    ORA-06550: line 124, column 44:
    PLS-00364: loop index variable 'TEST' use is invalid
    ORA-06550: line 124, column 10:
    ...

    ReplyDelete
  12. Sorry Duane, it was a wrong repo name.
    Now I have "Command Completed." and I can see the REPORT_FILENAME.html file in the bin folder.
    But it is kind of empty - 1 KB. And when I tried to use sed command I can see:
    sed: -e expression #2, char 31: unterminated address regex
    new xml file is empty

    ReplyDelete
    Replies
    1. Can you confirm you have data in your repository?

      If you are running the "junit_suite_report.xml", then run this query:

      select * from ut_suite_results;

      If you are running the "junit_test_report.xml", then run this query:

      select * from ut_test_results;

      If there are no records in these tables for the suite/test name you entered for the "-name" parameter, the report will be more or less empty.

      Delete
  13. Hi Duane, thanks for the quick response!
    I tried "select * from ut_test_results;" query and it returned some data.. OK, at least it means that method should work for the sqldeveloper 19.1, which is good. I'll keep digging.
    I like this tool except two things - no dark theme and no UT reports in html.
    Did you try to create html format reports for Code coverage?
    Stan

    ReplyDelete
    Replies
    1. "Did you try to create html format reports for Code coverage?"
      No. I developed a different database application for unit testing that includes code coverage (wtplsql.org). There are some tricky aspects to a good code coverage implementation.

      Delete