Проблемы, вызывающие хранимую процедуру с С# с большим CLOB

Я не первый, у кого есть эти проблемы, и перечислю некоторые справочные сообщения ниже, но я все еще ищу подходящее решение.

Мне нужно вызвать хранимую процедуру (базу данных Oracle 10g) из веб-службы С#. На веб-сервере установлен клиент Oracle 9i, и я использую Microsoft System.Data.OracleClient.

Процедура принимает XML как CLOB. Когда XML был более 4000 байт (что, вероятно, в обычном случае использования), я наткнулся на следующую ошибку:

ORA-01460 - запрошено неосуществленное или необоснованное преобразование

Я нашел , this и Это сообщение.

Далее я нашел многообещающее обходное решение, которое не вызывает хранимую процедуру непосредственно из С#, но вместо этого определяет часть анонимного кода PL/SQL. Этот код запускается как OracleCommand. XML встроен как строковый литерал, а вызов процедуры выполняется изнутри этого фрагмента кода:

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

К сожалению, этот подход теперь терпит неудачу, как только XML превышает 32 Кбайт или так, что все еще очень вероятно в моем приложении. На этот раз ошибка связана с компилятором PL/SQL, который гласит:

ORA-06550: строка1, столбец 87: PLS-00172: строковый литерал слишком длинный

После некоторых исследований я пришел к выводу, что просто решить проблему с моим вторым подходом просто невозможно.

После вышеупомянутых сообщений у меня есть следующие два варианта.

Переключиться на ODP.NET (поскольку предполагается, что это будет ошибка в Microsoft устарел клиента БД)
Вставьте CLOB в таблицу и сделайте сохраненную прочтку прочитанной есть

(Первое сообщение говорит, что некоторые клиенты ошибочны, но мои (9i ) не попадает в указанный диапазон версий 10g/11g.)

Можете ли вы подтвердить, что это только два варианта? Или есть другой способ помочь мне?

Просто пояснить: XML в конечном итоге не будет сохранен в какой-либо таблице, но обрабатывается хранимой процедурой, которая вставляет некоторые записи в какую-либо таблицу на основе содержимого XML.

Мои соображения обо всех двух вариантах:

Переключение на ODP.NET затруднено, потому что я должен установить его на веб-сервере, на котором у меня пока нет доступа к системе, и потому, что нам также может понадобиться развернуть кусок кода на клиентах, поэтому каждый клиент должен был установить ODP.NET как часть развертывания.
Объезд по таблице делает код клиента довольно сложным, а также прикладывает немало усилий для базы данных, которая адаптирует/расширяет подпрограммы PL/SQL.

Ответ 1

Я обнаружил, что есть другой способ обойти эту проблему! Мой сотрудник продолжал мой день, указывая мне на этот блог, в котором говорится:

Задайте значение параметра, когда BeginTransaction уже вызвал DbConnection.

Может ли быть проще? Блог относится к Oracle.DataAccess, но он работает так же хорошо для System.Data.OracleClient.

На практике это означает:

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

Ответ 2

В моем случае решение chiccodoro не работает. Я использую ODP.NET(Oracle.DataAccess).

Для меня решение использует объект 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)
{
}

Ответ 3

chiccodoro прав.

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;
        }
    }

Ответ 4

Думаю, я просто искал это для вас, чтобы получить дешевые очки, но здесь есть отличное объяснение:

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

В основном вы не можете использовать более 4000 символов в строковом литерале, и если вам нужно сделать больше, вы должны использовать хранимую процедуру. Затем вы ограничены 32 КБ на максимальном уровне, поэтому вам нужно "обрезать" вставки. Blech.

-Oisin