Problemas al llamar al procedimiento almacenado desde C#con CLOB grande

oracle stored-procedures

Question

No soy el primero en tener estos problemas, y enumeraré algunas publicaciones de referencia a continuación, pero sigo buscando una solución adecuada.

Necesito llamar a un procedimiento almacenado (base de datos Oracle 10g) desde un servicio web de C #. El servidor web tiene un cliente Oracle 9i instalado y estoy usando Microsofts System.Data.OracleClient .

El procedimiento toma un XML como un CLOB. Cuando el XML tenía más de 4000 bytes (lo que es probable en un caso de uso normal), me topé con el siguiente error:

ORA-01460: conversión no implementada o irrazonable solicitada

He encontrado this , this y this post.

Además, encontré una solución prometedora que no llama al procedimiento almacenado directamente desde C #, sino que define un pedazo de código PL / SQL anónimo. Este código se ejecuta como un OracleCommand. El XML está incrustado como un literal de cadena y la llamada al procedimiento se realiza desde ese fragmento de código:

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();

Desafortunadamente, este enfoque ahora falla tan pronto como el XML tiene más de 32 KBytes , lo cual es muy probable en mi aplicación. Esta vez el error proviene del compilador PL / SQL que dice:

ORA-06550: línea 1, columna 87: PLS-00172: cadena literal demasiado larga

Después de algunas investigaciones, concluyo que simplemente no es posible resolver el problema con mi segundo enfoque.

Siguiendo las publicaciones mencionadas anteriormente, tengo las siguientes dos opciones.

• this (porque se supone que es un error en el cliente de base de datos obsoleto de Microsoft)
• this y haga que el proceso almacenado se lea desde allí.

( this dice que algunos clientes tienen errores, pero el mío (9i) no está en el rango mencionado de las versiones 10g / 11g).

¿Puedes confirmar que estas son las únicas dos opciones que quedan? ¿O hay otra manera de ayudarme?

Solo para aclarar: el XML finalmente no se guardará en ninguna tabla, pero se procesa mediante el procedimiento almacenado que inserta algunos registros en alguna tabla según los contenidos XML.

Mis consideraciones sobre las dos opciones:

• Cambiar a ODP.NET es difícil porque tengo que instalarlo en un servidor web en el que no tengo acceso al sistema hasta ahora, y porque es posible que también queramos implementar el código en los clientes, por lo que cada cliente tendría que instale ODP.NET como parte de la implementación.
• El desvío sobre una tabla hace que el código del cliente sea un poco más complicado y también requiere bastante esfuerzo en la adaptación / ampliación de la base de datos de las rutinas PL / SQL.

Answer 1

¡Encontré que hay otra manera de solucionar el problema! Mi compañero de trabajo me salvó el día y me señaló este blog , que dice:

Establezca el valor del parámetro cuando ya se haya llamado a BeginTransaction en DbConnection.

¿Podría ser más simple? El blog se relaciona con Oracle.DataAccess , pero funciona igual de bien para System.Data.OracleClient .

En la práctica esto significa:

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

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

// DO NOT assign the parameter value yet in this place

cmd.Transaction = _oracleConnection.BeginTransaction();
try
{
    // Assign value here, AFTER starting the TX
    xmlParam.Value = xmlWithWayMoreThan4000Characters;

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

Answer 2

Creo que solo busqué en Google para que obtuvieras puntos baratos, pero hay una gran explicación aquí:

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

Básicamente, no puede usar más de 4000 caracteres en un literal de cadena, y si necesita hacer más, debe usar un procedimiento almacenado. Entonces, está limitado a 32KB como máximo, por lo que tiene que "dividir" las inserciones. Blech

-Oisin

Answer 3

En mi caso, la solución de chiccodoro no funcionó. Estoy usando ODP.NET ( Oracle.DataAccess ).

Para mí la solución está utilizando el objeto OracleClob .

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

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

//connection should be open!
OracleClob clob = new OracleClob(_oracleConnection);
// xmlData: a string with way more than 4000 chars
clob.Write(xmlData.ToArray(),0,xmlData.Length);
xmlParam.Value = clob; 

try
{
    cmd.ExecuteNonQuery();
}
catch (OracleException e)
{
}

Answer 4

chiccodoro tiene razón.

public static int RunProcedure(string storedProcName, IDataParameter[] parameters)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            int rowsAffected;

            OracleCommand command = new OracleCommand(storedProcName, connection);
            command.CommandText = storedProcName;
            command.CommandType = CommandType.StoredProcedure;
            foreach (OracleParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            connection.Open();

            try
            {
                // start transaction
                command.Transaction = connection.BeginTransaction();
                rowsAffected = command.ExecuteNonQuery();
                command.Transaction.Commit();
            }
            catch (System.Exception ex)
            {
                command.Transaction.Rollback();
                throw ex;
            }

            connection.Close();
            return rowsAffected;
        }
    }