0

I have a stored procedure for inserting data and specially a document more than 4000 characters when i am inserting from plsql it is inserting data and document however when i am calling it from asp.net project it is not uploading a document more than 4000 characters but it is inserting a small document.I am inserting like this

Database _db = DatabaseFactory.CreateDatabase();
ReportTemplateSchema _schema=(ReportTemplateSchema)_envelope.GetMaster();
DbCommand cmd = _db.GetStoredProcCommand("INSRT");
_db.DiscoverParameters(cmd);
_db.AddInParameter(cmd, "Tmp_Document", DbType.Object, _schema.Document);
_db.ExecuteNonQuery(cmd);

ora-01460 unimplemented or unreasonable conversion requested this is because of large document.

muhammad
  • 11
  • 1
  • 3
  • Can you post the code for the procedure (or at least its definition) you are calling and the command you are calling the procedure with (`INSRT` is not a valid PL/SQL statement). SQL has a limit of 4000 bytes for `VARCHAR2` datatype and PL/SQL has a limit of 32k for `VARCHAR2` so it appears that you are somehow passing the value as an SQL `VARCHAR2` and not as a `CLOB`. – MT0 Jul 12 '16 at 08:53

1 Answers1

0

Oracle has a limit on the full size of the command and parameters that can be sent to the DBMS, you're exceeding that limit. There are a set of functions specifically for reading and writing Lobs and Clobs using streams or pages that you should use instead.

See http://www.codeproject.com/Articles/13675/Using-C-for-Inserting-CLOB-Data-in-Oracle for an example of how to do this in .net

Russell Young
  • 2,033
  • 1
  • 14
  • 12
  • But i can save the same document from sqldeveloper. Can you give an example of function for reading or writing the document – muhammad Jul 12 '16 at 07:21
  • not using an insert statement? Arent you using the UI to upload the file, which will be using the lob/clob library to add the data to oracle... Link to an example added to my edit – Russell Young Jul 12 '16 at 07:23
  • In fact, just found a duplicate question & answer here as well http://stackoverflow.com/questions/3557995/issues-calling-stored-procedure-from-c-sharp-with-large-clob?rq=1 – Russell Young Jul 12 '16 at 07:28
  • That is not a duplicate - just that no-one spotted the actual problem in that linked question that the value is being passed into a string literal (which is then being stored in the `CLOB` rather than setting the `CLOB` value directly). The string literal [has a 32k limit](http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/datatypes.htm#i43252) which is why it is failing. I have added a comment on that linked question. – MT0 Jul 12 '16 at 08:26