Unable to pass parameter OracleParameter.Value > 4k

Devendra · Post by **Devendra** » Wed 16 Mar 2011 12:52

Hi,

We are stuck with one problem. In our application User can make large query to the DB on UI. We use StoredProcedure and pass User query as where clause. But in some cases the query becomes lengthy and OracleDataAdapter returns "ORA-01460: unimplemented or unreasonable conversion requested" error.
We have observed that when the OracleParameter value (string value) greater than 4k size then the error is coming. Oracle support parameter length around 32k.
How much parameter size is supported by Devart.

Waiting for reply ASAP.

Thanks
Devendra

Shalex · Post by **Shalex** » Wed 16 Mar 2011 13:33

Please give us the following information:
1) the exact version (x.xx.xxx) of your dotConnect for Oracle. You can find it in the Tools > Oracle > About menu of Visual Studio;
2) are you using OCI (via Oracle client) or Direct mode?
3) send us a small test project with the corresponding DDL/DML script to reproduce the issue in our environment;
4) the versions of your Oracle server and Oracle client (for the OCI mode).

The parameter's size depends on the parameter's type. E.g., OracleDbType.VarChar's max size is 4000 bytes.

Devendra · Post by **Devendra** » Fri 18 Mar 2011 09:45

Hi Shalex,

Thanks for the reply. Yes you are right we are using OracleDbType.VarChar. We have tested with the Devart's latest version also but max size is limited to 4k bytes.
Since ORACLE supports around 32k, Can you guys increase the max size to 32k.

Regards
Devendra

Shalex · Post by **Shalex** » Fri 18 Mar 2011 14:52

I will post here when the issue is fixed.

Devendra · Post by **Devendra** » Mon 21 Mar 2011 06:45

Hi Shalex,

It would be more helpful, if it is fixed ASAP.

Thanks & Regards
Devendra

Shalex · Post by **Shalex** » Mon 21 Mar 2011 17:05

The OracleDbType.VarChar parameter's max size will be increased from 4000 to 32767 symbols starting from the next build of dotConnect for Oracle. I will post here when it is available for download.

Shalex · Post by **Shalex** » Fri 25 Mar 2011 13:19

The 6.10.126 build of dotConnect for Oracle includes the mentioned fix.
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=20584 .

Devendra · Post by **Devendra** » Thu 31 Mar 2011 09:16

Hi Shalex,

I have verified with the 6.10.126 build and still it is not working. I am setting OracleParameter in following manner:
// passing "whereClause" variable with data length around 29750 characters

OracleParameter parameterCnt = new OracleParameter();
parameterCnt.OracleDbType = OracleDbType.VarChar;
parameterCnt.ParameterName = "whereClause";
parameterCnt.Direction = ParameterDirection.Input;
parameterCnt.Value = whereClause;

I am still getting "ORA-01460: unimplemented or unreasonable conversion requested" exception. Could you please verify at your end.

Thanks & Regards
Devendra

Shalex · Post by **Shalex** » Tue 05 Apr 2011 12:55

I cannot reproduce the ORA-01460 error with dotConnect for Oracle v 6.10.126. Please make sure that:
1) Devart.Data.dll v 5.0.227 and Devart.Data.Oracle.dll v 6.10.126 are loaded to the process of your application (the Debug > Windows > Modules menu of VS);
2) you are setting the Size properties of your OracleParameter objects in the code explicitly.

If the problem persists, please send us a small test project with the corresponding DDL/DML script to reproduce the problem in our environment.

Devendra · Post by **Devendra** » Wed 13 Apr 2011 07:09

Hi Shalex,

I sent you a TestApplication on mail id:(AlexSh at devart*com).
Please acknowledge me if you have received and have a look on TestApp.

Thanks & Regards
Devendra

Shalex · Post by **Shalex** » Wed 13 Apr 2011 16:42

Devendra, we have received your test project. If I am setting the parameter size explicitly, it works in my environment.
Please follow these steps:
1) set the size of your "whereClause" parameter explicitly to 4022 because the length of your "whereClause" string variable is 4022:
//parameterCnt.Size = 2000;
-->
parameterCnt.Size = 4022;
2) set the break point in your code and navigate to the Debug > Windows > Modules menu of Visual Studio to make sure that Devart.Data.Oracle.dll of the 6.10.126 version is loaded to the process of your application.

If this doesn't help, please send me the script of your stored procedure.

Devendra · Post by **Devendra** » Fri 01 Jul 2011 10:16

Hi Alex,

I have sent you a complete sample application, we are still able to reproduce this problem with 126 version dll of Devart.Data.
Please let me know your findings ASAP.

Thanks & Regards
Devendra

Shalex · Post by **Shalex** » Mon 04 Jul 2011 13:42

Devendra,

We have answered you by e-mail.

Devendra · Post by **Devendra** » Thu 25 Aug 2011 11:34

Hi,

In absence of Devendra, I am (Sachin Kumar) working on this issue.

I have verified the sample application (which Devendra has sent you) with the 6.10.126 build and it is not working. Even I have verified this issue with your latest version 6.30.202 also but the same error is displayed.

Error:
ORA-01460: unimplemented or unreasonable conversion requested

Could you please verify it again at your end and let me know your finding.

Thanks
Sachin

Shalex · Post by **Shalex** » Wed 31 Aug 2011 15:45

Sachin,
As we answered to Devendra by e-mail, we cannot reproduce the issue in our environment.
Please follow these steps:
1) set the break point in your code and navigate to the Debug > Windows > Modules menu of Visual Studio to make sure that Devart.Data.Oracle.dll of the 6.30.202 (or 6.10.126) version is loaded to the process of your application;
2) Devendra has sent only DDL script of the TEST_WELL_RES_MAT_VIEW table. Maybe we need some test data (DML script) to reproduce the issue?
3) tell us the exact text of the error, your call stack, and the line of code where the error occurs;
4) specify the version of your Oracle server and its NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET settings.
You can post information here or send it via our contact form.