Unit tests don't run in current session

Toad for Oracle & DevOps Toolkit
1

Toad for Oracle Base Edition (64-bit)
Add-Ons:
26.1.193.8012

We have started using the unit test functionality which leverages utPLSQL. However, we have some unit tests that require credentials to be set prior to running. We can't put these in the unit test package code, as they get committed to our repo. So, we set them before running the test, however, it seems the unit test runs in a separate sessions, as it is telling us they haven't been set.

Is there a way of running in the current session context, rather than spawning a new session?

Paul

2

Any comment on this?

3

You are right, it does create a separate session to run the tests. The test-running process is threaded and we use separate sessions when using threads because otherwise you wouldn't be able to run any other SQL in Toad until it finishes.

I don't see any way to not thread it, or to use the main connection's session, or to give it additional commands to run right before the test code runs.

I do have an idea though - what if in your test suite, the first "test" just sets the credentials, and then the tests after that do the real testing? Would that work?

So, in this case - test_1 sets things up, test_2 is actual testing. Then run the suite to run it all together.

image
image278×335 12.2 KB

4

Hi John

Thanks for the reply. Our tests get committed to the code base, so wouldn't that need the credentials to be stored in the code base? If so, obviously we can't do that.

Paul

5

hm, yeah I guess so. Just trying to toss out something there.

How would you like to see it solved? Maybe to get prompted like we do in the Editor with bind/substitution variables? How do you set them in the main session? Are these package variables or are you talking about DBMS_CREDENTIAL? Or something else?

6

At the moment we call a couple of packaged stored procs, these effectively set credentials behind the scenes and setup package state, which subsequent calls use.

We could do an anonymous block and manually set credentials prior to a call to UT.RUN or whatever afterwards, but using the Unit Test Manager in Toad is super useful and intuitive, it would be nice to have some way to do it, whilst preserving the threaded aspects you allude to.

I suppose the easiest for me would be to have a code block that could be run before tests in a threaded session, perhaps it could have binds? But I'm unsure of the feasibility.

Having worked on other dev environments and languages using unit tests, it's quite common to have config that initialises everything but isn't part of the unit tests.

7

There are some setup and teardown options in there (under "Show more options" - you've probably seen that), but I guess that doesn't help you as it would be stored with the tests

8

Yes, but as you mention that would require storing credentials in the unit test, which we can't do.

If we ran UT.RUN manually, we can make a call prior to it to set credentials, an equivalent is what we need here.

9

Could you use contexts?

This might need a little fleshing out, but...

Create this package and context in your DB:

CREATE OR REPLACE PACKAGE ctx_pkg
AS
  PROCEDURE set_value(attr in varchar2, val in varchar2, clientid varchar2);
  PROCEDURE  clear_value(attr in varchar2);
END;
/
  
CREATE OR REPLACE PACKAGE BODY ctx_pkg
AS
 
  PROCEDURE set_value(attr in varchar2, val in varchar2, clientid varchar2)
  AS
  BEGIN
     DBMS_SESSION.SET_CONTEXT(
      namespace  => 'MY_CONTEXT',
      attribute  => attr,
      value      => val,
      username   => null,
      client_id  => clientid);
  END set_value;

  PROCEDURE clear_value(attr in varchar2)
  AS
  BEGIN
    DBMS_SESSION.CLEAR_CONTEXT('MY_CONTEXT', attr);
  END clear_value;
    
END;
/

CREATE or replace CONTEXT my_context USING ctx_pkg accessed globally;

Then in any session on the DB, run this before you start testing

begin
  ctx_pkg.set_value('attr_name', 'attr_value', 'client_id');
end;
-- attr_name is a variable name
-- attr_value is the value you want for it
-- client_id must be known by a session that wants to read it
-- call ctx_pkg.set_value as many times as needed with different attr_names for all your package variables

Then, in your test setup

begin
  DBMS_SESSION.SET_IDENTIFIER('client_id');
  -- use whatever value you specified in the other session
end;

select SYS_CONTEXT('MY_CONTEXT', 'attr_name')
from dual;
-- attr_value is returned.  You could set your package variables with it, then move on to the tests.
10

Yes, I did consider contexts with the approach you mention, but storing sensitive, unencrypted credentials in shared SGA memory seemed too risky, especially when working with banks and financial institutions. Also, that only addresses this one scenario, whereas I can think of other cases where you may need to run specific session config, that you wouldn't want to be part of the unit test.