Oracle BLOB with asp.net

Oracle BLOB with asp.net 

http://msdn.microsoft.com/en-US/library/cydxhzhz(v=VS.80).aspx

cannot create a Binary Large Object (BLOB) from a byte array

Using the code above, I get the error:
"invalid operation. the connection is closed"

http://support.microsoft.com/default.aspx?scid=kb;en-us;322796

the key: "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;"

It is a misnomer to assume we will pre-populate using "INSERT INTO tablewithlobs values (1, 'AA', 'AAA', N'AAAA')";

Here is the VB.NET code from the Microsoft site that worked for me, followed by a C# example I had found first from Experts Exchange:

Dim conn As New OracleConnection("server=Oracle;Uid=uid;pwd=pwd")
Dim filePath As String
Dim bigData As Byte()
Dim t As Date

t = Now

filePath = "C:\mytest.bmp" 'Add the path to the file you want to insert
If Not File.Exists(filePath) Then
' handle error
End If


Dim fs As Stream = File.OpenRead(filePath)
Dim tempBuff(fs.Length) As Byte

fs.Read(tempBuff, 0, fs.Length)
fs.Close()
conn.Open()

Dim tx As OracleTransaction
tx = conn.BeginTransaction()

Dim cmd As New OracleCommand()
cmd = conn.CreateCommand()

cmd.Transaction = tx

cmd.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;"
cmd.Parameters.Add(New OracleParameter("tempblob", OracleType.Blob)).Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()



Dim tempLob As OracleLob
tempLob = cmd.Parameters(0).Value
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite)
tempLob.Write(tempBuff, 0, tempBuff.Length)
tempLob.EndBatch()

cmd.Parameters.Clear()
cmd.CommandText = "InsertBlob.TestBlobInsert"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New OracleParameter("BlobParam", OracleType.Blob)).Value = tempLob

Try
cmd.ExecuteNonQuery()
Catch myex As Exception
MsgBox(myex.Message)
End Try

tx.Commit()

--------------------------------------

From "ofaniel" on Experts Exchange where I found it first...

Here is a similar method in C# ... at the veeeery bottom of this Experts Exchange page:

http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_24230043.html

private void UpdateCaseNotes(string SchemaReference, string DefectId, string Comments)
{
using (OracleConnection conn = new OracleConnection(ConfigurationSettings.AppSettings["DBConnStr"]))
{
conn.Open();

OracleCommand cmd = new OracleCommand();
cmd = conn.CreateCommand();

OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;

cmd.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :tempclob := xx; end;";
cmd.Parameters.Add(new OracleParameter("tempclob", OracleType.Clob)).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();

byte[] cbComments = Encoding.Unicode.GetBytes(Comments);

OracleLob tmpClob = default(OracleLob);
tmpClob = (OracleLob)cmd.Parameters[0].Value;
tmpClob.BeginBatch(OracleLobOpenMode.ReadWrite);
tmpClob.Write(cbComments, 0, cbComments.Length);
tmpClob.EndBatch();

cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "UPDATE " + SchemaReference + ".BUG SET " + colCaseComment + " = :clb WHERE BG_BUG_ID = " + DefectId;
cmd.Parameters.Add(new OracleParameter("clb", OracleType.Clob)).Value = tmpClob;

try
{
cmd.ExecuteNonQuery();
tx.Commit();
}
catch (OracleException ex)
{
throw new Exception("Error updating case comment.", ex);
}
finally
{
cmd.Dispose();
tx.Dispose();
conn.Close();
}
}
}