由于对象的当前状态，VB.net Oracle 操作无效

Question

好吧，我真的需要找到一种方法来通过字符串来完成此操作，而不是使用更新查询来完成所有操作。

Dim theXMLCode As OracleClob
Dim OracleConnection2 As New OracleConnection()
Dim dr2 As OracleDataReader
Dim holdXML As String = ""

Public Function connectToOracleDB2() As Boolean
    OracleConnection2.ConnectionString = "User Id=" & dbUserId & ";Password=" & dbPassword & ";Data Source=(DESCRIPTION=(ADDRESS_LIST=" & _
                                      "(ADDRESS=(PROTOCOL=TCP)(HOST=" & dbHost & ")(PORT=" & dbPort & ")))" & _
                                     "(CONNECT_DATA=(SERVICE_NAME=" & dbServiceName & ")))"

    Try
        OracleConnection2.Open()
        Return True
    Catch ee As Exception
        OracleConnection2.Close()
        Return False
    End Try
End Function

    Dim strSQL = "UPDATE   CSR.TARGET ces " & _
                 "SET      (STATUS_CODE, COMPLETE_DATE, DATA) = " & _
                    "(SELECT    'ERROR', '', (:XML_DATA) " & _
                    "FROM       CSR.SOURCE C " & _
                    "WHERE      (c.EID = ces.EID) " & _
                    "AND        c.STATUS_CODE = 'ERROR') " & _
                 "WHERE    EXISTS (SELECT 1 " & _
                 "FROM     CSR.SOURCE C " & _
                 "WHERE    (c.EID = ces.EID) " & _
                 "AND      c.STATUS_CODE = 'ERROR')"

        Try
            Dim parmData As New OracleParameter

            With parmData
                .Direction = ParameterDirection.Input
                .OracleDbType = OracleDbType.Clob
                .ParameterName = "XML_DATA"
                .Value = holdXML
            End With

            OracleCommand2.Parameters.Add(parmData)
            OracleCommand2.CommandText = strSQL
            OracleCommand2.ExecuteNonQuery()

但我收到错误：

错误：由于对象的当前状态，操作无效。

在线：

OracleCommand2.ExecuteNonQuery()

任何帮助都会让这个东西正常工作:o)

David

Answer 1

由于您找不到创建临时 lob 指针的示例，因此这里是使用以前项目中的 Microsoft Enterprise Library 的 C# 示例。此示例与存储过程交互，但使用 SQL 更新和 BLOB/CLOB 时的方法相同：

internal static void Save(Attachments attachment)
    {
        try
        {
            // Microsoft Enterprise Library does not provide support for Oracle BLOB objects
            // The Microsoft Data Provider for Oracle needs to allocate a BLOB pointer in memory first
            // while running in the context of a database transaction. Once the placeholder is allocated,
            // the byte stream is written to the handler and then passed to Oracle to update the database
            //
            OracleConnection connection = new OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connstring_devl"].ConnectionString);
            connection.Open();
            OracleTransaction transaction = connection.BeginTransaction();
            OracleCommand command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;";
            command.Parameters.Add(new OracleParameter("tempblob", OracleType.Blob)).Direction = ParameterDirection.Output;
            command.ExecuteNonQuery();

            OracleLob tempLob = (OracleLob)command.Parameters[0].Value;
            tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
            if (attachment.FileContent != null)
                tempLob.Write(attachment.FileContent, 0, attachment.FileContent.Length);
            tempLob.EndBatch();

            command.Parameters.Clear();
            command.CommandText = MC_SAVE_ATTACHMENT;
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(new OracleParameter("IN_USER_ID", OracleType.VarChar)).Value = attachment.UserID;
            command.Parameters.Add(new OracleParameter("IN_FILE_CONTENT", OracleType.Blob)).Value = tempLob;
            command.Parameters.Add(new OracleParameter("ERROR_DESC", OracleType.VarChar, 4000)).Direction = ParameterDirection.Output;

            command.ExecuteNonQuery();
            transaction.Commit();

            //Check errors and handle it (log, throw exception etc)
            errors = command.Parameters["error_desc"].Value.ToString();
            HandleExceptions.CheckError(errors);
        }
        catch (Exception e)
        {
            string errMsg = e.Message;
            throw;
        }
    }

Answer 2

• 命令对象是否与连接对象关联？
• 连接是否打开？

一般来说，您会得到如下所示的内容：

Dim conn as new OracleConnection() 'This may have parameters, such as the connection string
OracleCommand2.Connection = conn
conn.Open()
' execute the command
conn.Close()

重要：此代码是凌乱的徒手代码。我不再那么熟悉 VB 语法，但在 C# 中，您可以在 using 语句的声明中实例化 OracleConnection 对象。如果 VB 中没有这样的东西（尽管我怀疑有），那么您可以将其包装在 try/catch/finally 中，以确保连接正确关闭并且对象正确处置。