Compilation error in ut_compound_data_helper

[felipebz]

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.

[lwasylow]

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.

[jgebal]

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.

[lwasylow]

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

[lwasylow]

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

[felipebz]

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.

[felipebz]

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.