Code coverage is not working properly

Question

Code coverage is not working properly

horimre opened this issue 6 years ago · 23 comments

Hi,
When I right click on a specific package/procedure/function and try to run code coverage, it does not do anything.
Code coverage is only working when I select the whole DB.
Is this behaviour intentional?
I tried with SQLDeveloper v19.4.0 extension v1.1.0 and 1.1.1.
Thanks,
Imre

Answer 1 · 2020-04-27T15:56:59.000Z

Hi @horimre,

I've installed plscope-utils and then right-clicked on plscope.test_lineage_util and got this dialog:

and then pressed Run and after a moment the following result was shown in my browser:

So, it should work to run code coverage for single test package only. And it should work for multiple test packages as well.

I've used Oracle Database 19.7. But it should work also in 12.2.

What do you mean with

it does not do anything?

No dialog? No result? both? Does Run utPLSQL test work for the same test package?

Thanks,
Philipp

Answer 2 · 2020-04-27T16:30:40.000Z

Hi,

There is no dialog, and no result.
After I click Code coverage... the context menu just disappears.
Run utPLSQL test is working for the same package.
Maybe the issue is that we have a very old utPLSQL install on the DB. It is v3.1.9.3270.
I am using Oracle Database 18c.

Thanks,
Imre

Answer 3 · 2020-04-27T16:35:32.000Z

Thanks for the details. utPLSQL v3.1.9 is good enough. That should not be the problem.
And it's working when you run it from the connection or package node, right?

Answer 4 · 2020-04-27T16:43:05.000Z

Right, it is working when I run it from the connection. The dialog shows up I can run it and see the report.

Answer 5 · 2020-04-27T17:42:27.000Z

Could you please run the following query?

Please amend the lines commented with --2) and --3) and run it with all_dependencies in case it fails with dba_dependencies.

select referenced_owner || '.' || referenced_name AS dep_name
  from dba_dependencies                    -- 1) dba_ or all_
  WHERE owner = upper('PLSCOPE')           -- 2) owner
    AND name = upper('TEST_LINEAGE_UTIL')  -- 3) test package name
    AND referenced_owner NOT IN (
           'SYS', 'SYSTEM', 'XS$NULL', 'OJVMSYS', 'LBACSYS', 'OUTLN', 'SYS$UMF', 
           'DBSNMP', 'APPQOSSYS', 'DBSFWUSER', 'GGSYS', 'ANONYMOUS', 'CTXSYS', 
           'SI_INFORMTN_SCHEMA', 'DVF', 'DVSYS', 'GSMADMIN_INTERNAL', 'ORDPLUGINS', 
           'MDSYS', 'OLAPSYS', 'ORDDATA', 'XDB', 'WMSYS', 'ORDSYS', 'GSMCATUSER', 
           'MDDATA', 'REMOTE_SCHEDULER_AGENT', 'SYSBACKUP', 'GSMUSER', 'APEX_PUBLIC_USER', 
           'SYSRAC', 'AUDSYS', 'DIP', 'SYSKM', 'ORACLE_OCM', 'APEX_INSTANCE_ADMIN_USER', 
           'SYSDG', 'FLOWS_FILES', 'ORDS_METADATA', 'ORDS_PUBLIC_USER'
        )
    AND referenced_owner NOT LIKE 'APEX\_______'
    AND referenced_type IN ('PACKAGE', 'TYPE', 'PROCEDURE', 'FUNCTION', 'TRIGGER');

Please let me know your database version as well and if you are running it in a PDB.

Thank you.

Answer 6 · 2020-04-27T18:09:51.000Z

DEP_NAME

UTPLSQL.UT_EXPECTATION
USER1.TEST_PKG
USER1.TEST_PKG_UT

Oracle Database 18c (18.0.0.0.0), running it in a PDB.

Answer 7 · 2020-04-27T18:33:35.000Z

Thanks. And, does the query work?

Answer 8 · 2020-04-27T18:43:03.000Z

yes, that is the result of the query:

DEP_NAME

UTPLSQL.UT_EXPECTATION
USER1.TEST_PKG
USER1.TEST_PKG_UT

Answer 9 · 2020-04-27T19:05:21.000Z

Nevermind, this must be some sort of setup issue on my side. The result and runtime should be the same when I run it from the connection and provide the package name in the include objects section. I'll just keep running it that way.

Answer 10 · 2020-04-27T22:29:48.000Z

Yes, that's a workaround.

However, I consider it a bug, when the code coverage dialog does not show up when called from a test package node. To fix it, we have to reproduce it.

Could you please open View -> Log and check if there is a utPLSQL related error message in the Logging Page tab?

Answer 11 · 2020-04-28T08:16:17.000Z

There is no error message in the log. It is empty.
Code coverage is also working when I run it from the Packages node.
Btw, I am using jdk1.8.0_241.

Answer 12 · 2020-04-28T17:00:29.000Z

I'm running out of ideas. I cannot reproduce it on my side. I guess I could provide an intermediate version of the extension with extensive logging to narrow down the problem area. Let me know if you are interested to dig deeper. Thanks.

Answer 13 · 2020-04-28T19:22:42.000Z

Okay I'm in.

Answer 14 · 2020-04-28T22:01:30.000Z

Perfect. Thank you. Could you please do as the following:

1. Configure Logging in SQL Developer 19.4.0

In the sqldeveleloper/bin directory rename the existing logging.conf file to logging.conf.ori and create a logging.conf file with this content.

This is basically the default behaviour plus full logging for utPLSQL.

2. Update utPLSQL for SQL Developer

Download utplsql_for_SQLDev_1.1.2-SNAPSHOT.zip and install it.

This will require a restart and after that the new logging settings will be active.

3. Open View -> Log

Click on the "Messages" tab.

4. Open Code Coverage for a package

Right click on a test package and select `Code coverage...``

5. Check logging output

In my case the log output looks like this:

2020-04-28 23:55:49 FINER org.utplsql.sqldev.menu.UtplsqlController: handle utplsql.coverage
2020-04-28 23:55:49 FINER org.utplsql.sqldev.menu.UtplsqlController: Code coverage from view oracle.dbtools.raptor.navigator.db.DBNavigatorWindow and node oracle.dbtools.raptor.navigator.plsql.PlSqlNode.
2020-04-28 23:55:49 FINER org.utplsql.sqldev.menu.UtplsqlController: Code coverage from DB navigator
2020-04-28 23:55:49 FINE org.utplsql.sqldev.menu.UtplsqlController: url: sqldev.nav:IdeConnections%2523plscope-odb-macphs//PLSCOPE/PACKAGE/TEST_LINEAGE_UTIL
2020-04-28 23:55:49 FINER org.utplsql.sqldev.menu.UtplsqlController: url: sqldev.nav:IdeConnections%2523plscope-odb-macphs//PLSCOPE/PACKAGE/TEST_LINEAGE_UTIL
2020-04-28 23:55:49 FINE org.utplsql.sqldev.menu.UtplsqlController: connectionName: IdeConnections#plscope-odb-macphs
2020-04-28 23:55:49 FINE org.utplsql.sqldev.menu.UtplsqlController: path: PLSCOPE.TEST_LINEAGE_UTIL
2020-04-28 23:55:49 FINER org.utplsql.sqldev.menu.UtplsqlController: pathList: [PLSCOPE.TEST_LINEAGE_UTIL]
2020-04-28 23:55:49 FINER org.utplsql.sqldev.menu.UtplsqlController: includeObjectList: [PLSCOPE.COL_LINEAGE_TYPE, PLSCOPE.COL_TYPE, PLSCOPE.LINEAGE_UTIL, PLSCOPE.TEST_LINEAGE_UTIL, PLSCOPE.T_COL_LINEAGE_TYPE, PLSCOPE.T_COL_TYPE, UT3.UT_EXPECTATION, UT3.UT_EXPECTATION_COMPOUND]
2020-04-28 23:55:49 FINER org.utplsql.sqldev.menu.UtplsqlController: showing code coverage dialog
2020-04-28 23:55:49 FINER org.utplsql.sqldev.menu.UtplsqlController: code coverage dialog shown

I expect that your logging output contains an error message. That hopefully should help us to determine the next steps.

Please post the output after handle utplsql.coverage here. Thank you very much.

Answer 15 · 2020-04-29T08:48:56.000Z

It seems that it fails because I do not have privilege to access dba_dependencies. The query runs with all_dependencies though.

2020-04-29 10:25:31 SEVERE org.utplsql.sqldev.menu.UtplsqlController: Failed to handle event due to exception PreparedStatementCallback; bad SQL grammar [select referenced_owner || '.' || referenced_name AS dep_name
  from dba_dependencies
  WHERE owner = upper(?)
    AND name = upper(?)
    AND referenced_owner NOT IN (
           'SYS', 'SYSTEM', 'XS$NULL', 'OJVMSYS', 'LBACSYS', 'OUTLN', 'SYS$UMF', 
           'DBSNMP', 'APPQOSSYS', 'DBSFWUSER', 'GGSYS', 'ANONYMOUS', 'CTXSYS', 
           'SI_INFORMTN_SCHEMA', 'DVF', 'DVSYS', 'GSMADMIN_INTERNAL', 'ORDPLUGINS', 
           'MDSYS', 'OLAPSYS', 'ORDDATA', 'XDB', 'WMSYS', 'ORDSYS', 'GSMCATUSER', 
           'MDDATA', 'REMOTE_SCHEDULER_AGENT', 'SYSBACKUP', 'GSMUSER', 'APEX_PUBLIC_USER', 
           'SYSRAC', 'AUDSYS', 'DIP', 'SYSKM', 'ORACLE_OCM', 'APEX_INSTANCE_ADMIN_USER', 
           'SYSDG', 'FLOWS_FILES', 'ORDS_METADATA', 'ORDS_PUBLIC_USER'
        )
    AND referenced_owner NOT LIKE 'APEX\_______'
    AND referenced_type IN ('PACKAGE', 'TYPE', 'PROCEDURE', 'FUNCTION', 'TRIGGER')
]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Answer 16 · 2020-04-29T09:04:33.000Z

That's strange. I check the accessibility to dba_ views with the following query:

SELECT 1
  FROM dba_objects
 WHERE 1=2

If the query fails with an exception then all_ views are used, otherwise dba_views.

Does this query work with the user you are testing? I mean access to dba_objects are allowed but access to dba_depencencies are not?

Answer 17 · 2020-04-29T09:18:39.000Z

Yes, exactly. The user I am using can access dba_objects, but not dba_dependencies.
Okay, so we have a strange setup then. It is not really a bug.
I know it is a very rare case, but what if you handle this exception and try to query all_dependencies in this case.

Answer 18 · 2020-04-29T09:25:40.000Z

Ok, thank you for the feedback. Happy we found the culprit. The check is a bit naïve, so I have to improve it. I'll read the documentation and think about a good alternative.

The dba_ views are faster (less security checks) that's why we try to use them, if possible. But in the end such an optimization must not lead to a loss of service as in this case.

I'll look into it later this evening and will update this issue.

Answer 19 · 2020-04-29T09:30:48.000Z

okay, sure thank you!

Answer 20 · 2020-04-29T19:14:24.000Z

@horimre I think I found a solution.

Could you please download the updated utplsql_for_SQLDev_1.1.2-SNAPSHOT.zip and install it? Please let and let me know if it works in your environment.

Thank you.

Answer 21 · 2020-04-29T20:16:18.000Z

@PhilippSalvisberg it is working now, thank you! 🙂

The code coverage is running for a very long time though.

I have created a package which contains a dummy function (adds 1 to a number parameter and returns the result) then created a test package with a single unit test for the function.

It took 30 seconds for the dialog to pop up and approx 4-5mins for the coverage to finish and display the results.

I have also tried to run it from sql instead of using the extension, but it also took approximately 3 minutes to finish. I used the below commands.

set serveroutput on;
begin
  ut.run(
     ut_coverage_html_reporter(),
     a_include_objects => ut_varchar2_list('test_pkg')
  );
end;
/

I am sure it is partly because my environment is a bit slow.
Is the performance better for you?

Thanks,
Imre

Answer 22 · 2020-04-29T21:00:09.000Z

Is the performance better for you?

Yes. As you see in the logs (it contains also my time to click on the Run button):

2020-04-29 22:43:06.272 FINER org.utplsql.sqldev.menu.UtplsqlController: handle utplsql.coverage
2020-04-29 22:43:06.272 FINER org.utplsql.sqldev.menu.UtplsqlController: Code coverage from view oracle.dbtools.raptor.navigator.db.DBNavigatorWindow and node oracle.dbtools.raptor.navigator.plsql.PlSqlNode.
2020-04-29 22:43:06.273 FINER org.utplsql.sqldev.menu.UtplsqlController: Code coverage from DB navigator
2020-04-29 22:43:06.273 FINE org.utplsql.sqldev.menu.UtplsqlController: url: sqldev.nav:IdeConnections%2523plscope-odb-macphs//PLSCOPE/PACKAGE/TEST_LINEAGE_UTIL
2020-04-29 22:43:06.273 FINE org.utplsql.sqldev.menu.UtplsqlController: connectionName: IdeConnections#plscope-odb-macphs
2020-04-29 22:43:06.273 FINE org.utplsql.sqldev.menu.UtplsqlController: path: PLSCOPE.TEST_LINEAGE_UTIL
2020-04-29 22:43:06.273 FINER org.utplsql.sqldev.menu.UtplsqlController: pathList: [PLSCOPE.TEST_LINEAGE_UTIL]
2020-04-29 22:43:06.358 FINER org.utplsql.sqldev.menu.UtplsqlController: includeObjectList: [PLSCOPE.COL_LINEAGE_TYPE, PLSCOPE.COL_TYPE, PLSCOPE.LINEAGE_UTIL, PLSCOPE.TEST_LINEAGE_UTIL, PLSCOPE.T_COL_LINEAGE_TYPE, PLSCOPE.T_COL_TYPE, UT3.UT_EXPECTATION, UT3.UT_EXPECTATION_COMPOUND]
2020-04-29 22:43:06.594 FINER org.utplsql.sqldev.menu.UtplsqlController: showing code coverage dialog
2020-04-29 22:43:06.594 FINER org.utplsql.sqldev.menu.UtplsqlController: code coverage dialog shown
2020-04-29 22:43:07.768 FINE org.utplsql.sqldev.coverage.CodeCoverageReporter: Running code coverage reporter for [PLSCOPE.TEST_LINEAGE_UTIL]...
2020-04-29 22:43:08.457 FINE org.utplsql.sqldev.coverage.CodeCoverageReporter: Writing result to /var/folders/lf/8g3r0ts900gfdfn2xxkn9yz00000gn/T/utplsql_9152831904887327166.html...
2020-04-29 22:43:08.459 FINE org.utplsql.sqldev.coverage.CodeCoverageReporter: Opening file:/var/folders/lf/8g3r0ts900gfdfn2xxkn9yz00000gn/T/utplsql_9152831904887327166.html in browser...
2020-04-29 22:43:08.496 FINE org.utplsql.sqldev.coverage.CodeCoverageReporter: file:/var/folders/lf/8g3r0ts900gfdfn2xxkn9yz00000gn/T/utplsql_9152831904887327166.html opened in browser.

One thing is for sure the access to the all_ views instead of the dba_ views. Ensure you get access to

dba_objects
dba_synonyms
dba_dependencies

and you will experience better performance. I suggest to get the grants.

Look at the statement logs. SQL Developer logs every statement with the time it took. It should reveal the culprit and you should get a better idea where having access to the dba_ views is beneficial.

When the code coverage is slow in the worksheet as well, then the issue is not related to the SQL Developer extension.

Since, the original issue (code coverage not working from a test package node) is solved, I'm going to close this issue.

Thanks for reporting this bug and your help in finding the cause.

Answer 23 · 2020-04-29T21:17:56.000Z

sure, thank you for fixing the issue in such a short time, and for your advices on performance as well!