Toad for Oracle New Unit Testing Capability Available in Beta

Toad for Oracle - Beta
1

An important part of the Toad for Oracle 13.2 release is a new Unit Testing capability, which is now available for testing in Beta with the Beta released today.

The Unit Testing integrates with utPLSQL to assist users with creating, running, and viewing the results of Unit Tests. utPLSQL is packaged with Toad for Oracle Unit Testing.

If you have never used utPLSQL, it is an open-source testing framework for PL/SQL and SQL, with documentation available here: http://utplsql.org/utPLSQL/

To install the Toad for Oracle 13.2 Unit Testing feature:

  1. Download Toad for Oracle 13.2 beta.
  2. Extract the beta ZIP and run the Install.bat packaged within. This will first run the beta MSI to install beta followed by the MSP to patch it to the latest version.
  3. Going forward... you can keep up with updates by running the latest beta's update installer (MSP). We post announcements to the Toad World forum each time a new beta is posted.

We are always interested in feedback from our users... Feel free to post feedback via the forum, or we have also created a survey where you can provide us with your feedback:

We hope you enjoy the new enhancement!

The Toad for Oracle Engineering team

2

Existing beta users... you will need to add the new menu items to your toolbar.

Option 1:

Right-click over your toolbar and "Restore toolbar defaults"

image

Option 2:

Right-click over your toolbar and "Customize toolbars..."

image

Select the "Unit Testing" category from the "Commands" tab and drag the menu items up to your toolbar where you'd like them.

image
image.png520×515 17.8 KB

3

Howdy,

I don't think I do enough PL/SQL to warrant unit testing, but thought I'd give it a shot. It installed into my throwaway test 12.1 database just fine, but when I try to go into the UT manager or create a new UT, I get:

ORA-01031: insufficient privileges
ORA-06512: at "UTTEST.UT_TRIGGER_CHECK", line 26
ORA-06512: at "UTTEST.UT_ANNOTATION_MANAGER", line 339
ORA-06512: at line 1
ORA-06512: at "UTTEST.UT_SUITE_MANAGER", line 579
ORA-06512: at "UTTEST.UT_SUITE_MANAGER", line 626
ORA-06512: at "UTTEST.UT_SUITE_MANAGER", line 819
ORA-06512: at "UTTEST.UT_RUNNER", line 201
ORA-06512: at line 1

Tracing my session, I see this SQL:

SELECT 1
FROM   SYS."_CURRENT_EDITION_OBJ" O, SYS.USER$ U
WHERE  0=1

In Oracle 12.1 and up, SYS.USER$ needs explicit access granted, as it was removed from SELECT ANY DICTIONARY. That GRANT can be an issue, especially if the system is busy. (not sure what exactly is attempting to access USER$, but I struggle to think of a valid reason for it and not the DBA views).

So instead I used Schema Browser to view the spec on UT_RUNNER. And I got the 1031 again and Toad froze -- sorta -- the LHS is blank and the SB got confused. I did install the UT trigger, so I disabled that, logged out and back in, but the same stuff.

Again, I'm not a target user for UT, but thought I'd mention this issue.

Thanks!
Rich

1 Like
4

Rich did you use today's beta or the one released on Friday?

5

Sent the last message premature. This issue has come up and we think resolved in today's beta. The install script is missing a grant. Run...

grant execute on <utplsql_owner>.ut_suite_item_info to <utplsql_user>;
6

This query is just a privilege check, not an actual query that we are attempting. In fact, we aren't even running the query, just having Oracle parse it. there is probably another error further down in the spool sql.

7

I haven't had a lot of time to play with this today, but uninstalling (manual schema wiping) and reinstalling, then reconnecting, with Toad 13.2.0.223 didn't help. Reading through http://utplsql.org/utPLSQL/latest/userguide/install.html my account has all of the "CREATE" and "ALTER" privs required, but many via the RESOURCE or DBA roles (which are not active inside of PL/SQL). Hmmmm...

Once I granted all of the privs explicitly, the errors went away. This includes the ORA-1031 errors attempting to use Toad's SB to simply view any of the installed packages.

I could see UTPLSQL not working, but I did not expect that to affect the Schema Browser...

Rich

8

The SB polls utPLSQL to update menu items for running tests from there.

1 Like
9

Toad 13.2.0.223 wouldn't correct your problem if you had previously installed it using 13.2.219. Build 219 had a few problems with install scripts and was missing an object grant among other things.

10

Hi Quest,

had some time now to test this out. I had utPLSQL installed to our dev database prior, which was detected correctly (using Toad 13.2.0.223). Ran some tests, that also just worked. Pretty neat feature so far :ok_hand:

One suggestion, though: We have all our test suites installed into an other schema than the main one to which we login and which holds most of our application objects. So I tried to set that other schema as the default one for this connection using the right-click menu on the schema dropdown. I closed the connection and reconnected. Unfortunately, the schema selected in the Unit Test Manager still was my logon schema. It changed the default schema in the Schema Browser, though. I really would like to behave it the other way around: correctly set the schema in the UTM and leave my SB default schema untouched. I know it should change in both places, so this is actually a bug report as well as a feature request :wink:

Cheers,
Julian

11

Thanks for the feedback Julian. I'll discuss this with the developers. In the mean time, just as an FYI, the "Favorites" filter (Star button at the top of the Unit Test Manager) goes across schemas. So maybe that will help for now.

Thanks again!
Keith W.