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?
CodePudding user response:
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