GeezerVison

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.

Activity

lwasylow
self-assigned this
on Apr 12, 2019
lwasylow
added
bug
on Apr 12, 2019
lwasylow commented on Apr 12, 2019
lwasylow
on Apr 12, 2019
MemberAuthor
More actions
That particular should be simple to fix but will check any other special char scenarios, but most likely a column name will have to also be escaped into xml to match that one in xmlgen.
lwasylow commented on Apr 12, 2019
lwasylow
on Apr 12, 2019
MemberAuthor
More actions
It looks like dbms_xmlgen adhere mostly to rules for xml names https://www.w3schools.com/xml/xml_elements.asp

Element names are case-sensitive

Element names must start with a letter or underscore

Element names can contain letters, digits, hyphens, underscores, and periods

Element names cannot contain spaces

except

Element names cannot start with the letters xml (or XML, or Xml, etc)

I will add a special function to reconstruct a valid xml name
jgebal commented on Apr 13, 2019
jgebal
on Apr 13, 2019
Member
More actions
How is the fix for this issue working?
What do we replace the :B2 with?
I'm wondering if we should do the replace at all as it is change to the column name.
Column name is part of equality comparison.

I'd like to hear pros/cons for doing/not doing it.
lwasylow commented on Apr 13, 2019
lwasylow
on Apr 13, 2019
MemberAuthor
More actions
We don't replace. If you look into xml generated it replace a : with _x003_ as colon is not valid xml char for start of name as per oracle docs https://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#i1026349 What I'm implemented is same transformation when we query xml but a original column name from cursor describe is kept in cursor details and used later to display info.
I'm checking each character to validate that is a valid xml name char and transform if not as per oracle xmlgen. Then that transformed name that is matching name of column in xml document is used in query (similar what we do with to long name and hash) but if you compare a xml content and sql they will use same column name.
If that's not done a dynamic query generated tries to use a : in xmltable path and fails with error. Since its a per char there is no danger of false positive.
lwasylow
closed this as completedin #903 on Apr 14, 2019
lwasylow
added this to the v3.1.7 milestone on Apr 27, 2019

Metadata

Assignees

lwasylow

Labels

bug

Type

No type

Projects

No projects

Milestone

v3.1.7

Relationships

None yet

Development

No branches or pull requests

Issue actions

You are visitor 01402503 — GeezerVison does not store the pages you look up.
Best viewed with Netscape Navigator 4.7 or Internet Explorer 6 at 800×600. No cookies required.

GeezerVison

Refcursor do not handle well sys names out of records. #902

Description

Activity

lwasylow commented on Apr 12, 2019

lwasylow commented on Apr 12, 2019

jgebal commented on Apr 13, 2019

lwasylow commented on Apr 13, 2019

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions