UNPIVOT Statement Returns Incorrect Results In 12.1.0.2 on table with XMLTYPE (Doc ID 2763997.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

On : 12.1.0.2 version, when loading a data from an XML object where data is merged into a table by unpivotting the data, the result is wrong.

In 12.1.0,2 the fields not explicitly mentioned in the query are loaded as NULLs.

Same procedure works fine in 11.2.0.4.

--11.2.0.4 result:

INSERT INTO xxtt_cp_rebates
(
year,
account_number,
account_name,
spg_code,
effective_start_date,
effective_end_date,
rebate_eur_tn,
last_update_date,
xml_doc_id
)
WITH load_data AS
(
SELECT /*+ MATERIALIZE */ account_number, account_name, spg_code,
month1, month2,month3,month4,month5,month6,
month7,month8,month9,month10,month11,month12
FROM XMLTABLE('/fileDrop/cpRebatesInv/cpRebatesInv/CPRebates/CPRebatesLine'
PASSING lx_xml_doc
COLUMNS
account_number VARCHAR2(30) PATH 'AccountNumber',
account_name VARCHAR2(240) PATH 'AccountName',
spg_code VARCHAR2(40 BYTE) PATH 'SPGCode',
Month1 NUMBER PATH 'Month1',
Month2 NUMBER PATH 'Month2',
Month3 NUMBER PATH 'Month3',
Month4 NUMBER PATH 'Month4',
Month5 NUMBER PATH 'Month5',
Month6 NUMBER PATH 'Month6',
Month7 NUMBER PATH 'Month7',
Month8 NUMBER PATH 'Month8',
Month9 NUMBER PATH 'Month9',
Month10 NUMBER PATH 'Month10',
Month11 NUMBER PATH 'Month11',
Month12 NUMBER PATH 'Month12'
)
),
unpivotted AS
(
SELECT account_number, account_name, spg_code,
month_name, rebate_eur_tn
FROM load_data ld
UNPIVOT INCLUDE NULLS (rebate_eur_tn
FOR month_name IN (Month1 AS 'JAN',
Month2 AS 'FEB',
Month3 AS 'MAR',
Month4 AS 'APR',
Month5 AS 'MAY',
Month6 AS 'JUN',
Month7 AS 'JUL',
Month8 AS 'AUG',
Month9 AS 'SEP',
Month10 AS 'OCT',
Month11 AS 'NOV',
Month12 AS 'DEC'))
)
SELECT lv_year,
account_number,
accou 2 nt_name, 3
spg_code,
4 TO_DATE('01- 5 '||mon 6 t 7 h_name||'-'||lv_year) effective_start_date,
last_day(T 8 O_DATE('01-'||month_name||'- 9 '||lv_year)) effective_end_da 10 te,
rebate_eur_tn,
11 s 12 ysdate,
1545 13 45
FROM unpiv 14 otted;

END;
/
PL/SQL procedure successfully completed.

SQL> col ACCOUNT_NUMBER for a30
col ACCOUNT_NAME for a40
set lin 400
SELECT *
FROM xxtt_cp_rebates
/
SQL> SQL> SQL> 2 3
YEAR ACCOUNT_NUMBER ACCOUNT_NAME SPG_CODE EFFECTIVE EFFECTIVE REBATE_EUR_TN LAST_UPDATE_DATE XML_DOC_ID
---------- ------------------------------ ---------------------------------------- ---------------------------------------- --------- --------- ------------- --------------------------------------------------------------------------- ----------
2021 1001206 DANTRADE BV (IE VAT- POWDERS MACROOM) 2210 01-JAN-21 31-JAN-21 -100 08-MAR-21 02.19.48.000000 PM 154545
2021 1001206 DANTRADE BV (IE VAT- POWDERS MACROOM) 2210 01-FEB-21 28-FEB-21 08-MAR-21 02.19.48.000000 PM 154545
2021 1001206 DANTRADE BV (IE VAT- POWDERS MACROOM) 2210 01-MAR-21 31-MAR-21 08-MAR-21 02.19.48.000000 PM 154545
2021 1001206 DANTRADE BV (IE VAT- POWDERS MACROOM) 2210 01-APR-21 30-APR-21 08-MAR-21 02.19.48.000000 PM 154545
2021 1001206 DANTRADE BV (IE VAT- POWDERS MACROOM) 2210 01-MAY-21 31-MAY-21 08-MAR-21 02.19.48.000000 PM 154545
2021 1001206 DANTRADE BV (IE VAT- POWDERS MACROOM) 2210 01-JUN-21 30-JUN-21 08-MAR-21 02.19.48.000000 PM 154545
2021 1001206 DANTRADE BV (IE VAT- POWDERS MACROOM) 2210 01-JUL-21 31-JUL-21 08-MAR-21 02.19.48.000000 PM 154545
2021 1001206 DANTRADE BV (IE VAT- POWDERS MACROOM) 2210 01-AUG-21 31-AUG-21 08-MAR-21 02.19.48.000000 PM 154545
2021 1001206 DANTRADE BV (IE VAT- POWDERS MACROOM) 2210 01-SEP-21 30-SEP-21 08-MAR-21 02.19.48.000000 PM 154545

--12.1.0.2 result

INSERT INTO xxtt_cp_rebates
(
year,
account_number,
account_name,
spg_code,
effective_start_date,
effective_end_date,
rebate_eur_tn,
last_update_date,
xml_doc_id
)
WITH load_data AS
(
SELECT /*+ MATERIALIZE */ account_number, account_name, spg_code,
month1, month2,month3,month4,month5,month6,
month7,month8,month9,month10,month11,month12
FROM XMLTABLE('/fileDrop/cpRebatesInv/cpRebatesInv/CPRebates/CPRebatesLine'
PASSING lx_xml_doc
COLUMNS
account_number VARCHAR2(30) PATH 'AccountNumber',
account_name VARCHAR2(240) PATH 'AccountName',
spg_code VARCHAR2(40 BYTE) PATH 'SPGCode',
Month1 NUMBER PATH 'Month1',
Month2 NUMBER PATH 'Month2',
Month3 NUMBER PATH 'Month3',
Month4 NUMBER PATH 'Month4',
2 Month5 NUMBER PATH 'Month5',
Month6 NUMBER PATH 'Month6',
Month7 NUMBER PATH 'Month7',
Month8 NUMBER PATH 'Month8',
Month9 NUMBER PATH 'Month9',
Month10 NUMBER PATH 'Month10',
Month11 NUMBER PATH 'Month11',
Month12 NUMBER PATH 'Month12'
)
),
unpivotted AS
(
SELECT account_number, account_name, spg_code,
month_name, rebate_eur_tn
FROM load_data ld
UNPIVOT INCLUDE NULLS (rebate_eur_tn
FOR month_name IN (Month1 AS 'JAN',
Month2 AS 'FEB',
Month3 AS 'MAR',
Month4 AS 'APR',
Month5 AS 'MAY',
Month6 AS 'JUN',
Month7 AS 'JUL',
Month8 AS 'AUG',
Month9 AS 'SEP',
Month10 AS 'OCT',
Month11 AS 'NOV',
Month12 AS 'DEC'))
)
SELECT lv_year,
account_number,
account_name,
spg_code,
3 TO_DATE('01- 4 '||mon 5 t 6 h_name||'-'||lv_year) effective_start_date,
last_day(T 7 O_DATE('01-'||month_name||'- 8 '||lv_year)) effective_end_da 9 te,
rebate_eur_tn,
10 s 11 ysdate,
1545 12 45
FROM unpiv 13 otted;

END;
/
PL/SQL procedure successfully completed.

SQL>
SQL> col ACCOUNT_NUMBER for a30
col ACCOUNT_NAME for a40
set lin 400
SELECT *
FROM xxtt_cp_rebates
/
SQL> SQL> SQL> 2 3
YEAR ACCOUNT_NUMBER ACCOUNT_NAME SPG_CODE EFFECTIVE EFFECTIVE REBATE_EUR_TN LAST_UPDATE_DATE XML_DOC_ID
---------- ------------------------------ ---------------------------------------- ---------------------------------------- --------- --------- ------------- --------------------------------------------------------------------------- ----------
2021 01-JAN-21 31-JAN-21 08-MAR-21 02.16.41.000000 PM 154545
2021 01-FEB-21 28-FEB-21 08-MAR-21 02.16.41.000000 PM 154545
2021 01-MAR-21 31-MAR-21 08-MAR-21 02.16.41.000000 PM 154545
2021 01-APR-21 30-APR-21 08-MAR-21 02.16.41.000000 PM 154545
2021 01-MAY-21 31-MAY-21 08-MAR-21 02.16.41.000000 PM 154545
2021 01-JUN-21 30-JUN-21 08-MAR-21 02.16.41.000000 PM 154545
2021 01-JUL-21 31-JUL-21 08-MAR-21 02.16.41.000000 PM 154545
2021 01-AUG-21 31-AUG-21 08-MAR-21 02.16.41.000000 PM 154545
2021 01-SEP-21 30-SEP-21 08-MAR-21 02.16.41.000000 PM 154545
2021 01-OCT-21 31-OCT-21 08-MAR-21 02.16.41.000000 PM 154545

Cause

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