If you want to unit test your PL/SQL, then Oracle SQL Developer can help make that easier for you. I’m not going to try to convince you to test your code, but if you’re already rearing to go, then read on.

So I have a long history with unit testing tools for PL/SQL, but I won’t bore you with that. What I will do, is give you a teaser, and show you a few features that I really appreciated as a person who doesn’t like to code to test code.

SQL Developer’s unit testing features REQUIRES a repository. However, it only requires ONE repository. So you do not have to set this up for every instance you want to test – you only set it up once.

To get started, open the Unit Test panel under the ‘View’ menu.

Then on the Tools Menu, access the Unit Test flyout.

'Select' Current Repository' will allow you to specify a connection to deploy the repository to.
‘Select’ Current Repository’ will allow you to specify a connection to deploy the repository to.

Once the repository is setup, the Unit Test panel will ‘know’ where it is going forward.

Now you are ready to build and run your tests!

The Sample Test and Some Cool Stuff

The Help has a chapter on the Unit Testing, and an entire section that steps you through building a test, using AWARD_BONUS() in the HR schema. So you’ll need HR.

Do this, then do this, then...you get the idea.
Do this, then do this, then…you get the idea.

To build a test, the easiest way to start is to just right-click on your program:

This will launch the unit test wizard
This will launch the unit test wizard

Now, instead of taking you through the ins and outs, I thought I would just call out a couple of cool features I ‘discovered’ while playing with the feature. Things that made me go, oh the users will LOVE that – that kind of stuff.

Startup and Teardowns
Before you run a test, you might want to do some ‘stuff.’ And after you run a test, you might want to undo some ‘stuff.’ The tool makes it pretty easy to define these, and even gives you an interface for backing up an entire table or portion of a table so you can get ‘the data back’ after you run your tests.

So to save off some data:

Pick a table, some or all of the rows, give it a name...
Pick a table, some or all of the rows, give it a name…

And then after your test is done, decide what you want to do with it:

I need to get that ONE row back to where it was before I ran this test...
I need to get that ONE row back to where it was before I ran this test…

Code Coverage Reports
Testing is good, but if you have 100 test cases for your package but only ‘exercised’ 25% of your program, then you still have a lot of ground left to cover. In SQL Developer, you only need to check a single box to collect this data:

Yes or no, do you want it?
Yes or no, do you want it?

If yes, then:

What ran, what didn't, how many times, how long did it take...
What ran, what didn’t, how many times, how long did it take…

Running Tests from the Command Line
So you define your tests until you’re blue in the face, and then what? You probably want to run your tests each time your program changes. So, what you want to do is build the unit testing into your automated build process. Our CLI makes that pretty straightforward.

Where is your repository, what test do you want to run, what database do you want to run it on?
Where is your repository, what test do you want to run, what database do you want to run it on?

Test Re-Use
Kinda like code-reuse, but with testing. So, if you define a tear down, or if you define a bit of code for a test, or a query for defining a list of inputs..you can take that and add it a library. Then you can use this over and over in your tests. It’s not only convenient, it encourages more testing, and it cuts down on errors – assuming your library items aren’t buggy 🙂

You can save what you're doing to the library, or pull stuff from the library.
You can save what you’re doing to the library, or pull stuff from the library.

There’s so, so, so much more to talk about. Like how to actually do the tests 🙂 And I’ll talk about that next.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

71 Comments

  1. Chris Jenner Reply

    Hi Jeff,

    I am creating a unit test in SQL Developer for a function within a package. I have a startup which creates a record in approx. 100 tables (done dynamically based on some meta data) and the function takes in one parameter, deletes the data I’ve just created in the startup and returns a VARCHAR2 to indicate success or failure.

    The startup and function process ok but I have an issue when it comes to the validation. I am using a boolean function to check each table defined in meta data to see if any records exist via a ref cursor and return FALSE if there is a record found from any of the tables. If no records are found we return TRUE.

    When this validation runs I am getting this error:

    Validation Boolean function failed: Missing IN or OUT parameter at index:: 1

    To try and rule out my code being at fault I changed the boolean function to the following and the error is still coming up:

    BEGIN
    RETURN TRUE;
    END;

    Also to note that the function I had originally written works without error when run as part of a PL/SQL block in a SQL worksheet. I also put the validation function into a stored function and called it in a Query returning row(s) validation and that worked ok so it looks like it is restricted to the boolean function.

    I have tried to dig around online for other reports of this but I’ve not found anything specific to unit tests. Is this a bug? I am currently using v19.1.0.094 of SQL developer. We have definitely implemented validations using boolean functions in earlier versions of SQL Developer and I’ve recently moved up to 19.1.

    Thanks,

    Chris

  2. Just looking at this functionality for the first time.
    Code coverage is interesting. In the test case used in the help and running the suite it says 17 lines total and 6 were covered. Yet that refers to actual lines, not statements. It appears that to really see if your code was touched you have to manually look at the detail. Seems crazy, unless I’m missing an option or trick. I guess I might have to use a different tool to get that kind of information.

  3. Hi Jeff,
    I am trying to understand the code coverage getting generated by running the unit test. however the “Unit name” in the “Suite runs code coverage” report is not picking the correct name. we are using Oracle 11g and i have checked the gather code coverage statistics check box on both unit test and suite level.

    • Hi Jeff,

      Thanks for responding, we have XX_AP_REM_RPT_NOTF_SUBMIT.sql procedure for which we have written unit test and unit test suite. After executing the suite, “Suite Runs code coverage” report and “UT_TEST_COVERAGE_STATS” table are picking up unit_name as “FND_RELEASE” and the actual procedure body.

    • Sorry small correction –
      Hi Jeff,

      Thanks for responding, we have XX_AP_REM_RPT_NOTF_SUBMIT.sql procedure for which we have written unit test and unit test suite. After executing the suite, “Suite Runs code coverage” report and “UT_TEST_COVERAGE_STATS” table are picking up unit_name as “FND_RELEASE” and NOT the actual procedure body.

  4. Hi Jeff, I work for one of the biggest Swiss banks and I’m pushing teams of our department to implement DB Unit Testing. Having a deep love for all Oracle products, I’m proposing SQL Developer as the proper tool for this job. Unfortunately, after a lot of testing I’ve discovered few bugs which mine the usability of SQL Developer for Unit Testing on our projects (something a bit more complex than a football tournament schema). I’ve found some issues regarding REF CURSORS and I’ve explained them thoroughly on my blog http://www.oraclefindings.com/2018/05/28/db-unit-testing-ref-cursors-sql-developer/ (I already pinged you on Twitter about this).
    Moreover, I’ve discovered that exceptions cannot be tested in case of
    – functions
    – procedures with at least one OUT parameters.
    In those 2 cases, there is an error due to the fact that the returning/out values are not set (which is normal considering that the code is raising an exception).
    Is there any chance the DEV team can look into those issues? Should I raise an official ticket?
    Many thanks!

  5. Is it possible to create an implementation where some parameters are not supplied?
    I wanted to test the procedure calls to check the default parameter values were used
    When I leave a parameter value null it appears as [null] in the value but I get the error “ordinal binding and named binding cannot be combined!” when running the test.
    My assumption is I’m missing something… any help would be appreciated

    • Mike Wood

      The only way I have found to amend the number of input arguments in to export edit the parameters and re-import the test. Maybe there is another / a better way?

  6. Hi Jeff
    under View–>UnitTest I am facing an error
    “incorrect repository version: you must upgrade this repository to use it”

    where to upgrade this ?

    when I go to Tools –>Unit Test–>create/upgrade repository, I get an error:
    “Required roles do not exist”.
    the user I am using is having elevated access like DBA_SYS_PRIVS
    Our DB is 11.2 and oracle sql developer is 4.2

    is there anything I am missing ?
    thanks

    • thatjeffsmith

      it should prompt you to upgrade it – is it not?

      if not, export the tables if you have existing unit tests, then delete them

      then try to set the current repository connection again and it should prompt you to install them

    • Yes Jeff
      I got the prompt to upgrade.
      when I tried to upgrade, I get an error
      “required roles do not exist. You will now be prompted for connection info to grant needed permissions”
      and then prompts for “sys” password.
      Our Company does not allow usage of “sys”.
      I changed “sys” to the user which created the connection.

      I get an error message ORA-01017 invalid username/password.
      the same username/password I used to create the initial connection.

      can you please guide.

      thanks

      Madhav

    • thatjeffsmith

      find someone who can login as sys or as SYSDBA to do the install for you

      or install it somewhere else on a db you can use SYS on, then export the DDL and import it to this database

    • thanks Jeff
      what are the privileges needed for the user, so that we need not look for DBA to install
      we are trying to do a POC for UNIT test and Code Coverage
      Madhav

  7. please allow format in the print command for refcursor. Exporting ref cursor data is beyond difficult in Oracle. And almost everyone has to use PL SQL as no DBA will allow direct table access in this age.

    btw, the html disguised as excel export hack from sqlplus plus is _bad_. Please, we are in 2017.
    eg

    print :myrefcur , ‘CSV’, ‘c:\temp\fname.csv’

    • thatjeffsmith

      I like this idea, consider it on the list!

      >>btw, the html disguised as excel export hack from sqlplus plus is _bad_.
      No idea what you’re referring to here.

  8. If i have an OUTPUT as REFCURSOR, how do we configure that in Unit Test Case creation, please help here. I am able to create Test Case for procedures where output is a variable, but when it’s a CURSOR, it’s giving me errors. I have tried adding the query of cursor to Dynamic Query, adding it to Result but it didnt work out. Appreciate your help here.!!

Write A Comment