Unit Testing Your PL/SQL with Oracle SQL Developer

thatjeffsmith SQL Developer 65 Comments

Tell Others About This Story:

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.

Related Posts

Tell Others About This Story:

Comments 65

  1. 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!

  2. 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

    1. 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?

  3. 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

    1. thatjeffsmith Post
      Author

      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

    2. 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

    3. thatjeffsmith Post
      Author

      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

    4. 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

  4. 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’

    1. thatjeffsmith Post
      Author

      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.

  5. 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.!!

Leave a Reply

Your email address will not be published. Required fields are marked *