Attention: We are retiring the ASP.NET Community Blogs. Learn more >

Oracle stored procedure and Blob data type problem

Hi,

Few days back I was working with Oracle database and one of my stored procedures which was inserting a blob data to the database.  But I started getting a strange when the data in the blob data type got large. For large blob data I was getting the following error.

ORA-01460: unimplemented or unreasonable conversion requested.

The system would work consistently for small files but for large files I was getting this error.  After Doing a bit of Google I found that this is a drawback of using stored procedure in Oracle database.  If you try to pass over 32 Kb (or greater than 32512 char of string) then oracle will give this error in case of Blob data type.

The only simple solution I found for the problem was to use inline query to the database for updating large Blob data. There is no restriction on the inline query.

Regards
Vikram

4 Comments

  • Are you using odp.net? I've have applications that store BLOBs that are over 100 M Bytes on a regular basis, and have never seen this problem.

  • This is a problem with the provider, not the database. If you use Oracle's provider (ODP.NET -- a free download from oracle), this restriction does not exist.

  • Can you post an example? The type of data I'm trying to store is binary, so when I'm constructing the inline SQL, do I do something like...?

    byte[] bytesToSave;
    string sql = String.Format("update table set column = {0} where a=b", bytesToSave.ToString();

    or something like that?

  • Hi Vikram,
    I have same sort of requirement in my project, where i need to insert BLOB data(zipped file) to Oracle. If you please share the inline function you used and other prerequisite commands to run, then it would be of great help to me.

    Thanks in advance,
    Karim

Comments have been disabled for this content.