Test for dbms_session.sleep

[VladDr85]

How to cover the sleep branch with a test: dbms_session.sleep(1). The test should work quickly.
We have the code

/*
  calculation log table
  status 0-the calculation has started and is continuing now
  status 1-the calculation was completed successfully
  status 2-the calculation failed with an error
*/
create table log_calc_tbl 
(	
  id number not null enable, 
	startdate date not null enable, 
	enddate date, 
	status number(1) not null enable, 
	id_calc_type number not null enable, 
	calcdate date
);

create or replace procedure wait_end_calcul is
  l_enddate log_calc_tbl.t_enddate%type;
  l_status  log_calc_tbl.t_status%type;
begin
  loop
    -- смотрим последний запуск за сегодня
    select max(l.t_enddate) keep(dense_rank first order by l.t_startdate desc)
          ,max(l.t_status) keep(dense_rank first order by l.t_startdate desc)
      into l_enddate
          ,l_status
      from log_calc_tbl l
     where l.t_id_calc_type = 99
       and l.t_startdate >= trunc(sysdate)
       and l.t_startdate < trunc(sysdate) + 1;
  
    if (l_status = 1 and l_enddate is not null)
    then
      --if the calculation was completed successfully then we exit
      exit;
    elsif l_status = 2
    then
      --if the calculation was completed with an error, we will return an error message
      raise_application_error(-20213,
                              'Oh, something fell');
    elsif l_status = 0 or
          l_status is null
    then
      -- if the calculation is not completed or has not started, then we are waiting
      dbms_session.sleep(60);
    end if;
  end loop;
end;
/

delete log_calc_tbl;
--calculation number 99 begins
insert into log_calc_tbl(t_id,t_startdate,t_enddate,t_status,t_id_calc_type,t_calcdate)values(1,sysdate,null,0,99,trunc(sysdate));

select * From log_calc_tbl;

begin
  wait_end_calcul;
end;

--in another session, we will set the successful completion of the calculation
update log_calc_tbl
   set t_status  = 1
      ,t_enddate = sysdate
 where t_id = 1;

--in another session, we will set an error in the calculation
update log_calc_tbl
   set t_status  = 2
      ,t_enddate = sysdate
 where t_id = 1;

Or will I have to change the production code?
Add parameters: sleep_time, loop_count

create or replace procedure wait_end_calcul(p_sleep_time number default 60, p_loop_count number default 180) is
  l_enddate log_calc_tbl.t_enddate%type;
  l_status  log_calc_tbl.t_status%type;
  i number := 0;
begin
  loop
    -- смотрим последний запуск за сегодня
    select max(l.t_enddate) keep(dense_rank first order by l.t_startdate desc)
          ,max(l.t_status) keep(dense_rank first order by l.t_startdate desc)
      into l_enddate
          ,l_status
      from log_calc_tbl l
     where l.t_id_calc_type = 99
       and l.t_startdate >= trunc(sysdate)
       and l.t_startdate < trunc(sysdate) + 1;
  
    if (l_status = 1 and l_enddate is not null)
    then
      --if the calculation was completed successfully then we exit
      exit;
    elsif l_status = 2
    then
      --if the calculation was completed with an error, we will return an error message
      raise_application_error(-20213,
                              'something fell');
    elsif i > p_loop_count
    then
      --How long can I wait? It's time to fall!
      raise_application_error(-20215,
                              'time to fall');
    elsif l_status = 0 or
          l_status is null
    then
      -- if the calculation is not completed or has not started, then we are waiting
      dbms_session.sleep(p_sleep_time);
      i := i + 1;
    end if;
  end loop;
end;
/

Change the procedure call to

begin
  wait_end_calcul(p_sleep_time => 1, p_loop_count => 1);
end;

and add test

--%test
--%throws(-20215)
procedure long_sleep;

[pesse]

What behaviour do you want to test?
Depending on that, there might be different options how to approach it with automated tests.

[VladDr85]

@pesse
I do not know how to cover only one behavior with a test: "dbms_session.sleep(60);"
When: l_status = 0 and l_enddate is null

This is a special example.
The general question is: "how to cover the condition with a test when you need to sleep for N seconds"?

I gave an example of a solution. But it requires changing the production program code.
Are there other solutions?
Without changing the production code?

[mathewbutler]

@VladDr85 sorry for jumping into the middle of this. I saw when this was raised and made a note to comment, but other stuff got in the way.

It looks like you have some concurrent action you are waiting to complete in another session.

From a design perspective you have a few options;

• let the caller trigger the work in the other session when there is work to do. The documentation lists a number of inter session communication mechanisms. A dbms_pipe, or a dbms_alert for example.
• a poll like you have, so a check and wait. Here you then need to consider the additional work that may need to follow when the seep times out.

Only you know your constraints and requirements. So, difficult to comment further on the application approach other than to say that maybe there are alternative designs that make this easier to test “fast”.

To comment on test approach and your question. I think you are asking how to make the tests run “fast” when you need the sleep. As you have pointed , to do this you need control over the duration of execution. So, exposing parameters that control the things that take time works here. Giving these default values then also reduces the impact on any calling code. You have created a “seam” through which you can test without creating too much other work for consumers of the call. (Michael Feathers book Working Effectively with Legacy Code introduces the concept of a test “seam”)

The other approach I’ve also found useful is to expose parameters that replace the use of SYSDATE. I add a parameter that defaults to SYSDATE. Again the default behaviour is that required by the application when the parameter need not be passed. This means you can take control of time in your tests, passing it in. Generalising this approach, your tests can now carefully craft data setup to verify edge cases of time to whatever granularity you need - e.g second boundaries.

In summary - fast tests don’t sleep, but when you need sleeping application code expose the controls needed to enable a fast execution.

Oh - good luck with this, and please feed back with what you end up doing, and let us know if you see anything in this area that utPLSQL could help with.

[jgebal]

Closing as we have no reply from @VladDr85 (author)