VB.net Oracle Operation is not valid due to the current state of the object

Ok, i am really in need of finding a way to do this via a string to clob instead of using the update query to to do all.

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 " & _
                 "WHE开发者_运维百科RE    (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()

But i get the error:

ERROR: Operation is not valid due to the current state of the object.

On the line:

OracleCommand2.ExecuteNonQuery()

Any help would be great to get this thing working :o)

David

---

Since you could not find an example of creating a temporary lob pointer, here is an example in C# using the Microsoft Enterprise Library from a previous project. This example interfaces with a stored procedure, but is the same approach when using a SQL update and 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;
        }
    }

---

• Is the command object associated with a connection object?
• Is the connection open?

Generally, you'd have something like this:

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

Important: This code is messy free-hand code. I'm not all that familiar with VB syntax anymore, but in C# you'd instantiate the OracleConnection object inside the declaration of a using statement. If there's no such thing in VB (though I suspect there is) then you'd wrap it in a try/catch/finally to make sure the connection is properly closed and the object(s) properly disposed.