Skip to content

Fixed issue with implicit cursor #646

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
merged 1 commit into from
Apr 21, 2018
Merged

Fixed issue with implicit cursor #646

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
14 changes: 14 additions & 0 deletions source/core/ut_utils.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -544,5 +544,19 @@ procedure append_to_clob(a_src_clob in out nocopy clob, a_clob_table t_clob_tab,
return l_filtered_list;
end;

function xmlgen_escaped_string(a_string in varchar2) return varchar2 is
l_result varchar2(4000);
l_sql varchar2(32767) := q'!select q'[!'||a_string||q'!]' as "!'||a_string||'" from dual';
begin
if a_string is not null then
select extract(dbms_xmlgen.getxmltype(l_sql),'/*/*/*').getRootElement()
into l_result
from dual;
else
l_result := a_string;
end if;
return l_result;
end;

end ut_utils;
/
3 changes: 3 additions & 0 deletions source/core/ut_utils.pks
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -302,5 +302,8 @@ create or replace package ut_utils authid definer is
/*It takes a collection of type ut_varchar2_list and it only returns the elements which meets the regular expression*/
function filter_list(a_list IN ut_varchar2_list, a_regexp_filter in varchar2) return ut_varchar2_list;

-- Generates XMLGEN escaped string
function xmlgen_escaped_string(a_string in varchar2) return varchar2;

end ut_utils;
/
50 changes: 33 additions & 17 deletions source/expectations/data_values/ut_compound_data_helper.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -55,7 +55,12 @@ create or replace package body ut_compound_data_helper is
a_cursor := dbms_sql.to_refcursor( l_cursor_number );
l_columns_tab := get_columns_info( l_columns_desc, l_columns_count);

select XMLELEMENT("ROW", xmlagg(xmlelement(evalname key, value))) into l_result from table(l_columns_tab );
select
XMLELEMENT("ROW", xmlagg(xmlelement(evalname ut_utils.xmlgen_escaped_string(key),
XMLATTRIBUTES(key AS "xml_valid_name"),
value)))
into l_result
from table(l_columns_tab );

return l_result;
end;
Expand Down Expand Up @@ -89,31 +94,41 @@ create or replace package body ut_compound_data_helper is
l_sql varchar2(32767);
l_results tt_column_diffs;
begin
l_column_filter := ut_compound_data_helper.get_columns_filter(a_exclude_xpath, a_include_xpath);
l_column_filter := get_columns_filter(a_exclude_xpath, a_include_xpath);
l_sql := q'[
with
expected_cols as ( select :a_expected as item_data from dual ),
actual_cols as ( select :a_actual as item_data from dual ),
expected_cols_info as (
expected_cols_info as (
select e.*,
replace(expected_type,'VARCHAR2','CHAR') expected_type_compare
from (
select rownum expected_pos,
r.column_value.getrootelement() expected_name,
extractvalue(r.column_value,'/*') expected_type
from ( select ]'||l_column_filter||q'[ from expected_cols ucd ) s,
table( xmlsequence(extract(s.item_data,'/*/*')) ) r
SELECT rownum expected_pos,
xt.name expected_name,
xt.type expected_type
FROM (select ]'||l_column_filter||q'[ from expected_cols ucd) x,
XMLTABLE('/ROW/*'
PASSING x.item_data
COLUMNS
name VARCHAR2(4000) PATH '@xml_valid_name',
type VARCHAR2(4000) PATH '/'
) xt
) e
),
actual_cols_info as (
select a.*,
replace(actual_type,'VARCHAR2','CHAR') actual_type_compare
from (
select rownum actual_pos,
r.column_value.getrootelement() actual_name,
extractvalue(r.column_value,'/*') actual_type
from ( select ]'||l_column_filter||q'[ from actual_cols ucd ) s,
table( xmlsequence(extract(s.item_data,'/*/*')) ) r
SELECT rownum actual_pos,
xt.name actual_name,
xt.type actual_type
FROM (select ]'||l_column_filter||q'[ from actual_cols ucd) x,
XMLTABLE('/ROW/*'
PASSING x.item_data
COLUMNS
name VARCHAR2(4000) PATH '@xml_valid_name',
type VARCHAR2(4000) PATH '/'
) xt
) a
),
joined_cols as (
Expand Down Expand Up @@ -152,7 +167,7 @@ create or replace package body ut_compound_data_helper is
l_column_filter varchar2(32767);
l_results tt_row_diffs;
begin
l_column_filter := get_columns_filter(a_exclude_xpath, a_include_xpath);
l_column_filter := get_columns_filter(a_exclude_xpath,a_include_xpath);
execute immediate q'[
with
diff_info as (select item_no from ut_compound_data_diff_tmp ucdc where diff_id = :diff_guid and rownum <= :max_rows)
Expand Down Expand Up @@ -231,14 +246,14 @@ create or replace package body ut_compound_data_helper is
a_hash_type binary_integer := dbms_crypto.hash_sh1
) return t_hash is
l_cols_hash t_hash;
begin
if not a_data_value_cursor.is_null then
begin
if not a_data_value_cursor.is_null then
execute immediate
q'[select dbms_crypto.hash(replace(x.item_data.getclobval(),'>CHAR<','>VARCHAR2<'),]'||a_hash_type||') ' ||
' from ( select '||get_columns_filter(a_exclude_xpath, a_include_xpath)||
' from (select :columns_info as item_data from dual ) ucd' ||
' ) x'
into l_cols_hash using a_exclude_xpath, a_include_xpath, a_data_value_cursor.columns_info;
into l_cols_hash using a_exclude_xpath,a_include_xpath, a_data_value_cursor.columns_info;
end if;
return l_cols_hash;
end;
Expand All @@ -263,5 +278,6 @@ begin
g_type_name_map( dbms_sql.number_type ) := 'NUMBER';
g_type_name_map( dbms_sql.rowid_type ) := 'ROWID';
g_type_name_map( dbms_sql.urowid_type ) := 'UROWID';

end;
/
2 changes: 1 addition & 1 deletion source/expectations/data_values/ut_data_value_refcursor.tpb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -140,7 +140,7 @@ create or replace type body ut_data_value_refcursor as
for i in 1 .. a_column_diffs.count loop
if a_column_diffs(i).diff_type in ('-','+') then
l_incomparable_cols.extend;
l_incomparable_cols(l_incomparable_cols.last) := coalesce(a_column_diffs(i).expected_name,a_column_diffs(i).actual_name);
l_incomparable_cols(l_incomparable_cols.last) := ut_utils.xmlgen_escaped_string(coalesce(a_column_diffs(i).expected_name,a_column_diffs(i).actual_name));
end if;
end loop;
l_result := ut_utils.to_xpath(l_incomparable_cols);
Expand Down
41 changes: 41 additions & 0 deletions test/core/expectations/compound_data/test_expectations_cursor.pkb
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -963,5 +963,46 @@ Rows: [ 4 differences ]
ut.expect(ut3.ut_expectation_processor.get_warnings()(1)).to_be_like('The syntax: "%" is deprecated.%');
end;

procedure column_diff_on_col_name_implicit is
l_actual SYS_REFCURSOR;
l_expected SYS_REFCURSOR;
l_actual_message varchar2(32767);
l_expected_message varchar2(32767);
begin
--Arrange
open l_actual for select '1' , '2' from dual connect by level <=2;
open l_expected for select rownum , rownum expected_column_name from dual connect by level <=2;
--Act
ut3.ut.expect(l_actual).to_equal(l_expected);

l_expected_message := q'[Actual: refcursor [ count = 2 ] was expected to equal: refcursor [ count = 2 ]%
Diff:%
Columns:%
Column <ROWNUM> [data-type: NUMBER] is missing. Expected column position: 1.%
Column <EXPECTED_COLUMN_NAME> [data-type: NUMBER] is missing. Expected column position: 2.%
Column <'1'> [position: 1, data-type: CHAR] is not expected in results.%
Column <'2'> [position: 2, data-type: CHAR] is not expected in results.%
Rows: [ 2 differences ]%
All rows are different as the columns are not matching.%]';
l_actual_message := ut3.ut_expectation_processor.get_failed_expectations()(1).message;
--Assert
ut.expect(l_actual_message).to_be_like(l_expected_message);
end;

procedure column_match_on_col_name_implicit is
l_actual SYS_REFCURSOR;
l_expected SYS_REFCURSOR;
l_actual_message varchar2(32767);
l_expected_message varchar2(32767);
begin
--Arrange
open l_actual for select '1' , rownum from dual connect by level <=2;
open l_expected for select '1' , rownum from dual connect by level <=2;
--Act
ut3.ut.expect(l_actual).to_equal(l_expected);
--Assert
ut.expect(expectations.failed_expectations_data()).to_be_empty();
end;

end;
/
6 changes: 5 additions & 1 deletion test/core/expectations/compound_data/test_expectations_cursor.pks
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -187,7 +187,11 @@ create or replace package test_expectations_cursor is
--%test(Adds a warning when using depreciated syntax to_( equal( a_expected sys_refcursor, a_exclude ut_varchar2_list )) )
procedure deprec_equal_excl_list;

--%test(Reports column name differences if found)
--%test(Reports column diff on cursor with column name implicit )
procedure column_diff_on_col_name_implicit;

--%test(Reports column match on cursor with column name implicit )
procedure column_match_on_col_name_implicit;

end;
/