"ORA-01036: illegal variable name/number\n" for oracle clob in C#

When I try to create an oracle stored procedure call with clob input and output in C#, I get the following error:

ORA-01036: illegal variable name/number\n

Here's the code itself:

OracleTransaction transaction = connection.BeginTransaction();
OracleCommand command = connection.CreateCommand();
command.Transaction = transaction;
command.CommandText = 
      @"declare xx clob; 
      begin dbms_lob.createtemporary(xx, false, 0); 
      :tempclob := xx; end;";

command.Parameters.Add(new OracleParameter("tempclob", OracleType.Clob))
    .Direction = ParameterDirection.Output;

command.ExecuteNonQuery();

OracleLob tempLob = (OracleLob)command.Parameters[0].Value;
//byte[] tempbuff = new byte[10000];
byte[] tempbuff = System.Text.Encoding.Unicode.GetBytes(generateXMLMessage());

tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tempLob.Write(tempbuff, 0, tempbuff.Length);
tempLob.EndBatch();
command.Parameters.Clear();
command.CommandText = "InsertMessageAndGetResponseWRP";
command.CommandType = CommandType.StoredProcedure;
//command.Parameters
//.Add(new OracleParameter("ImportDoc", OracleType.Blob)).Value = tempLob;
command.Parameters.Add(new OracleParameter("iSourceSystem", OracleType.VarChar))
       .Value = "XXX";

command.Parameters.Add(new OracleParameter("iMessage", OracleType.Clob))
       .Value = tempLob;


command.Parameters.Add(new OracleParameter("iResponseMe开发者_运维问答ssage", OracleType.Clob)).Direction = ParameterDirection.Output;
command.Parameters.Add(new OracleParameter("retVar ", OracleType.Int32)).Direction = ParameterDirection.Output;

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

Just try

command.CommandText = 
      @"declare xx clob; 
      begin dbms_lob.createtemporary(xx, false, 0); 
      tempclob := xx; end;";

Replace :tempclob with tempclob.

Try adding:

command.BindByName = true;

after you call your parameters.

Which of the two calls to ExecuteNonQuery() is generating the error? I'm guessing it's the second one.

I don't do C#, but from several examples I found online, it looks like when you are using the StoredProcedure command type, you don't want to create actual OracleParameter objects. Instead, you initialize the parameters like this:

command.Parameters.Add("iSourceSystem", OracleType.VarChar).Value = "XXX";

It looks like you might be using the Microsoft Oracle client.

Try using the Oracle 11 client with ODP.Net for 4.0. This gives the best results when dealing with clob's.

Binding variable to an anonymous pl/sql block is not supported. Use a procedure and define all parameters instead.

command.CommandText = "dbms_lob.createtemporary"