Unable to pass parameter OracleParameter.Value > 4k

Unable to pass parameter OracleParameter.Value > 4k

Postby 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
Devendra
 
Posts: 8
Joined: Wed 16 Mar 2011 12:34

Postby 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.
Shalex
Devart Team
 
Posts: 7343
Joined: Thu 14 Aug 2008 12:44

Postby 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
Devendra
 
Posts: 8
Joined: Wed 16 Mar 2011 12:34

Postby Shalex » Fri 18 Mar 2011 14:52

I will post here when the issue is fixed.
Shalex
Devart Team
 
Posts: 7343
Joined: Thu 14 Aug 2008 12:44

Postby Devendra » Mon 21 Mar 2011 06:45

Hi Shalex,

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

Thanks & Regards
Devendra
Devendra
 
Posts: 8
Joined: Wed 16 Mar 2011 12:34

Postby 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
Devart Team
 
Posts: 7343
Joined: Thu 14 Aug 2008 12:44

Postby 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 .
Shalex
Devart Team
 
Posts: 7343
Joined: Thu 14 Aug 2008 12:44

Postby 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
Devendra
 
Posts: 8
Joined: Wed 16 Mar 2011 12:34

Postby 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.
Shalex
Devart Team
 
Posts: 7343
Joined: Thu 14 Aug 2008 12:44

Postby 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
Devendra
 
Posts: 8
Joined: Wed 16 Mar 2011 12:34

Postby 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.
Shalex
Devart Team
 
Posts: 7343
Joined: Thu 14 Aug 2008 12:44

Postby 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
Devendra
 
Posts: 8
Joined: Wed 16 Mar 2011 12:34

Postby Shalex » Mon 04 Jul 2011 13:42

Devendra,

We have answered you by e-mail.
Shalex
Devart Team
 
Posts: 7343
Joined: Thu 14 Aug 2008 12:44

Unable to pass parameter OracleParameter.Value > 4k

Postby 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
Devendra
 
Posts: 8
Joined: Wed 16 Mar 2011 12:34

Postby 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.
Shalex
Devart Team
 
Posts: 7343
Joined: Thu 14 Aug 2008 12:44

Next

Return to dotConnect for Oracle