Toad Unit Test functionality fails if installed in multiple schemas

Toad for Oracle & DevOps Toolkit
1

Toad's unit test functionality allows you to install to all schemas on an instance or pick individual schemas. If you pick an individual schema (schema 2) and it is already installed in another schema on the same instance (schema 1), the schema 2 install breaks the functionality in schema 1. Running a unit test in schema 1 yields....

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 237
ORA-06512: at "SCHEMA_1.UT_SESSION_CONTEXT", line 36
ORA-06512: at "SCHEMA_1.UT_SESSION_INFO", line 109
ORA-06512: at "SCHEMA_1.UT_SESSION_INFO", line 175
ORA-06512: at "SCHEMA_1.UT_EVENT_MANAGER", line 70
ORA-06512: at "SCHEMA_1.UT_EVENT_MANAGER", line 80
ORA-06512: at "SCHEMA_1.UT_RUNNER", line 47
ORA-06512: at "SCHEMA_1.UT_RUNNER", line 177
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 141
ORA-06512: at "SCHEMA_1.UT_SESSION_CONTEXT", line 26
ORA-06512: at "SCHEMA_1.UT_SESSION_INFO", line 30
ORA-06512: at "SCHEMA_1.UT_SESSION_INFO", line 171
ORA-06512: at "SCHEMA_1.UT_EVENT_MANAGER", line 70
ORA-06512: at "SCHEMA_1.UT_EVENT_MANAGER", line 80
ORA-06512: at "SCHEMA_1.UT_RUN", line 60
ORA-06512: at "SCHEMA_1.UT_SUITE_ITEM", line 49
ORA-06512: at "SCHEMA_1.UT_RUNNER", line 172
ORA-06512: at line 23

This is because the context it uses (in this case UT_SESSION_CONTEXT) has to be associated to a given package (local to the schema it is installed to) and it is set to be access locally.

For various security policy reasons, we cannot install for all schemas, so this bug makes the unit test functionality next to useless as it has now broken the functionality. Given the individual schema install option is there as part of the installation wizard, it seems it wasn't tested very well at Quest. Any ideas how to address this?

2

Thanks, Paul. I'm looking. I can reproduce this.

3

Is there any progress on this fix? It would be nice to selectively use the Unit Test functionality on certain schemas on our instances.

4

This is a limitation in the framework introduced since we first added support for utPLSQL. There is an open GitHub issue in their project to update the documentation for instructions on multi-user install. In the meantime, you can use the undocumented "self_testing_install" flag. However, you'll need to uninstall/install utPLSQL manually.

  1. Extract utPLSQL\utPLSQL.zip from Toad's install folder.
  2. Run the uninstall_all.sql script from the source folder, enter the schema containing the framework when prompted. Do this for each schema that has the framework installed.
  3. Create a new script in the source directory for installing utPLSQL into a single schema; adjust values of owner, password, and tablespace on line 4 accordingly.
ALTER SESSION SET PLSQL_CCFLAGS = 'self_testing_install:true'
/

@@set_install_params.sql owner password tablespace

-- Comment this next line if schema already exists
@@create_utplsql_owner.sql &&ut3_owner &&ut3_password &&ut3_tablespace
@@install.sql &&ut3_owner

-- Uncomment for the DDL trigger option
-- @@install_ddl_trigger.sql &&ut3_owner

exit
  1. Run that script for each schema you want to install in.