Issues calling stored procedure from C# with large CLOB

Question

Dear all,

I'm not the first to have these issues, and will list some reference posts below, but am still looking for a proper solution.

I need to call a stored procedure (Oracle 10g database) from a C# web service. The web server has an Oracle 9i client installed and I am using Microsofts System.Data.OracleClient.

The procedure takes an XML as a CLOB. When the XML was over 4000 Bytes (which is likely in a normal use case), I stumbled over the following error:

ORA-01460 - unimplemented or unreasonable conversion requested

I've found this, this and this post.

Further I found a promising workaround which doesn't call the stored procedure directly from C# but defines a piece of anonymous PL/SQL code instead. This code is run as an OracleCommand. The XML is embedded as a string literal and the procedure call is done from within that piece of code:

private const string LoadXml =
    "DECLARE " +
    "  MyXML CLOB; " +
    "  iStatus INTEGER; " +
    "  sErrMessage VARCHAR2(2000); " +
    "BEGIN " +
    "  MyXML := '{0}'; " +
    "  iStatus := LoadXML(MyXML, sErrMessage); " +
    "  DBMS_OUTPUT.ENABLE(buffer_size => NULL); " +
    "  DBMS_OUTPUT.PUT_LINE(iStatus || ',' || sErrMessage); " +
    "END;";
OracleCommand oraCommand = new OracleCommand(
    string.Format(LoadXml, xml), oraConnection);
oraCommand.ExecuteNonQuery();

Unfortunately, this approach now fails as soon as the XML is over 32 KBytes or so, which still is very likely in my application. This time the error stems from the PL/SQL compiler which says:

ORA-06550: line1, column 87: PLS-00172: string literal too long

After some research I conclude that it's simply not feasible to solve the problem with my second approach.

Following the above-mentioned posts I have the following two options.

• Switch to ODP.NET (because it is supposed to be a bug in Microsoft's deprecated DB client)
• Insert the CLOB into a table and make the stored proc read from there

(The first post said some clients are buggy, but mine (9i) does not fall in the mentioned range of 10g/11g versions.)

Can you confirm that these are the only two options left? Or is there another way to help me out?

Just to clarify: the XML won't eventually be saved in any table, but it is processed by the stored procedure which inserts some records in some table based on the XML contents.

My considerations about the two options:

• Switching to ODP.NET is difficult because I have to install it on a web server on which I don't have system access so far, and because we might also want to deploy the piece of code on clients, so each client would have to install ODP.NET as part of the deployment.
• The detour over a table makes the client code quite a bit more complicated and also takes quite some effort on the database adapting/extending the PL/SQL routines.

Answer 1

I guess I just googled this for you to get cheap points, but there's a great explanation here:

http://www.orafaq.com/forum/t/48485/0/

Basically you cannot use more than 4000 chars in a string literal, and if you need to do more, you must use a stored procedure. Then, you are limited to 32KB at max so you have to "chunk" the inserts. Blech.

-Oisin

Answer 2

Wow! There is another way to work around the problem! My fellow employee saved my day pointing me to this blog, which says:

Set the parameter value when BeginTransaction has already been called on the DbConnection.

Could it be simpler? The blog relates to Oracle.DataAccess, but it works just as well for System.Data.OracleClient.

In practice this means:

OracleCommand cmd = new OracleCommand("LoadXML", _oracleConnection);
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter xmlParam = new OracleParameter("XMLFile", OracleType.Clob);
cmd.Parameters.Add(xmlParam);

And do not assign the value in this place yet. Then:

// (... some other parameters to add ...)

cmd.Transaction = _oracleConnection.BeginTransaction();
try
{
    // assign value here, after starting the TX
    xmlParam.Value = xmlData; // xmlData: a string with way more than 4000 chars

    cmd.ExecuteNonQuery();
    cmd.Transaction.Commit();
}
catch (OracleException e)
{
    cmd.Transaction.Rollback();
}