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.xx
Information 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.
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"
and l_compare_sql
Provide version info
Information about utPLSQL and Database version,
Information about client software
PLSQLDEVELOPER
Expected behavior
It should compare values and escape any special characters.
Example code
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.