ORA-01460: unimplemented or unreasonable conversion requested

While trying to INSERT a BLOB type to Oracle from .Net the following error will be thrown, in you try to INSERT the BLOB as part of a procedure or a Batch statement BEGIN .. END;

[System.Data.OracleClient.OracleException] = {"ORA-01460: unimplemented or unreasonable conversion requested\n"}

Make sure that the statement used to insert the BLOG type is an individual SQL statement, it should not be a Procedure or a Batch statement BEGIN … END;

The below code used a batch statement BEGIN .. END to insert a BLOB and will fail with the above exception.

// Get the File name and Extension

strFileName = Path.GetFileName(file.FileName);

strFileExtension = Path.GetExtension(file.FileName);

objDAL = new DataAccessLayer();

//

// Extract the content of the Document into a Byte array

int intlength = file.ContentLength;

Byte[] byteData = new Byte[intlength];

file.InputStream.Read(byteData, 0, intlength);

//

// Save the file to the DB

string strConn = objDAL.strConnection;

objConn = new OracleConnection(strConn);

//

strQuery = "BEGIN";

strQuery += " DELETE FROM DOCUMENTS WHERE PROJECT_ID = '" + ProjectID.ToString() + "';";

strQuery += " INSERT INTO DOCUMENTS(PROJECT_ID, DOCUMENT_ID, DOCUMENT_NAME, DOCUMENT_TYPE, DOCUMENT) VALUES (";

strQuery += "'" + ProjectID.ToString() + "', ";

strQuery += "'" + intDocID.ToString() + "', ";

strQuery += "'" + strFileName + "', ";

strQuery += "'" + strFileExtension + "', ";

strQuery += ":Document);";

strQuery += " END;";

//

OracleParameter blobParameter = new OracleParameter();

blobParameter.ParameterName = "Document";

blobParameter.OracleType = OracleType.Blob;

blobParameter.Direction = ParameterDirection.Input;

blobParameter.Value = byteData;

objCmd = new OracleCommand(strQuery, objConn);

objCmd.Parameters.Add(blobParameter);

//

objConn.Open();

objCmd.ExecuteNonQuery();

objConn.Close();

SOLUTION

Split the Batch into individual statement, make sure that the INSERT statement used to insert the BLOG statement is an individual SQL Statement. Replacing the code as follows solves the issue.

string strConn = objDAL.strConnection;

objConn = new OracleConnection(strConn);

//

strQuery = "DELETE FROM DOCUMENTS WHERE PROJECT_ID = '" + ProjectID.ToString() + "'";

objDAL.ExcuteQuery(strQuery);

//

strQuery = "INSERT INTO DOCUMENTS (PROJECT_ID, DOCUMENT_ID, DOCUMENT_NAME, DOCUMENT_TYPE, DOCUMENT) VALUES (";

strQuery += "'" + ProjectID.ToString() + "', ";

strQuery += "'" + intDocID.ToString() + "', ";

strQuery += "'" + strFileName + "', ";

strQuery += "'" + strFileExtension + "', ";

strQuery += ":Document)";

//

OracleParameter blobParameter = new OracleParameter();

blobParameter.ParameterName = "Document";

blobParameter.OracleType = OracleType.Blob;

blobParameter.Direction = ParameterDirection.Input;

blobParameter.Value = byteData;

objCmd = new OracleCommand(strQuery, objConn);

objCmd.Parameters.Add(blobParameter);

//

objConn.Open();

objCmd.ExecuteNonQuery();

RELATED POST

Save files to Oracle 10g using Asp.net

Retrieve files from Oracle 10g using Asp.net