When querying a table with a column stored in XML format, we get this error for large XML values. This works in Oracle 10g but this error happens when the same data is queried in an 11.2.0.3 instance. I have had a support request open with Oracle for about a month and so far it has not made any progress. What we've seen are the following:
1. The xml data itself is good.
2. The good xml data when queried with a wholly 10g client/server environment, works.
3. The same good xml data when inserted into a table in an 11g instance through a stored package run from the 11g instance (server to server) gets the 27163 error.
4. The same xml data when queried in a 10g instance from an 11g client fails with the 27163 error.
5. If we disable the 11g XML parser by setting an internal event (alter session set events='31156 trace name context forever, level 0x400';), then run the same code that was run in item 3 above, it does not get the 27163 error. However, the xml loaded to the 11g instance can then only be queried without getting the 27163 error if we use an older 10g client. An 11g client consistently gets the error.
The server versions are 11.2.0.3 (upgraded from 10.2.0.4). The SQL*Net client versions that we have tested on are 11.2.0.2 and 11.2.0.3 (both produce the 27163 error).
With considerable trial and error, we found that it is some combination of the file size and format that causes the error. Copied below is the smallest sample data of a failing XML that will produce this erro (about 8K). Remove any single character even in an a comment and the file parses successfully – but it’s not the file size alone as the original 800+KB file where we first dicovered this problem will process if the period in the attribute: @value=”${item.id}” is removed.
We are at our wit's end, and with an 11g migration project looming, any ideas anyone can suggest would be very helpful.
Thanks,
Joe
Here is a test case to play around with. Sorry I don't have a way to upload a zip file for this, but you can cut & paste from the post:
1. CREATE TABLE my_xml_test
(record_id NUMBER(4,0),
xml SYS.XMLTYPE,
comments VARCHAR2(200))
/
ALTER TABLE my_xml_test
ADD CONSTRAINT my_xml_test_pk PRIMARY KEY (record_id)
USING INDEX
/
2. mkdir SampleData
3. cd into SampleData, copy the XML I will post in the first reply to this message into a new text document called xml_items_removed.xml.
4. cd .. and create a file called 20120112_11g_bad_xml_issue.txt with this in it:
1, .\SampleData\xml_items_removed.xml,"Fails: OriginalXML w/ all instances of <Item> removed (count 600) -- reduces file size to ca. 55KB but still fails (Saved by XMLSpy)"
5. Next create your SQL*Loader control file (call it my_xml_test.ctl):
LOAD DATA
APPEND INTO TABLE my_xml_test
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
record_id,
ext_fname FILLER CHAR(200),
xml LOBFILE(ext_fname) TERMINATED BY EOF,
comments
)
6. sqlldr <username>/<password>@<database> control=my_xml_test.ctl data=20120112_11g_bad_xml_issue.txt log=20120112_11g_bad_xml_issue.log bad=20120112_11g_bad_xml_issue.bad
7. Once the data is loaded, query my_xml_test table that you created in step 1. You should get the 27163 error:
SELECT a.record_id,
comments,
a.XML,
length(a.XML.GetClobVal()) clob_length
FROM my_xml_test a