I am working on a bulk loading program that needs to load between 10K and 30K rows into multiple tables. The approach taken is to send in a large XML file to a stored procedure. This stored procedure does an INSERT/SELECT from XMLTABLE to load the table.
When the stored procedure is called from SQL Developer with the following XML, it runs fine:
<NMFC_CTL_STG>
<row><cust>CSA</cust><itemNbr>001010</itemNbr><sub>00</sub><seqNbr>0</seqNbr><status></status><effectiveDate>1995-08-26</effectiveDate><expirationDate></expirationDate></row>
</NMFC_CTL_STG>
When the C# client generates the above XML, saves it into a Unicode string that is placed into a Dapper OracleClob as a byte[], and then call the stored procedure, the following error happens:
C# code:
using var connection = InvocationHelper.GetConnection();
var parameters = new OracleDynamicParameters();
var clobData = new OracleClob(connection as OracleConnection);
clobData.Write(bytes, 0, bytes.Length);
parameters.Add("pi_nmfc_data", bytes, OracleDbType.Clob, ParameterDirection.Input);
connection.Execute(procedureName, parameters, commandType: CommandType.StoredProcedure);
Error
Error: ORA-31011: XML parsing failed
ORA-19213: error occurred in XML processing at lines 1
LPX-00210: expected '<' instead of 'S'
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at "RATES_PBLC.NMFC_IMPORTS", line 244
ORA-06512: at line 1
Line 244 of RATES_PBLC.NMFC_IMPORTS is
l_nmfc_xml_blob := xmltype(pi_nmfc_data);
I simply cannot figure out the S it is finding. I did look at the binary array and saw that the first two bytes are 255,254, which is the Byte Order Mark (BOM). I tried stripping that off and still got the same error. I tried changing the encoding to UTF-8 and the stored procedure didn't like that one bit. I did see something about the issue being the line breaks, so I removed those and still get the same error.
Any suggestions on what is going here?
You are creating an OracleClob object, writing some data into it from a byte array bytes, and then using bytes as the value of the parameter in your call to parameters.Add(...).
Did you perhaps mean to use clobData instead of bytes as the value of the parameter?
The error message you are getting is because the string System.Byte[] (i.e. what you get when you call .ToString() on a byte array) is being sent to the stored procedure. I verified this by writing a stored procedure that called raise_application_error with the procedure parameter in the message, and this is what was in the error message. Also, I can reproduce your error running select xmltype('System.Byte[]') from dual in SQL*Plus.
Incidentally, you don't show us how you created bytes in your app, but on my system I needed to create it using Encoding.GetEncoding("UTF-16LE").GetBytes(...);. That may be an artefact of how my Oracle database (18c XE) is set up, though.