Coverage reporters failing with "operand value exceeds system limits" #368

Activity

1. Pazus

   added this to the v3.0.2 milestone on Jun 27, 2017
2. jgebal commented on Jun 27, 2017

   

   jgebal

   on Jun 27, 2017

   Last edited by jgebal

   Member

   More actions

   This is related to the upper limit of oracle nested tables (2^31) (2147483647).
   The limit is described here.

   Can you give me the results of this query from the schema where utPLSQL v3 is installed?

       select count(1)
         from user_objects o
        where o.object_name = 'UT' or object_name like 'UT\_%' escape '\'
          and o.object_type <> 'SYNONYM';
   

   Edit

   Or try to execute the following query:
   select count(1) from table( UT3.ut_utils.get_utplsql_objects_list() );

   UT3 is the schema containing the utplsql v3
3. jolmopar commented on Jun 28, 2017

   

   jolmopar

   on Jun 28, 2017

   Author

   More actions

   SQL> select count(1)
     2        from user_objects o
     3       where o.object_name = 'UT' or object_name like 'UT\_%' escape '\'
     4         and o.object_type <> 'SYNONYM';
     COUNT(1)
   ----------
          183
   

   SQL>  select count(1) from table( UT3.ut_utils.get_utplsql_objects_list() );
     COUNT(1)
   ----------
          103
   

   Forgot to mention, I have the UTPSQL v2 installed on the same DB, different schema though, just in case this can be caused by having both at the same time.
4. jgebal commented on Jun 29, 2017

   

   jgebal

   on Jun 29, 2017

   Member

   More actions

   v2 and v3 and coexist in the same DB - no issues with that.
   I cant reproduce the issue on my test DB's.
   Anything specific that you can point?
   Would you be able to provide an isolated scenario to reproduce the issue?
5. jolmopar commented on Jul 6, 2017

   

   jolmopar

   on Jul 6, 2017

   Author

   More actions

   Hi,
   I did a bit of debugging on my DB and the issue seems to be that 11g does not allow for a variable in an SQL statement to be >4000 chars.
   So, the exception was being raised in the ut_coverage package, function get_coverage_data when executing the following:

   execute immediate populate_sources_tmp_table(a_coverage_options) using l_schema_names, l_skipped_objects, a_coverage_options.include_objects;

   l_schema_names is a table of varchar2(32767) and 11g does not like it.
   If i change l_schema_names to be a table of varchar2(4000) it works fine.
6. jgebal

   added

   bug

   on Jul 7, 2017
7. jgebal commented on Jul 7, 2017

   

   jgebal

   on Jul 7, 2017

   Member

   More actions

   Hi @jolmopar
   Thanks for providing some more debug info.
   The odd thing is that we run a full set of unit tests on 11g release 2 and never encountered this issue.
   Also, as I read about the exception, it seems to be related to the actual data in the Array rather than the size limit of an array.
   But (maybe) you have a different version of 11g with different patches applied and therefore the bug is showing there.

   We will need to be careful when change to this datatype is done, as it's used in several places.
8. jgebal commented on Jul 13, 2017

   

   jgebal

   on Jul 13, 2017

   Member

   More actions

   Hi @jolmopar
   Are you able to try the solution without change to the datatype?
   Can you try the solution with pipelined function suggested here?
   That could be a pipelined function in ut_utils package (like ut_utils.pipe_table)
   The query in ut_coverage could then use syntax like:

   where s.owner in (select upper(t.column_value) from table(ut_utils.pipe_table(:l_schema_names)) t)
9. Pazus

   modified the milestones: v3.1.0, v3.0.2 on Jul 14, 2017
10. jolmopar commented on Jul 14, 2017

    

    jolmopar

    on Jul 14, 2017

    Author

    More actions

    Hi @jgebal
    That solution works fine! I added this to the ut_utils package:

      function pipe_table(a_list ut_varchar2_list) return ut_varchar2_rows pipelined is
      begin
         for i in 1..a_list.count loop
            pipe row (a_list(i));
         end loop;
         
         return;
      end pipe_table;
    

    I had to add a public synonym for the ut_utils package, otherwise I could not run the coverage reporter from a different schema other than UT3.
    Note that the returning type is ut_varchar2_rows (which is a table of 4000 bytes). I tried returning ut_varchar2_list and I got the same error message.
11. Pazus

    self-assigned this

    on Jul 15, 2017
12. Pazus

    added

    in progress

    on Jul 15, 2017
13. jgebal

    self-assigned this

    on Jul 15, 2017
14. Pazus

    closed this as completedin #407on Jul 15, 2017
15. ghost removed

    in progress

    on Jul 15, 2017