Fun with BLOBS and Oracle SP's

# re: Fun with BLOBS and Oracle SP's

TBH, I am no longer surprised by stupidity when it comes to Oracle 8/23/2007 9:45 PM | Carl

# re: Fun with BLOBS and Oracle SP's

Actually, this explains why I got that error when I tried to replace some of the code that we use for passing blobs to an SP with the stuff that you posted about the other day (the DBType.Binary stuff).

What this does mean is that somewhere here, I may have a solution to your problem - I'll have a scout around... ;o) 8/23/2007 9:49 PM | Carl

# re: Fun with BLOBS and Oracle SP's

Ok, so here's icky Oracle workaround code #1:

Private Sub AddBlobParameter(ByRef db As EnterpriseLibrary.Data.Database, ByRef cmd As DBCommandWrapper, ByVal paramName As String, ByVal blobData As Byte())

Dim tempOracleCmd As New OracleCommand("declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;", m_conn, m_tx)
tempOracleCmd.Parameters.Add(New OracleParameter("tempblob", OracleType.Blob)).Direction = ParameterDirection.Output

tempOracleCmd.ExecuteNonQuery()

Dim lob As OracleLob

lob = CType(tempOracleCmd.Parameters.Item("tempblob").Value, OracleLob)
lob.BeginBatch(OracleLobOpenMode.ReadWrite)
lob.Write(blobData, 0, blobData.Length)
lob.EndBatch()

Dim OracleCmd As OracleCommand = CType(cmd.Command, OracleCommand)
OracleCmd.Parameters.Add(New OracleParameter(paramName, OracleType.Blob)).Value = lob

End Sub

So basically we this was using the Enterprise Library which didn't have support for blob params.

m_conn is an OracleConnection.
m_tx is an OracleTransaction

OracleConnection, OracleTransaction, OracleCommand and OracleLob are in System.Data.OracleClient.

I'm sure that the principles and the lob creation will be usable in you DAAB stuff.

In the 'declare xx blob' bit, you can replace blob with clob or nclob for character lobs if you needed to - I think that the rest of the code would be the same (apart from using some Encoding.GetBytes() to convert from your string).

I have no idea how this post will look in your comments, I hope it's legible, apologies if it isn't.

HTH.

Carl

8/23/2007 10:08 PM | Carl

# re: Fun with BLOBS and Oracle SP's

I WANA JOIN THIS SITE 10/21/2007 6:36 PM | AMAN