XMLELEMENT Behaves Differently In Database Version 19.3 And 19.6 (Doc ID 2643038.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

On : 19.5.0.0.0 version, XML Database

ACTUAL BEHAVIOR
---------------
XMLELEMENT different result after installing 19.6RU

--19.6 output:
SQL> conn testuser/oracle
Connected.
SQL> CREATE OR REPLACE function getValue return varchar2
as
 vCampo2 varchar2 (5);
begin
 select dummy into vCampo2 from dual;
 return vCampo2;
exception
 when no_data_found then
   return nvl(vCampo2,'noval');
end;
/
 2    3    4    5    6    7    8    9   10   11

Function created.

SQL> set long 10000
SQL> SELECT
      XMLELEMENT (
          "xml_elem",
          XMLFOREST (
 2    3    4    5                 XMLFOREST (DUMMY AS "Dummy_field") AS "xml_forest_node",
              getValue AS "Get_function",
              TO_CHAR (SYSTIMESTAMP, 'RRRR-MM-DD"T"HH24:MI:SS.FF3')
                  AS "test_date"))    AS Result
 6    7    8    9    FROM DUAL
10  /

RESULT
--------------------------------------------------------------------------------
<xml_elem><Dummy_field>X</Dummy_field><Get_function>X</Get_function><test_date>2
020-01-28T01:53:10.024</test_date></xml_elem>

--19.3 output:
SELECT
      XMLELEMENT (
          "xml_elem",
          XMLFOREST (
              XMLFOREST (DUMMY AS "Dummy_field") AS "xml_forest_node",
              getValue AS "Get_function",
              TO_CHAR (SYSTIMESTAMP, 'RRRR-MM-DD"T"HH24:MI:SS.FF3')
                  AS "test_date"))    AS Result
 FROM DUAL
Function created.

SQL> SQL> SQL>   2    3    4    5    6    7    8    9
10  /

RESULT
--------------------------------------------------------------------------------
<xml_elem><xml_forest_node><Dummy_field>X</Dummy_field></xml_forest_node><Get_fu
nction>X</Get_function><test_date>2020-01-28T12:52:36.359</test_date></xml_elem>

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.