Unit Testing Your PL/SQL with Oracle SQL Developer

thatjeffsmith SQL Developer 59 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 Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 59

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

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

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

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

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

  4. Hi Jeff,
    I can not find any parameters in specify parameters.
    My procedure has some input parameters. But I can not find any of them in specify parameters step.
    Please advise.

    Thanks

    Aaron

    1. thatjeffsmith Post
      Author
  5. Hello Jeff,

    Below is my use case. I have a stored procedure which processes data from set of 3-4 tables(SRC1, SRC2, SRC3…) and infers information and generates some derived data that is stored in a Table “TRG”.

    I need to validate if the data derived is correct.

    For this,

    startup scripts :

    Create External Tables and load data to SRC1, SRC2, SRC3..tables from a CSV. I am using EXECUTE immediate to create an External table on the fly…these external table and structure has been wrapped in a PL\SQL pkg that has been created on the Unit Test USer schema itself which we use to run the Tests.

    Eg :
    DECLARE
    P_FILE_NAME VARCHAR2(200);
    BEGIN
    P_FILE_NAME := ‘ExpectedAWS.csv’;
    XXT_CREATE_EXTTAB_PKG.CREATE_EXPECTED_AWS_EXTTAB(P_FILE_NAME => P_FILE_NAME);
    END;

    Verify : Run the stored procedure that will actually derive and store data into TRG.

    Validate : The data from TRG will be compared against expected results which will be stored in an other external table

    Teardowns : Will delete data that has been loaded into SRC1, SRC2…and TRG. Also it will drop the External tables that have been created.

    ** The above approach of startup scripts worked well locally on when repository was created on a local DB Server(Personal Oracle Database 12c Release 12.1.0.1.0 – 64bit Production)**

    It does not work on a version installed on DBCS on the cloud.(Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production)

    Following is the error : The Unit Test Repository does not recognise a PL\SQL object created on the user where Unit Test Repository is created.
    The anonymous block runs fine when run standalone in SQL Developer

    Startup LoadEpxectedAWS (1/1) failed: ORA-06550: line 6, column 3:
    PLS-00201: identifier ‘XXT_CREATE_EXTTAB_PKG.CREATE_EXPECTED_AWS_EXTTAB’ must be declared
    ORA-06550: line 6, column 3:
    PL/SQL: Statement ignored

  6. Hello
    great tuto. I would like to ask you something about testing on PLSQL:

    – which framework for plsql test (utplsql, ??)
    – how to check PLSQL quality code on development step (as we do for java using SUN java rules ?)

    thanks in advance

    Teo

  7. Hello,
    please it is possible to export test results in some special format? Or only way how to report the test are with “Unit Tests – Reports” part?

    Thank You

  8. Are there any plans to support testing procedures and functions that have parameters with user defined object types?

  9. Quick question, is there a specific privilege or role needed to create a repository; I try to create the repository and keep getting a message that selected connection does not have required permission, which follows a prompt to log in as sys. Have no access to sys and trying to find the role and/or privilege, so I can ask dba for it.

    Thanks!

    1. thatjeffsmith Post
      Author
      1. Thanks, that showed me that it’s not meeting the minimum requirements, since have not been granted RESOURCE role, the last sql that runs is the basic_privs_granted decode query, is there a way around not having that privilege, since have been told by dba that role can’t be granted.

        1. thatjeffsmith Post
          Author
          1. Thanks for your help, ended up installing a local db and export all objects, it took me a while to get the db installed locally, but after that we are ready for TDD.

  10. Hi, I am using sample code provided in “Help Center” to test Unit Testing. It gives me error as below.

    APPS: Run On – 2016-03-08 17:13:22.711 ERROR 218.0 AWARD_BONUS failed: Startup Table or Row Copy (1/1) failed: ORA-00942: table or view does not exist

    Implementation – Test Implementation 1 ERROR 218.0 Startup Table or Row Copy (1/1) failed: ORA-00942: table or view does not exist

    Startups ERROR 218.0 Startup Table or Row Copy (1/1) failed: ORA-00942: table or view does not exist

    Any idea of getting this error?

  11. Hi,

    I posted this over a year ago on the forums (https://community.oracle.com/thread/3635194) but haven’t got any reply.
    I haven’t tested the UT since – so if things changed i’d be glad to know.

    My company wants to start using SQL Developer as our DB Unit Testing tool.
    However, we are not quite sure how to maintain it in our source control, and how to use the files once they are there.

    I’ll explain:
    1. Granularity of items kept in Source Control:
    When Exporting a Suite – the generated XML file contains the Suite definition but also the definitions all of it’s linked Suites, Tests, Libraries etc.
    We thought about Using a root Suite to run all the other Suites and Tests in one go and keep that in in our source control- but this is problematic since each developer would be locking the entire UT tree for his/her modifications – No working in parallel (SQL Developer doesn’t provide means for merging or comparing exported UT XMLs or UT repositories).
    So now we are thinking about saving all of the pieces separately – Tests and Library definitions, and not working with Suites at all. This would allow developers to check in just what they are working on and not interfere other developers.
    The problem with this approach is that it would more troublesome to import all of the separate files, let alone having to run multiple (can get to hundreds of) tests instead of a single Suite (I also assume running would take more time).
    It would have been best if there was an option to export Suites with just the references to the used items but without their actual definition, or alternatively, some way to automatically generate Suites for imported tests according to some rules.
    2. Repositories:
    Is it better to have a single master UT repository for all developers to use for their testing – or each developer working on his own repository (importing from the master) to not interfere/override someone else’s work?
    Since Repository creation can’t be done through CLI (command line interface)- I assume duplicating the UT Repository schema would work.
    3. CLI
    There are a bunch of things doable from the GUI, but aren’t doable through CLI.
    This puts sticks in the wheels of trying to automate the unit testing process – which leads us to sometime reverse engineer what the GUI does and implement it on our own using packages on the UT repository (in example – purging the repository or the results).

    Any guidance would be appreciated.
    Thanks.
    Mor

    1. thatjeffsmith Post
      Author

      what do you want to do via the CLI that’s only avail via the GUI?

      to deploy the repository, I would probably just copy/clone it, as you suggested

      and then one or many repositories…maybe both? devs could have their own, and when they were checking code into source control, i would also check in my unit tests.

      1. First – thanks for replying.
        Second – If you re-read your reply – I do hope you see there is something problematic in the way UT is implemented (or at least the export import part) if we have to do a mixture of both just to be able to not interfere with each other’s work. I am not aware of any other tool requiring this work method just to be able to keep things in a source control…

        As stated in my post above – we wanted to be able to Purge the Repository or the previous Run Result using CLI – both are achievable through the Tools->Unit Test menu but not through CLI.

        -Mor

        1. Hi Mor
          First I’m interested in SQLdev Unit testing but I must admit I’ve never used it for any real project so I will just try to provide guidance.
          The problem related to source control you are facing looks like those encountered with Oracle APEX for example.
          From what I read, in Apex there is an utility that export all the Datas related to an application (which in UT will be your repository) then you can split this export file into “1 sql file by module” which can be versioned in the VCS (version control system). The tool is called APEXExportSplitter.
          I’m talking about it because I feel like you are facing the same challenge, which should be common to all dev/test tools storing their definition in a database.
          so maybe you can Export Tests and Suites as SQL scripts from each developer computer, working with XE, commit them in SVN for example. Then they will be imported in the Server dedicated to testing and run automatically.
          The SQLdev UT tool seems interesting but in fact, challenges like the one you encountered really need to be adressed.

    1. thatjeffsmith Post
      Author
      1. But how to integrate with Jenkins if one must use the command line tool (sdcli) interactively to send passwords?

        Is there any other way to fully automate the use of the cli?

        1. So, I’ve found an answer to my own question… It might help someone else.

          This works on my machine, a Windows 7 workstation.

          I just used sqldeveloper to edit my connections and put the passwords there. Now, when I run sdcli to run the unit tests I am not asked about entering passwords.

          Now I’ll try to integrate with Jenkins also on my workstation just for testing. I guess I’ll have problems when trying to use a proper CI server on other machine, perhaps it will not have sqldevelper or I might not be able to edit the connections…

          1. Hi,

            Did you find an answer for this? I am also trying to integrate it with Jenkins. I am wondering on how to do it without installing the sql developer in jenkins server.

            Appreciate your help if you find any alternative option.

  12. Hi All,

    Once the Oracle programmer develops a package in development Instance and upload in PVCS. How does the code gets reflected in Testing Instance?

    Should the testing team create the package again in Testing instance?
    If they connect to dev instance using DB Link, the tesing will be done on DEV data , which doesnt differ from Unit testing…..

    Plz clarify…..

    Thanks in advance

  13. Hi Jeff,

    I started to use Unit Testing in SQL Developer recently, and I am stuck with the test where I expect exception. We have user-defined ORA codes (in the range of [20000, 20999]) that we raise with RAISE_APPLICATION_ERROR when needed. I wanted to make a test for such case. I have expected to get the error 20128, so I selected Exception from the “Expected result” drop down list, and put the value 20128 next to it. However, the test was not successful:

    Execution Results
    ERROR
    Expected exception: [20128], Received: [20128: ORA-20128: Invalid Username/Password! Please check and try again.
    ORA-06512: at “CUSTOMER.CUSTOMERLOGIN”, line 256
    ORA-06512: at line 2
    ]

    After that I tried to leave the default ANY in the field next to the Exception in order to at least verify that any exception occurred, but it was not successful as well:

    Execution Results
    ERROR
    Expected exception: [ANY], Received: [20128: ORA-20128: Invalid Username/Password! Please check and try again.
    ORA-06512: at “CUSTOMER.CUSTOMERLOGIN”, line 256
    ORA-06512: at line 2
    ]

    The version of SQL Developer I use is 4.1.1.19.

    Could you, please, help me with this issue?

    Thanks,
    Ivan

        1. I’ve just tried -20128, but with no success again:

          Execution Results
          ERROR
          Expected exception: [-20128], Received: [20128: ORA-20128: Invalid Username/Password! Please check and try again.
          ORA-06512: at “CUSTOMER.CUSTOMERLOGIN”, line 256
          ORA-06512: at line 2
          ]

          In PL/SQL I raise exception like this:

          RAISE_APPLICATION_ERROR (-20128, lvMsg);

          Seems like those user defined exceptions are not recognized well by the SQL Developer, as even if I put ANY as the expected exception, same problem occurs.

          1. Hi all,

            It seems I found out where the bug is in the Unit Testing tool. I experimented a bit with my procedure, and I noticed that in case one of the OUT parameters of the procedure is of VARCHAR2 type, exception 20128 is not recognized.

            However, in case I remove all VARCHAR2 OUT parameters, and leave only NUMBER and DATE OUT parameters in my procedure, exception 20128 is recognized either when I put 20128 as the expected exception, or when I put ANY.

            Jeff, could you, please, test this scenario on your side in order to confirm whether there is a bug in the Unit Test tool?

          2. Same problem happened to me testing a function returning a varchar2, any solutions around?

  14. Code Coverage statistics in Unit Testing feature set seems incorrect

    Need help on SQL support for Customizing the Pre-defined SQL Scripts on SQL Developer DB Unit testing Code Coverage Report .
    In the attached Screen shot its not reaching the 100 % lines code coverage ,Due to highlighted lines in the following stored procedure code coverage report we are covering only 50% code coverage only.
    And the following Stored procedure have 10 lines of code but its counting the 13 lines . If we avoid highlighted lines will get the 100 % of Code coverage in Stored procedures.

  15. When I open the Unit Test menu, everything is grayed out and un-selectable. I’m using SQL Developer v. 4.0.2. Is there a step I’m missing to enable it? I’m actively logged in.

  16. Thanks for your reply. But that approach just adds another layer I have to install on my target database before running the tests stored in my repository and therefore does not seem very handy to me.

    I would prefer a solution where I can test anonymous blocks with binds. So I could assign (in)binds and check the values of the (out)binds afterwards. I would get the flexibility to test types or whatever I want without the need to install anything on the target database. Everything belongig to the test would be stored in the repository.

  17. Hi Jeff,

    at the Moment I have no possibility to build unit tests in SQL Developer for my types. How would you test the logic of types in SQL Developer?

    Are there any plans to support unit testing for types?

    Thanks

    Malte

    1. thatjeffsmith Post
      Author
  18. Hi Jeff,

    I am told by our AD Technology to create a suite of “automated” unit tests in SQL*Developer (we have 3.0.04).

    I followed the tutorial and created a unit test, ran it, but I don’t understand some of the concepts.

    My biggest issue is, how does SQL*Developer “validate” the results.

    So, taking the award_bonus as an example.

    I give the inputs as emp_id = 1 and gross_sales as 10000.
    Now, emp_id 1’s comm_pct is 0.1%. So, for this sales, he should get a comm of 1000. His current salary is 5000. So, the award_bonus should increase his salary to 6000, right.

    (1.) Now, my question is, when I create a unit test in SD, do I “HAVE TO KNOW” that it will increase the salary of emp_id = 1 from 5000, to 6000 for gross_sales 10000 and comm pct 0.1%? Do I have to know this?

    (2.) Ok, in the SD unit test creation wizard, I give emp_id = 1 and gross_sales 10000 as inputs. So, when I go to the next screen, it is asking me how to test the result. Here, I type a query: SELECT COUNT(*) FROM emp where emp_id = 1 and salary = 6000. i.e. When this test is run, the employees salary will be increased to 6000.

    I don’t give any teardown. I run the unit test. It says success. Now, I run the test again. In my table the salary has increased to 7000. And my test says SUCCESS. But, the query I gave to validate, will give count(*) = 0, since his salary is not 6000 anymore. So, how does this work? How, does SQL*Developer know it was success.

    I have asked this in the Oracle Forums as well, but nobody has answered: https://community.oracle.com/thread/3623090

    I would be most greatful if you could explain what is happening here..

  19. Jeff, this blog led me to follow through the on-line tutorial, Thanks!

    1) I did not see complex data types such as XMLTYPE, SDO_GEOMETRY in the list of supported datatypes for Lookups. Is there a plan to support it?
    2) I recommend moving “Lookup Catagory” as a column in the Implementation grid so each parameter can be associated its own Lookup.
    3) I could not figure out how to validate my proc or function that returns a CLOB. Any suggestion?

    Bo

  20. I received the following error when I try to create de unit test:
    The type PL/SQL RECORD of argument MY_RECROD_VAR is not supported.

    What can I do?

  21. Hi Jeff,

    I am following this article trying to figure out how unit test in SQL Developer works, but I am stuck at the very first step, which is when I go to Tools->Unit Tests-> all options under unit tests grayed out, meaning none of them is available. Apparently I can’t go anywhere from here as I can’t even create a new test repository.

    I tested SQL Developer 4.0.2.15 on both Windows and Mac and received the same issue. Did I do anything wrong? Actually I barely started.

    Please advise.

    Thanks

    Aaron

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
      1. Don’t we all! 🙂

        Is it true that to install a Unit Test Repository you need the SYS password? That’s a big bummer since all of my test databases on are Amazon RDS — no SYS password 🙁

        1. thatjeffsmith Post
          Author

          Shouldn’t need SYS – but you don’t even have to do that. Install the Unit Test Repos on your local instance. You can run the tests anywhere then – there’s no requirement to have the repository on the database you want to test.

      2. Jeff,

        I love the tool and have implemented a suite of suites for testing our database procedures.

        I’d like to see some examples on setting up testing of more complex scenarios test scenarios.

        For example, we have a database with a container object, which we would want to create. Then populate it with the objects that reside in the container, test operations on the container with these objects in it, and the objects in the container, then clean up the container and the contents at the end.

        I would like to find a way to create a test suite that does the setup of the container (itself a test), then adds various flavors of contents to the container (itself a series of tests), and allows a series of tests on the contents and the container. After that, the contents is torn down (itself a test), and the container is torn down (also a test).

        Right now, the only way I can see to do this is to create pseudo tests that do a portion of each of the above, in series. However, the setup and teardown need to be separate so the other tests can occur before the teardown. Maybe (probably) I am missing something.

        Another way to look at it is that am looking for a way to have nested tests that call additional tests after they run but before they do their teardown.

        An example of something like this would be VERY helpful. Right now I am considering creating some kind of test harness in a package that manages this for me, but I’m hoping there is an easier way.

  22. Your Steven Feuerstein comment prompted me to see what he’s up to and I’m amazed to see that he’s rejoined Oracle! Looking forward to see what he brings to the table!

    1. thatjeffsmith Post
      Author

      You mean, to make it more awesome?

      Yes, we’re doing active dev on it right now. And this guy Steven Feurestein is excited about unit testing for some reason, so I expect some things to happen 🙂

Leave a Reply

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