Closed
Description
Describe the bug
When a record value is selected from dual to compare using a refcursor , the oracle give the column a sys generated name. That name seems to be failing XQuery.
This happening because XMLGEN replace a nonvalid colon (':') into x003A where the dbms_desc cursor uses a proper name ":B2"
<ROWSET>
<ROW>
<_x003A_B2>TEST</_x003A_B2>
<_x003A_B1>TEST</_x003A_B1>
</ROW>
</ROWSET>
<ROWSET>
<ROW>
<_x003A_B2>TEST</_x003A_B2>
<_x003A_B1>TEST</_x003A_B1>
</ROW>
</ROWSET>
and l_compare_sql
select
ucd.item_data
,x.data_id data_id
,position + x.item_no item_no
,ucd.":B2" as ":B2" , ucd.":B1" as ":B1"
from UT3.ut_compound_data_tmp x,
xmltable('/ROWSET/ROW' passing x.item_data columns
item_data xmltype path '*'
,position for ordinality
,":B2" VARCHAR2(32) PATH ':B2' , ":B1" VARCHAR2(32) PATH ':B1' ) ucd
where data_id = :act_guid
Provide version info
Information about utPLSQL and Database version,
UT_VERSION
--------------------------------------------------------------------------------
v3.1.6.2735
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
PARAMETER VALUE
------------------------------ ----------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RRRR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected
PORT_STRING
--------------------------------------------------------------------------------
x86_64/Linux 2.4.xxInformation about client software
PLSQLDEVELOPER
Expected behavior
It should compare values and escape any special characters.
Example code
CREATE OR REPLACE PACKAGE bug_test IS
--%suitepath(bugtest)
--%suite(bug_test)
TYPE t_rec IS RECORD(
col_name VARCHAR2(4000),
col_val VARCHAR2(4000));
--%test( Bug test)
PROCEDURE ut_bug_test;
END bug_test;
/
CREATE OR REPLACE PACKAGE BODY bug_test IS
PROCEDURE ut_bug_test IS
l_val1 t_rec;
l_val2 t_rec;
l_act SYS_REFCURSOR;
l_exp SYS_REFCURSOR;
BEGIN
l_val1.col_name := 'TEST';
l_val1.col_val := 'TEST';
l_val2.col_name := 'TEST';
l_val2.col_val := 'TEST';
OPEN l_act FOR
SELECT l_val1.col_name,
l_val1.col_val
FROM dual;
OPEN l_exp FOR
SELECT l_val2.col_name,
l_val2.col_val
FROM dual;
ut.expect(l_act).to_equal(l_exp);
END;
END bug_test;
/
SELECT * FROM TABLE(ut.run('bug_test'));
bugtest
bug_test
Bug test [.02 sec] (FAILED - 1)
Failures:
1) ut_bug_test
ORA-19114: XPST0003 - error during parsing the XQuery expression:
LPX-00801: XQuery syntax error at ':'
1 :B2
- ^
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 318
ORA-06512: at "UT3.UT_DATA_VALUE_REFCURSOR", line 366
ORA-06512: at "UT3.UT_EQUAL", line 225
ORA-06512: at "UT3.UT_EXPECTATION", line 26
ORA-06512: at "UT3.UT_EXPECTATION", line 138
ORA-06512: at "UT_TESTER.BUG_TEST", line 23
ORA-06512: at line 6
Finished in .021688 seconds
1 tests, 0 failed, 1 errored, 0 disabled, 0 warning(s)
We will have to probably convert to unicode column_name to avoid that situation.
Workaround
Putting a column aliases resolve a problem so its not a showstopper.