Coverage not being reported when DBMS_SCHEDULER.RUN_JOB is in tests

[jasonlyle88]

Describe the bug
When running code coverage, I get 0% code coverage is DBMS_SCHEDULER.RUN_JOB is called in the tests

below is my test:

begin
    dbms_scheduler.run_job(
        job_name            =>  c_full_job_name,
        use_current_session =>  true
    );

    select status
    into l_actual
    from all_scheduler_job_run_details
    where owner = c_owner
        and job_name = c_job_name
    order by log_date desc
    fetch first 1 rows only;

    ut.expect(l_actual).to_equal(c_succeeded);
end check_purge_job_runs;

commenting out the DBMS_SCHEDULER.RUN_JOB call returns all the code coverages to expected values.

Provide version info
Information about utPLSQL and Database version,

SQL> set serveroutput on
SQL> declare
  2    l_version varchar2(255);
  3    l_compatibility varchar2(255);
  4  begin
  5    dbms_utility.db_version( l_version, l_compatibility );
  6    dbms_output.put_line( l_version );
  7    dbms_output.put_line( l_compatibility );
  8  end;
  9* /
19.0.0.0.0
19.0.0


PL/SQL procedure successfully completed.

SQL> select substr(ut.version(),1,60) as ut_version from dual;

UT_VERSION
_______________
v3.1.12.3589

SQL> select * from v$version;

BANNER                                                                    BANNER_FULL                                                                                   BANNER_LEGACY                                                                CON_ID
_________________________________________________________________________ _____________________________________________________________________________________________ _________________________________________________________________________ _________
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.1.0    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production            0

SQL> select * from nls_session_parameters;

PARAMETER                  VALUE
__________________________ _______________________________
NLS_LANGUAGE               AMERICAN
NLS_TERRITORY              AMERICA
NLS_CURRENCY               $
NLS_ISO_CURRENCY           AMERICA
NLS_NUMERIC_CHARACTERS     .,
NLS_CALENDAR               GREGORIAN
NLS_DATE_FORMAT            DD-MON-RR
NLS_DATE_LANGUAGE          AMERICAN
NLS_SORT                   BINARY
NLS_TIME_FORMAT            HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT       DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT         HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT    DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY          $
NLS_COMP                   BINARY
NLS_LENGTH_SEMANTICS       BYTE
NLS_NCHAR_CONV_EXCP        FALSE

17 rows selected.

SQL> select substr(dbms_utility.port_string,1,60) as port_string from dual;

PORT_STRING
______________________
x86_64/Linux 2.4.xx

Information about client software
This was run in SQLDeveloper and SQLcl (latest versions)

To Reproduce
Create a test that runs any job with DBMS_SCHEDULER.RUN_JOB

Expected behavior
Code coverage to be reported correctly

[lwasylow]

Hi @jasonlyle88
So to clarify, as from the issue is not clear on what your are expecting coverage.
You running some procedure let's call it procedure1 via job scheduler and don't get results in code coverage.
When you run tests for same procedure without scheduler coverage on procedure1 is reported correctly?
Have you checked a content of the dbms profiler tables in framework schema? We are using profiler to run coverage.

[jasonlyle88]

Apologies, you are correct, i was not clear on the coverage.

Lets say I have the following:

PKG_A
PKG_B
PKG_C

TEST_PKG_A (unit test suite for PKG_A)
TEST_PKG_B (unit test suite for PKG_A)
TEST_PKG_C (unit test suite for PKG_C)

Without DBMS_SCHEDULER.RUN_JOB anywhere, I can run these tests either individual or as a group and get 95% coverage for the associated package.

Now, lets say I put a test that calls DBMS_SCHEDULER.RUN_JOB in TEST_PKG_C. If I run all the test packages together, I will have 0% coverage reported on PKG_A, PKG_B, and PKG_C. If I run just TEST_PKG_C, I will get 0% coverage on PKG_C. If I run TEST_PKG_A and TEST_PKG_B, I will get 95% coverage for each PKG_A and PKG_B.

So if any test is included that runs DBMS_SCHEDULER.RUN_JOB, all coverage is always reported as 0%. I would expect PKG_A, PKG_B, and PKG_C to all report 95% coverage still.

In my case, there was an instance where a job was release with a product with a typo in the job definition, so the job always failed, no matter what. So What I want to prove out here is just that the job runs successfully by running the job with DBMS_SCHEDULER.RUN_JOB and checking the result of the run from all_scheduler_job_run_details.

If the job runs code in something for which I am reporting coverage, I would also think that code execution would count towards the line coverage.

I have not checked the content of the DBMS_PROFILER tables in the framework schema, I didn't know where to look before you said that!

Hope this helps clear things up, please feel free to as for more clarification if not!

[lwasylow]

You are correct.
From what I can see the profile not profiling any run within a scheduler job and not data is collected.
It might be an issue with way scheduler works. I don't believe this is an utPLSQL issue but rather oracle internal problem ( if it is a problem indeed )

[jasonlyle88]

If profiler is not profiling any run inside scheduler, then that is one issue (if it is a problem). And it would be an oracle issue, not your issue.

However, all usage reporting being lost from other test runs in UTPLSQL is still potentially a UTPLSQL issue. What I mean by this is the use case explained above where I run all test packages, but 0% usage is reported even though there are tests not running jobs that should be collecting profiler data.

[lwasylow]

Hi @jasonlyle88 , sorry have not chance to finish :) Clicked entered and you replied while was editing comment.
From what I can see the profile not profiling any run within a scheduler job and not data is collected.
It might be an issue with way scheduler works. I don't believe this is an utPLSQL issue but rather oracle internal problem ( if it is a problem indeed )
Please consider this test case for dummy proc

create or replace PROCEDURE ptest IS
 l_number number;
BEGIN
  select 1 into l_number from dual;
END;
/

And execute

delete from ut3_develop.PLSQL_PROFILER_DATA;
delete from ut3_develop.PLSQL_PROFILER_UNITS;
delete from ut3_develop.PLSQL_PROFILER_RUNS;
commit;

DECLARE
  l_result  BINARY_INTEGER;
BEGIN
  l_result := DBMS_PROFILER.start_profiler(run_comment => 'do_something: ' || SYSDATE);
  ptest;
  l_result := DBMS_PROFILER.stop_profiler;
END;
/

select *  from PLSQL_PROFILER_DATA;
select *  from PLSQL_PROFILER_UNITS;
select *  from PLSQL_PROFILER_RUNS;
`

We can see a units provided in the run.

Then we execute our scheduler
```sql
delete from ut3_develop.PLSQL_PROFILER_DATA;
delete from ut3_develop.PLSQL_PROFILER_UNITS;
delete from ut3_develop.PLSQL_PROFILER_RUNS;
commit;

DECLARE
  l_result  BINARY_INTEGER;
BEGIN
  l_result := DBMS_PROFILER.start_profiler(run_comment => 'do_something: ' || SYSDATE);
  DBMS_SCHEDULER.RUN_JOB(job_name => '"UT3_DEVELOP"."TEST"', USE_CURRENT_SESSION => TRUE);
  l_result := DBMS_PROFILER.stop_profiler;
END;
/

select *  from PLSQL_PROFILER_DATA;
select *  from PLSQL_PROFILER_UNITS;
select *  from PLSQL_PROFILER_RUNS;

As you can see there are no data created, even more interesting a run in profiler takes 0sec which indicates a is being closed by something outside us.

[lwasylow]

To prove fact that is being closed abruptly you can put : dbms_session.sleep(5); before l_result := DBMS_PROFILER.stop_profiler; and you can see that run time is null for dbms_scheduler where in other case is not.

[jasonlyle88]

That is very interesting! It does indeed to be all on the Oracle side. Great debugging, thanks for the help getting through that. I had not considered putting the DBMS_SCHEDULER_RUN.RUN_JOB in a helper procedure with pragma autonomous. I can give that a try and let you know if it works for not!

[lwasylow]

Hi @jasonlyle88 I think I found a workaround for you.
Using a direct autonomous doesn't solve the problem however I think we been testing similar behaviour in our framework as a self testing mechanism.

If you try this it's working. I can assume is due to fact that a profile has to be start and stopped within a session and even scheduler is using "same session" it possibly only attach itself.

create or replace PROCEDURE ptest IS
 l_number number;
BEGIN
  select 1 into l_number from dual;
END;
/

create or replace package test_ptest as

  -- %suite
  -- %displayname(Scheduler cov)
  
  -- %test
  -- %displayname(test me)
  procedure test_ptestproc;
  
  -- %test
  -- %displayname(test me 1)
  procedure test_ptestproc1;  
  
end;
/


create or replace package body test_ptest as
  
 procedure run_job is
    l_coverage_run_id raw(32767);
    pragma autonomous_transaction;
  begin
    ut_runner.coverage_start(l_coverage_run_id);
    DBMS_SCHEDULER.RUN_JOB(job_name => 'TEST', use_current_session => true);
    ut_runner.coverage_stop();
  end;
  
  procedure test_ptestproc is
  begin
    run_job;
    ut3_develop.ut.expect( 1 ).to_( equal(1) );
  end;

  procedure test_ptestproc1 is
  begin
    ptest;
    ut3_develop.ut.expect( 1 ).to_( equal(1) );
  end;

end;
/

and then executing:

select *  from table(ut.run(ut_coverage_html_reporter()));

Produce a correct results.
e.g.

...
<div class="source_table" id="738C9A9AB3B9CCB3B488306B6395C96D"><div class="header"> <h3>UT3_DEVELOP.PTEST</h3><h4><span class="green">100 %</span> lines covered</h4><div> <b>1</b> relevant lines. <span class="green"><b>1</b> lines covered</span> and <span class="red"><b>0</b> lines missed</span></div></div><pre><ol>
...

In the test where we run_job you can check for success of your scheduler as a test of course.
Hope that helps.

[jasonlyle88]

Wow, awesome! Thank you so much. I definitely would not have gotten that. I will give this a try and report back!

[jasonlyle88]

Hey @lwasylowm,

I think that is working because DBMS_SCHEDULER.RUN_JOB isn't actually getting executed. I switched from the ut_coverage_html_reporter to the ut_documentation_reporter and it fails on the ut_runner.coverage_start(l_coverage_run_id); line. Therefore it never gets to the DBMS_SCHEDULER.RUN_JOB command.

Any further thoughts?

[lwasylow]

You right it was missing:
l_coverage_run_id raw(32) := sys_guid();
But that causes to fail. It looks like somehow scheduler closes reporter before executing.
I will have a think about it and play around.

[lwasylow]

What's funny is that creation of the job and execution works ok :)

 procedure run_job is
    l_coverage_run_id raw(32) := sys_guid();
    pragma autonomous_transaction;
  begin
    ut3_develop.ut_runner.coverage_start(l_coverage_run_id);
    dbms_scheduler.create_job(
      job_name      =>  'TEST2',
      job_type      =>  'PLSQL_BLOCK',
      job_action    =>  'begin ptest; end;',
      start_date    =>  sysdate,
      enabled       =>  TRUE,
      auto_drop     =>  TRUE,
      comments      =>  'one-time-job'
      );
    ut3_develop.ut_runner.coverage_stop();
    commit;
  end;


618	29-SEP-23 10.06.18.734013000 PM GMT	UT3_DEVELOP	TEST2		SUCCEEDED

[JensKaschuba]

Hello, I have similar problems:
I have a call to dbms_scheduler.run_job in a procedure to be tested.
When I run the test I got the attached output. The call is in the test "Create ApexWorkspace"
DBMSOutput.txt
How can I run this test suite correctly? The test "Create APEXWorkspace" is needed for the following tests ...