Is there a equivalent utassert.eqtable (Available in utplsql version 2.X) in utplsql version 3.3?

Question

After going through the documentations of Utplsql 3.0.2 , I couldn't find any references the assertion api as available in the older versions. Please let me know whether is there a equivalent assertion like utassert.eqtable available in newer versions.

Answer 1

I have just recently gone through the same pain. Most utPLSQL examples out there are for utPLSQL v2. It transpires appears that the assertions have been deprecated, and have been replaced by "Expects". I found a great blog post by Jacek Gebal that describes this. I've tried to put this and other useful links a page about how unit testing fits into Redgate's Oracle DevOps pipeline (I work for Redgate and we often get asked how to best implement automated unit testing for Oracle).

Answer 2

I don't think you can compare tables straight away, but you can compare cursors, which is quite flexible, because you can, for instance, set-up a cursor with test data based on a dual query, and then check that against the actual data in the table, something like this:

  procedure TestCursorExample is
    v_Expected sys_refcursor;
    v_Actual sys_refcursor;
  begin
    -- Arrange (Nothing really to arrange, except setting the expectation).
    open v_Expected for
      select 'me@example.com' as Email
      from dual;

    -- Act
    SomeUpsertProc('me', 'me@example.com');

    -- Assert
    open v_Actual for
      select Email
      from Tbl_User
      where UserName = 'me';

    ut.expect(v_Actual).to_equal(v_Expected);
  end;

Also, the example above works in Oracle 11, but if you're in 12c, apparently things got even easier, because you can use the table operator with locally defined types.

I've used a similar solution to verify that certain columns of a row were updated, while others were not. You can easily open a cursor for the original data, with some columns replaces by the new fixed values. Then do the update. Then open a cursor with the new actual data of all columns. You still have to write the queries, but it's way more compact than querying everything into variables and comparing those individually.

And, because you can open the 'expected' cursor before doing the actual 'act' step of the test, you can be sure that the query with 'expected' data is not affected by the test itself, and can even base that cursor on the data you are going to modify.

For comparing the data, the cursors are serialized to XML. This may have some side effects. In the test example above, my act step didn't actually do anything, so I got this difference, showing the count as well as showing the missing data. If your cursors have more columns, and multiple difference, it can sometimes take a seconds to spot the differences between the XML tags. Also, there are currently some edge-case issues with this, I think because of how trimming works in XML.

  1) testcursorexample
      Actual: refcursor [ count = 0 ] was expected to equal: refcursor [ count = 1 ]
      Diff:
      Rows: [ 1 differences ]
        Row No. 1 - Missing:  <EMAIL>me@example.com</EMAIL>
      at "MySchema.MyTestPackage", line 410 ut.expect(v_Actual).to_equal(v_Expected);

See also: 'comparing cursors' from utPLSQL 3 concepts