Oracle squence problem in XmlElement subquery

Question

having this function:

CREATE OR REPLACE FUNCTION get_seq_xi_pl9_wws2lvs
    RETURN NUMBER 
IS
BEGIN
    RETURN seq_xi_pl9_wws2lvs.NEXTVAL;
END get_seq_xi_pl9_wws2lvs;

and using this sample query:

SELECT id,
          ''
       || XMLELEMENT ("Request",
                      XMLELEMENT ("TransaktionsNr",
                                  id),
                      XMLELEMENT ("Zeitstempel",
                                  0),
                      (SELECT xmldaten
                         FROM DUAL))    AS xml_final
  FROM (SELECT id,
               xmldaten
          FROM (SELECT get_seq_xi_pl9_wws2lvs                           AS id,
                       (XMLELEMENT ("Bestellung",
                                    XMLELEMENT ("BestellNr",
                                                tmp_bsp.BestellNr)))    AS xmlDaten
                  FROM (SELECT 4711     AS BestellNr
                          FROM DUAL
                        UNION ALL
                        SELECT 4712     AS BestellNr
                          FROM DUAL) tmp_bsp)); 

this is the output:

ID	XML_FINAL
347816	<Request><TransaktionsNr>347817</TransaktionsNr><Zeitstempel>0</Zeitstempel><Bestellung><BestellNr>4711</BestellNr></Bestellung></Request>
347818	<Request><TransaktionsNr>347819</TransaktionsNr><Zeitstempel>0</Zeitstempel><Bestellung><BestellNr>4712</BestellNr></Bestellung></Request>

In one row, field "ID" and XmlElement "TransaktionsNr" (which also has "ID" as value) have different values - I would expect them to be the same.

What am I doing wrong?

Answer 1

The SQL engine is opting to not materialize the subquery and is pushing the function calls into the outer query where it gets called multiple times for each row. You need to force the function to be evaluated in the subquery where it is called rather than allowing the SQL engine to rewrite the query.

One method is to use a sub-query factoring clause with a materialize hint:

WITH data (bestellnr) AS (
  SELECT 4711 AS BestellNr FROM DUAL
UNION ALL
  SELECT 4712 AS BestellNr FROM DUAL
),
ids (id, bestellnr) AS (
  SELECT /*+ materialize */ 
         get_seq_xi_pl9_wws2lvs,
         bestellnr
  FROM   data
)
SELECT id,
       XMLELEMENT (
           "Request",
           XMLELEMENT ("TransaktionsNr", id),
           XMLELEMENT ("Zeitstempel", 0),
           XMLELEMENT(
             "Bestellung",
             XMLELEMENT ("BestellNr", BestellNr)
           )
       ).getStringVal() AS xml_final
FROM   ids;

Which outputs:

ID	XML_FINAL
1	<Request><TransaktionsNr>1</TransaktionsNr><Zeitstempel>0</Zeitstempel><Bestellung><BestellNr>4711</BestellNr></Bestellung></Request>
2	<Request><TransaktionsNr>2</TransaktionsNr><Zeitstempel>0</Zeitstempel><Bestellung><BestellNr>4712</BestellNr></Bestellung></Request>

---

If you don't want to use hints then you can add the (seemingly useless) filter WHERE ROWNUM >= 1 which will also force the SQL engine to materialize the query at that point.

SELECT id,
       XMLELEMENT (
           "Request",
           XMLELEMENT ("TransaktionsNr", id),
           XMLELEMENT ("Zeitstempel", 0),
           XMLELEMENT(
             "Bestellung",
             XMLELEMENT ("BestellNr", BestellNr)
           )
       ).getStringVal() AS xml_final
FROM   (
  SELECT get_seq_xi_pl9_wws2lvs AS id,
         bestellnr
  FROM   (
    SELECT 4711 AS BestellNr FROM DUAL UNION ALL
    SELECT 4712 AS BestellNr FROM DUAL
  )
  WHERE  ROWNUM >= 1
);

db<>fiddle here