Description

felipebz

opened on Jun 18, 2019

Issue body actions

Hi!

I just tried to install the new utPLSQL v3.1.7 on Oracle Database 11.2.0.4.0 and I noticed the package ut_compound_data_helper wasn't compile correctly.

The compilation error occurs on the code below:

utPLSQL/source/expectations/data_values/ut_compound_data_helper.pkb

Lines 727 to 736 in e409664

	function get_json_diffs_type(a_diffs_all tt_json_diff_tab) return tt_json_diff_type_tab is
	l_diffs_summary tt_json_diff_type_tab := tt_json_diff_type_tab();
	begin
	select d.difference_type,count(1)
	bulk collect into l_diffs_summary
	from table(a_diffs_all) d
	group by d.difference_type;
	return l_diffs_summary;
	end;

Errors for PACKAGE BODY TESTES.UT_COMPOUND_DATA_HELPER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
730/5    PL/SQL: SQL Statement ignored
732/10   PL/SQL: ORA-22905: cannot access rows from a non-nested table item
732/16   PLS-00642: local collection types not allowed in SQL statements
--------------------------------------------------------------

This usage of TABLE(local collection) is supported only on Oracle 12c and newer.

Activity

1. lwasylow commented on Jun 19, 2019

   

   lwasylow

   on Jun 19, 2019

   Member

   More actions

   Hmm, that's weird it passed our build on 11g. We will look into why in first place it slipped through. Thanks for reporting that.
2. jgebal commented on Jun 19, 2019

   

   jgebal

   on Jun 19, 2019

   Member

   More actions

   Thanks @felipebz for reporting the issue. It's really odd it didn't fail on our 11.2 XE build.
   We will look into fixing it.
3. lwasylow commented on Jun 19, 2019

   

   lwasylow

   on Jun 19, 2019

   Member

   More actions

   Hi @felipebz , in meantime can you checkout a branch : fix/json_11_2_comp that should address a problem.
4. lwasylow

   added this to the v3.1.8 milestone on Jun 19, 2019
5. lwasylow commented on Jun 19, 2019

   

   lwasylow

   on Jun 19, 2019

   Member

   More actions

   Hi @felipebz does your database uses native or interpreted compilation?
6. felipebz commented on Jun 19, 2019

   

   felipebz

   on Jun 19, 2019

   ContributorAuthor

   More actions

   Hi @felipebz , in meantime can you checkout a branch : fix/json_11_2_comp that should address a problem.

   Yes, it fixes the issue. 👍

   Hi @felipebz does your database uses native or interpreted compilation?

   PLSQL_CODE_TYPE is defined as interpreted. But I tried to forcing native compilation (adding a alter session set plsql_code_type = 'native'; to install.sql from the official v3.1.7 release) and it doesn't help, I see the same error. I don't have any ideia why this works fine in the CI.
7. lwasylow

   added

   bug

   on Jun 19, 2019
8. felipebz commented on Jun 19, 2019

   

   felipebz

   on Jun 19, 2019

   ContributorAuthor

   More actions

   By the way, I've just tried to install 3.1.7 on Oracle XE 11g and it compiles without any errors. Querying the all_plsql_object_settings view in this XE (11.2.0.2) and my real DB (11.2.0.4) shows the same settings. That's very weird.
9. jgebal

   closed this as completedin #948on Jun 20, 2019

Issue actions