i have a stored procedure in my database that returns a clob with xml data representation.
My problem is when i test the procedure from pl/sql developer iam getting the results in order.
On the other hand when i call the procedure with Java CallableStatement it returns me the results of the xml in incorrect order.
This happens when i have more than 12 elements in my xml. When i have less than 12 elements the results are correct and in order both in the output of the procedure and in output of the call in Java.
I need to get the same results in order both in the testing of the procedure in the database and in Java Call.
My result from the procedure
<Picking>1164
<Items Count="26">
<Item Index="1">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="052058100000010576">09.001.000.B10</Code>
<LocatedAt>K01.007.00A.004</LocatedAt>
<Requested>3456</Requested>
<Allocated>184</Allocated>
<AllocatedLU LU="ΚΙΛ">184</AllocatedLU>
<ContainedLU LU="ΚΙΛ">184</ContainedLU>
</Item>
<Item Index="2">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="052058100000010620">09.001.000.B10</Code>
<LocatedAt>K01.007.00A.004</LocatedAt>
<Requested>3456</Requested>
<Allocated>193</Allocated>
<AllocatedLU LU="ΚΙΛ">193</AllocatedLU>
<ContainedLU LU="ΚΙΛ">193</ContainedLU>
</Item>
<Item Index="3">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="052058100000010590">09.001.000.B10</Code>
<LocatedAt>K01.007.00B.004</LocatedAt>
<Requested>3456</Requested>
<Allocated>193</Allocated>
<AllocatedLU LU="ΚΙΛ">193</AllocatedLU>
<ContainedLU LU="ΚΙΛ">193</ContainedLU>
</Item>
<Item Index="4">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="052058100000010606">09.001.000.B10</Code>
<LocatedAt>K01.007.00B.004</LocatedAt>
<Requested>3456</Requested>
<Allocated>177</Allocated>
<AllocatedLU LU="ΚΙΛ">177</AllocatedLU>
<ContainedLU LU="ΚΙΛ">177</ContainedLU>
</Item>
<Item Index="5">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="052058100000010545">09.001.000.B10</Code>
<LocatedAt>K01.007.00B.005</LocatedAt>
<Requested>3456</Requested>
<Allocated>185</Allocated>
<AllocatedLU LU="ΚΙΛ">185</AllocatedLU>
<ContainedLU LU="ΚΙΛ">185</ContainedLU>
</Item>
<Item Index="6">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="052058100000010569">09.001.000.B10</Code>
<LocatedAt>K01.007.00B.005</LocatedAt>
<Requested>3456</Requested>
<Allocated>170</Allocated>
<AllocatedLU LU="ΚΙΛ">170</AllocatedLU>
<ContainedLU LU="ΚΙΛ">170</ContainedLU>
</Item>
<Item Index="7">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="052058100000010538">09.001.000.B10</Code>
<LocatedAt>K01.007.00A.006</LocatedAt>
<Requested>3456</Requested>
<Allocated>170</Allocated>
<AllocatedLU LU="ΚΙΛ">170</AllocatedLU>
<ContainedLU LU="ΚΙΛ">170</ContainedLU>
</Item>
<Item Index="8">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="052058100000010521">09.001.000.B10</Code>
<LocatedAt>K01.007.00A.006</LocatedAt>
<Requested>3456</Requested>
<Allocated>175</Allocated>
<AllocatedLU LU="ΚΙΛ">175</AllocatedLU>
<ContainedLU LU="ΚΙΛ">175</ContainedLU>
</Item>
<Item Index="9">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="052058100000010491">09.001.000.B10</Code>
<LocatedAt>K01.007.00B.006</LocatedAt>
<Requested>3456</Requested>
<Allocated>200</Allocated>
<AllocatedLU LU="ΚΙΛ">200</AllocatedLU>
<ContainedLU LU="ΚΙΛ">200</ContainedLU>
</Item>
<Item Index="10">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="052058100000010484">09.001.000.B10</Code>
<LocatedAt>K01.007.00A.007</LocatedAt>
<Requested>3456</Requested>
<Allocated>180</Allocated>
<AllocatedLU LU="ΚΙΛ">180</AllocatedLU>
<ContainedLU LU="ΚΙΛ">180</ContainedLU>
</Item>
<Item Index="11">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="052058100000010460">09.001.000.B10</Code>
<LocatedAt>K01.007.00B.007</LocatedAt>
<Requested>3456</Requested>
<Allocated>196</Allocated>
<AllocatedLU LU="ΚΙΛ">196</AllocatedLU>
<ContainedLU LU="ΚΙΛ">196</ContainedLU>
</Item>
<Item Index="12">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="052058100000010453">09.001.000.B10</Code>
<LocatedAt>K01.007.00B.007</LocatedAt>
<Requested>3456</Requested>
<Allocated>193</Allocated>
<AllocatedLU LU="ΚΙΛ">193</AllocatedLU>
<ContainedLU LU="ΚΙΛ">193</ContainedLU>
</Item>
<Item Index="13">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="052058100000010330">09.001.000.B10</Code>
<LocatedAt>K01.007.00C.001</LocatedAt>
<Requested>3456</Requested>
<Allocated>141</Allocated>
<AllocatedLU LU="ΚΙΛ">141</AllocatedLU>
<ContainedLU LU="ΚΙΛ">170</ContainedLU>
</Item>
<Item Index="14">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="552058100006300546">09.001.000.B10</Code>
<LocatedAt>K21.004.00D.002</LocatedAt>
<Requested>3456</Requested>
<Allocated>249</Allocated>
<AllocatedLU LU="ΚΙΛ">249</AllocatedLU>
<ContainedLU LU="ΚΙΛ">249</ContainedLU>
</Item>
<Item Index="15">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="552058100006300614">09.001.000.B10</Code>
<LocatedAt>K21.008.00A.005</LocatedAt>
<Requested>3456</Requested>
<Allocated>320</Allocated>
<AllocatedLU LU="ΚΙΛ">320</AllocatedLU>
<ContainedLU LU="ΚΙΛ">320</ContainedLU>
</Item>
<Item Index="16">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="152058100005319435">09.001.000.B10</Code>
<LocatedAt>K22.001.00D.002</LocatedAt>
<Requested>3456</Requested>
<Allocated>310</Allocated>
<AllocatedLU LU="ΚΙΛ">310</AllocatedLU>
<ContainedLU LU="ΚΙΛ">310</ContainedLU>
</Item>
<Item Index="17">
<Code Description="ΣΠΑΝΑΚΙ ΦΥΛΛΟ ΙQF ΕΛΛΗΝΙΚΟ ΗΜΙΕ ΒΙΝS"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="152058100005319428">09.001.000.B10</Code>
<LocatedAt>K22.016.00A.001</LocatedAt>
<Requested>3456</Requested>
<Allocated>220</Allocated>
<AllocatedLU LU="ΚΙΛ">220</AllocatedLU>
<ContainedLU LU="ΚΙΛ">220</ContainedLU>
</Item>
<Item Index="18">
<Code Description="SΤRΕΤCΗ FΙLΜ SFM 17micro NANAO ΒΙΟΜΗΧΑΝΙΚΟ"
BMU="ΚΙΛ"
LU="ΚΙΛ"
SSCC="152058100007572807">81.007.000.002</Code>
<LocatedAt>AYL.001.001.001</LocatedAt>
<Requested>1</Requested>
<Allocated>1</Allocated>
<AllocatedLU LU="ΚΙΛ">1</AllocatedLU>
<ContainedLU LU="ΚΙΛ">321</ContainedLU>
</Item>
<Item Index="19">
<Code Description="ΣΑΚΟΥΛΕΣ ΑΤΥΠΩΤΕΣ ΣΙΕΛ ΣΚΟΥΠΙΔΙΑ LD 90-60*65/6 (για τελαρα)"
BMU="ΤΕΜ"
SMU="ΚΙΛ"
LU="ΤΕΜ"
SSCC="252058100007016056">81.111.000.003</Code>
<LocatedAt>AYL.001.001.001</LocatedAt>
<Requested SMU=".471">8</Requested>
<Allocated SMU=".471">8</Allocated>
<AllocatedLU LU="ΤΕΜ">8</AllocatedLU>
<ContainedLU LU="ΤΕΜ">14280</ContainedLU>
</Item>
<Item Index="20">
<Code Description="ΤΑΙΝΙΕΣ ΜΠΛΕ-ΑCRΥLΙC 48Χ600Μ"
BMU="ΤΕΜ"
LU="ΤΕΜ"
SSCC="252058100006421219">81.112.000.004</Code>
<LocatedAt>AYL.001.001.001</LocatedAt>
<Requested>1</Requested>
<Allocated>1</Allocated>
<AllocatedLU LU="ΤΕΜ">1</AllocatedLU>
<ContainedLU LU="ΤΕΜ">840</ContainedLU>
</Item>
<Item Index="21">
<Code Description="ΕΤΙΚΕΤΑ ΛΕΥΚΗ ΘΕΡΜΙΚΗ 105Χ75 ΜΡ ΜΕΓΑΛΟ ΜΑΔΡΕΝ RETAIL BAGS-ΣΠΑΝΑΚΙ ΝΩΠΟ"
BMU="ΤΕΜ"
LU="ΤΕΜ"
SSCC="252058100007615747">81.114.000.002</Code>
<LocatedAt>AYL.001.001.001</LocatedAt>
<Requested>432</Requested>
<Allocated>432</Allocated>
<AllocatedLU LU="ΤΕΜ">432</AllocatedLU>
<ContainedLU LU="ΤΕΜ">120000</ContainedLU>
</Item>
<Item Index="22">
<Code Description="ΕΤΙΚΕΤΑ ΛΕΥΚΗ ΘΕΡΜΙΚΗ 105Χ75 ΜΡ ΜΕΓΑΛΟ ΜΑΔΡΕΝ RETAIL BAGS-ΣΠΑΝΑΚΙ ΝΩΠΟ"
BMU="ΤΕΜ"
LU="ΤΕΜ"
SSCC="252058100007615747">81.114.000.002</Code>
<LocatedAt>AYL.001.001.001</LocatedAt>
<Requested>432</Requested>
<Allocated>432</Allocated>
<AllocatedLU LU="ΤΕΜ">432</AllocatedLU>
<ContainedLU LU="ΤΕΜ">24000</ContainedLU>
</Item>
<Item Index="23">
<Code Description="ΕΤΙΚΕΤΑ ΛΕΥΚΗ ΘΕΡΜΙΚΗ 105Χ75 ΜΡ ΜΕΓΑΛΟ ΜΑΔΡΕΝ RETAIL BAGS-ΣΠΑΝΑΚΙ ΝΩΠΟ"
BMU="ΤΕΜ"
LU="ΤΕΜ"
SSCC="252058100007615747">81.114.000.002</Code>
<LocatedAt>AYL.001.001.001</LocatedAt>
<Requested>432</Requested>
<Allocated>432</Allocated>
<AllocatedLU LU="ΤΕΜ">432</AllocatedLU>
<ContainedLU LU="ΤΕΜ">78000</ContainedLU>
</Item>
<Item Index="24">
<Code Description="ΕΤΙΚΕΤΑ ΛΕΥΚΗ ΘΕΡΜΙΚΗ 150Χ210 BAR CODE"
BMU="ΤΕΜ"
LU="ΤΕΜ"
SSCC="252058100007415521">81.114.000.003</Code>
<LocatedAt>AYL.001.001.001</LocatedAt>
<Requested>8</Requested>
<Allocated>8</Allocated>
<AllocatedLU LU="ΤΕΜ">8</AllocatedLU>
<ContainedLU LU="ΤΕΜ">50302</ContainedLU>
</Item>
<Item Index="25">
<Code Description="Χ/Κ ΦΥΛΛΑ 0,80*1,20 EURO ΠΑΛΕΤΑΣ"
BMU="ΤΕΜ"
LU="ΤΕΜ"
SSCC="252058100007109482">83.110.000.005</Code>
<LocatedAt>DIA.001.001.001</LocatedAt>
<Requested>8</Requested>
<Allocated>8</Allocated>
<AllocatedLU LU="ΤΕΜ">8</AllocatedLU>
<ContainedLU LU="ΤΕΜ">450</ContainedLU>
</Item>
<Item Index="26">
<Code Description="Χ/Κ 383*248*273 441 GR 15ΚG NORMAL ΓΕΝΙΚΟ ΛΑΧΑΝΙΚΩΝ"
BMU="ΤΕΜ"
LU="ΤΕΜ"
SSCC="252058100007367103">83.110.000.007</Code>
<LocatedAt>DIA.001.001.001</LocatedAt>
<Requested>432</Requested>
<Allocated>432</Allocated>
<AllocatedLU LU="ΤΕΜ">432</AllocatedLU>
<ContainedLU LU="ΤΕΜ">880</ContainedLU>
</Item>
</Items>
</Picking>
And my Java Code
public ProductSuggestionMasterBean getProductContents64(int
usage, String username, long pickingListId,
String panel) throws DataBaseException {
try (Connection conn = controlEjb.getConnection(em);
CallableStatement cstmt = conn.prepareCall("{? = call MOM_WAREHOUSE.TERMINAL_PCG.Module064Get(?,?)}")) {
controlEjb.InitERP(usage, username);
cstmt.registerOutParameter(1, Types.CLOB);
cstmt.setLong(2, pickingListId);
cstmt.setString(3, panel);
cstmt.execute();
final Clob clob = cstmt.getClob(1);
if (clob == null || clob.length() == 0L) {
throw new DataBaseException("ΣΦΑΛΜΑ: Δεν βρέθηκαν πληροφορίες για το picking list" + pickingListId);
}
try (BufferedReader br = new BufferedReader(clob.getCharacterStream())) {
final DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
final Document document = builder.parse(new InputSource(br));
document.getDocumentElement().normalize();
XPath xpath = XPathFactory.newInstance().newXPath();
final ProductSuggestionMasterBean result = new ProductSuggestionMasterBean();
// Set Product sugggestions.
final Long itemCount = ((Number) xpath.compile("//Items/@Count").evaluate(document,
XPathConstants.NUMBER)).longValue();
final List<ProductSuggestionBean> details = new ArrayList<>(itemCount.intValue());
final int order = ((Number) xpath.compile("Response//Item/@Index").evaluate(document,
XPathConstants.NUMBER)).intValue();
for (long i = order; i <= itemCount; i++) {
final ProductSuggestionBean suggestion = new ProductSuggestionBean();
try {
suggestion.setId(((Number) xpath.compile("Response/Picking/text()").evaluate(document,
XPathConstants.NUMBER)).longValue());
suggestion.setItemDescription((String) xpath
.compile("Response/Picking/Items/Item[" + i + "][@Index=" + i + "]/Code/@Description")
.evaluate(document, XPathConstants.STRING));
suggestion.setItem((String) xpath
.compile("Response/Picking/Items/Item[" + i + "][@Index=" + i + "]/Code/text()")
.evaluate(document, XPathConstants.STRING));
suggestion.setPanelDescription((String) xpath
.compile("Response/Picking/Items/Item[" + i + "][@Index=" + i + "]/LocatedAt/text()")
.evaluate(document, XPathConstants.STRING));
try {
BigDecimal pUnitValue = new BigDecimal(
TextUtils.reverseDecimalSeparator((String) xpath
.compile("Response/Picking/Items/Item[" + i + "][@Index=" + i
+ "]/ContainedLU/text()")
.evaluate(document, XPathConstants.STRING)));
String pUnitDsc = ((String) xpath
.compile("Response/Picking/Items/Item[" + i + "][@Index=" + i + "]/Code/@LU")
.evaluate(document, XPathConstants.STRING));
BigDecimal sUnitValue = new BigDecimal(
TextUtils.reverseDecimalSeparator((String) xpath
.compile("Response/Picking/Items/Item[" + i + "][@Index=" + i
+ "]/AllocatedLU/text()")
.evaluate(document, XPathConstants.STRING)));
suggestion.setPrimaryMeasurementUnit(new MeasurementUnitBean(pUnitValue, pUnitDsc));
String sUnitDsc = ((String) xpath
.compile("Response/Picking/Items/Item[" + i + "][@Index=" + i + "]/Code/@LU")
.evaluate(document, XPathConstants.STRING));
suggestion.setSecondaryMeasurementUnit(new MeasurementUnitBean(sUnitValue, sUnitDsc));
} catch (NumberFormatException e) {
}
try {
suggestion.setSscc((String) xpath
.compile("Response/Picking/Items/Item[" + i + "][@Index=" + i + "]/Code/@SSCC")
.evaluate(document, XPathConstants.STRING));
} catch (NullPointerException e) {
}
details.add(suggestion);
} catch (NumberFormatException | NullPointerException e) {
e.printStackTrace();
}
}
result.setSuggestions(details);
clob.free();
return result;
}
} catch (SQLException | ParserConfigurationException | IndexOutOfBoundsException | IOException
| NullPointerException | NumberFormatException | SAXException | XPathExpressionException e) {
controlEjb.printErrorTrace(e, null);
throw new DataBaseException(e);
}
}
Update: Replacing ORDER BY ID in the sql query that sorts the elements with ORDER BY NLSSORT(ID, 'NLS_SORT = UNICODE_BINARY') fixes the issue.