3 Replies Latest reply: Aug 20, 2010 8:33 AM by 687216 RSS

    issues / disadvantages of the unit testing framework

      Hi everyone,

      we've decided to try the unit testing framework integrated into SQL Developer no a new project (we've been using utplsql in the past). Generally it works but we have found some issues / disadvantages compared to utplsql:

      1) The unit tests are linked directly to the functions / procedures, including schema, which means you can't have multiple schemas (each with different sets of testing data for example) and functions/procedures using definer rights. And you can't move (export/import) the tests to a different database unless the schema names etc. match perfectly (which is often the case during development). The only workaround we've found is editing the XML file, which is something I really don't like as it iterferes with the version control system we use (the file is considered 'modified' although).

      2) It's not possible to debug the unit tests (e.g. to place a breakpoint and use the usual debugging options). The only thing I've found is a 'debug' button that prints an unusable details about the execution. Moreover the text area used to enter the PL/SQL code is just a plain textbox, with no code insight (so we're developing the tests in a plain SQL worksheet), and then copying the code into the unit test.

      3) There is no place intended for description of the unit test (a header, what is it testing etc.). You can't actually use any of the editable areas (tearup, teardown, validation) as these may be referencing a library items, i.e. shared among multiple tests.

      4) It's designed to test individual procedures / functions, so if you want to test a whole process (calling multiple procedures) you need to wrap that into a completely new procedure. You can't create a unit test not linked to an existing function/procedure.

      5) Renaming a tested function / procedure makes the test invalid, and there's no way to make it work except for editing the XML (again).

      I could probably name a few more but these four are the most annoying ones - especially the second one (no debugging), which makes fixing the tests much slower. Have you solved any of the issues or have you found another (better) workarounds?

      I understand this is a quite fresh feature, and I hope some of the issues will be addressed in the next versions, but for now I'm thinking about switching back to utplsql.
        • 1. Re: issues / disadvantages of the unit testing framework
          Phil Richens-Oracle
          Hi Tomas,

          Thank you very much for your thoughtful suggestions, and my apologies for not replying to you more promptly.

          There are features planned for our next release that may help to answer some of your requirements. For the others, I will ensure that that they are considered for a future release.

          [1] A "synchronize" feature planned for the next release enables a test to be remapped to a stored operation in a different schema or database, or even with a different signature. However, it also sounds like the ability to bind dynamically at run time would be useful, so this will be considered for a future release.

          [2] PL/SQL level debug sounds like a great idea, but is not planned for the next release. I will ensure it is considered for a future release.

          [3] Again this is a great idea, which will be considered for a future release.

          [4] I think you can use suites to support this functionality. Suites enable you to group tests together and the tests can be ordered within the suite. In the next release, it is also planned to support nested suites.

          [5] The "synchronize" feature planned for the next release should resolve this problem for you.

          Here are some other new Unit Test features planned for our next release that may be useful to you: support for multiple start-ups and teardowns within a test or suite; improved parameter data type coverage.

          I hope this helps to answer some of your concerns.

          Best regards,
          • 2. Re: issues / disadvantages of the unit testing framework
            Hi Phil,

            thanks for your answers.

            1) OK, "synchronize" sounds nice.

            2) OK, I understand this would require a significant changes to the framework, as now it calls anonymous PL/SQL blocks (and AFAIK it's not possible to debug anonymous block)

            3) Great ;-)

            4) I don't think so. Maybe in some really simple cases, when just calling a sequence of procedures. But a test suite is just group of unit tests (each test testing exactly one procedure), so when you need a little bit more complex logic (not just a sequence of procedures), you can't do that with a test suite. But I can live with this - writing a separate procedure encapsulating that test logic is not a big deal. And I perceive test suites as 'a group of logically related tests' rather than a 'complex test'.

            5) OK, "synchronize" sounds nice.
            • 3. Re: issues / disadvantages of the unit testing framework
              BTW we've found another 'strange thing' - the tests behave differently when executed from GUI and from a command-line. My colleague already reported that here: Different results of unit test with ututil and GUI