Passing a large blob to Oracle database

Topics: Data Access Application Block
Apr 28, 2011 at 4:30 PM

I'm using the latest/greatest version of the DAAB and am trying to pass a multi-megabyte valueof type DbType.Binary to a Oracle stored proc (the parameter's data type is blob). The code works fine for small files but fails for larger files with the error:

"ora-01460 unimplemented or unreasonable conversion requested blob"

Searching the web on that string returned many hits but nothing that specifically indicates that I can't do this with the DAAB (most "solutions" indicated to use a SQL statement instead of stored proc - unfortunetly, this is not an option for me).

So, can I do this with the DAAB and a stored proc? How about with ODP.NET and a stored proc (any size limitiations if I use ODP.NET instead of the DAAB)? Any help would be greatly appreciated.

My code is as follows:

      public void StoreMuFile(byte[] fileContents, string fileName)
      {
         DbTransaction transaction = null;
         DbCommand     command     = _database.GetSqlStringCommand("SYS.StoreMUFile");
         command.CommandType       = CommandType.StoredProcedure;

         try
         {
            transaction         = _connection.BeginTransaction();
            command.Transaction = transaction;

            _database.AddInParameter(command, "BlobParam"DbType.Binary, fileContents);
            _database.AddInParameter(command, "FileName"DbType.String, fileName);

            _database.ExecuteNonQuery(command);

            transaction.Commit();
         }

         catch
         {
            if (transaction != null)
            {
               transaction.Rollback();
            }

            throw;
         }
      } // StoreMuFile()
Apr 29, 2011 at 4:30 AM

Hi,

I think this is a limitation with System.Data.OracleClient. I found this thread suggesting a workaround. Hope this helps.

 

Noel Angelo Bolasoc
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com