D'Arcy from Winnipeg
Solution Architecture, Business & Entrepreneurship, Microsoft, and Adoption

Fun with BLOBS and Oracle SP's

Wednesday, August 22, 2007 4:40 AM

I'm working on a file-upload component on my current project, saving the file to an Oracle database. I've got it working, testing it out, things are going just SPLENDID.

Then out of nowhere I get this error:

ORA-01460: unimplemented or unreasonable conversion requested.

Odd...even odder that if I upload one image file things are fine, but if I upload another one it craps out.

It turns out that Oracle stored procedures have a size limit of 32k! What does that mean? Well, it means that you can't upload a BLOB value (like a file) through a stored procedure in Oracle unless you're dealing with really small files OR you want to write some custom parsing code that will break up a binary array into little chunks and loop through subsets appending it to the database field as you go.

Most of the solutions I've found online suggest to save your non-BLOB code through the stored proc, and then use straight SQL to save the BLOB...because straight SQL will work...but a stored proc won't.

Does that not seem inherintly stupid to anyone else?!

D




Feedback

# 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

# re: Fun with BLOBS and Oracle SP's

Try using a transaction. This worked for me. 10/7/2010 1:41 PM | Leonardo Vannucci

# re: Fun with BLOBS and Oracle SP's

Thank you Leonardo!! Using a transaction fixed my issue!! 10/24/2013 10:50 AM | Carrie

Post a comment