Value length exceeds the parameter size

Value length exceeds the parameter size

Postby srjraja » Tue 29 May 2012 11:12

Hi,

Am using DevArt for Oracle version 670Pro, getting below exception while performing execute non query operation,

Exception :: An error occurred while executing the command definition. See the inner exception for details.
InnerException :: Devart.Data.Oracle.OracleException (0x80004005): Value length exceeds the parameter size
at Devart.Data.Oracle.OracleParameter.a(OracleDbType A_0, Object A_1, Object A_2, Byte[] A_3, Hashtable A_4, Int32 A_5, Int32 A_6, Int32 A_7, Int32 A_8, Int32 A_9, Boolean A_10, OracleConnection A_11, ParameterDirection A_12, OracleType A_13, ai A_14, Boolean& A_15)
at Devart.Data.Oracle.OracleParameter.a(aa& A_0, Boolean A_1, OracleConnection A_2, Byte[] A_3, Hashtable A_4, ai A_5, Boolean& A_6, Int32 A_7)
at Devart.Data.Oracle.OracleCommand.a(ah A_0, Int32 A_1, OracleParameterCollection A_2, ai A_3, Boolean& A_4)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at Devart.Data.Oracle.Entity.f.a(CommandBehavior A_0)
at Devart.Common.Entity.ad.b(CommandBehavior A_0)
at Devart.Data.Oracle.Entity.f.b(CommandBehavior A_0)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
StackTrace :: at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
at System.Data.EntityClient.EntityCommandDefinition.Execute(EntityCommand entityCommand, CommandBehavior behavior)
at System.Data.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.EntityClient.EntityCommand.ExecuteScalar[T_Result](Func`2 resultSelector)
at System.Data.EntityClient.EntityCommand.ExecuteNonQuery()
at xxxxxMethod(String P_TICKET_CODE, String P_CLIENT_NUMBER, Nullable`1 P_DOMAIN_COMPLETED, Nullable`1 P_EMP_SEQ_NO, String P_ACCT_LEVEL_TICKET, String P_ACTION, Nullable`1 P_TIME_WORKED_ON, String P_ACTION_STATUS_CODE, String P_CURRENT_ACTION_CODE, String P_HIGHLIGHTS, String P_TRIGGER_OPTION, String P_TRIGGER_REASON, String P_SUB_STATUS, String P_CLOSE_TASK, Nullable`1 P_TYPE, String P_PRIVATE_NOTE, String P_TYPE_NAME, String P_EMAIL_TO, String P_CUR_WAITING_ON, String P_WAITING_ON, String P_NEW_DOMAIN, String P_CUR_DOMAIN, String P_NEW_CATEGORY, String P_CUR_CATEGORY, String P_NEW_PRIORITY, String P_CUR_PRIORITY, String P_VENDOR_TICKET, String P_CUSTOMER_TICKET, String P_ASSIGN_TO, Nullable`1 P_FROM_NOW, String P_CREATE_WS, String P_CURRENT_DISPOSITION, String P_NEW_DISPOSITION, String P_TICKLER_DATE, String P_EMAIL_SUBJECT, String P_ALERT_LIST, String P_REPLY_TO, String P_CALL_CENTER, String P_CALL_QUEUE, String P_CONTACTOR_TYPE, String& C_ERROR_CODE, String& C_ERROR_DESC)

the snippet given below is an auto generated using devart entity model template.

EntityParameter P_ACTION_STATUS_CODEParameter = new EntityParameter("P_ACTION_STATUS_CODE", System.Data.DbType.String);
if (P_ACTION_STATUS_CODE != null)
P_ACTION_STATUS_CODEParameter.Value = P_ACTION_STATUS_CODE;

My problem is while inserting a string of length 32000 throwing the above exception. But it works normally with string of minimal length. Please provide solution to this issue ASAP.
srjraja
 
Posts: 3
Joined: Tue 29 May 2012 11:03

Re: Value length exceeds the parameter size

Postby srjraja » Thu 31 May 2012 09:35

Hi,

Found a way to close this issue, have used EntityParameter TName = new EntityParameter("Name", System.Data.DbType.AnsiString, 80000);

AnsiString accepts 8k chars by default, and you have an option of resetting the size. Also, please check the length of the string you are going to insert, i was in need of 32k chars to be pushed, hence declared almost double the size.

Hope this helps someone,

Cheers,
Raj
srjraja
 
Posts: 3
Joined: Tue 29 May 2012 11:03

Re: Value length exceeds the parameter size

Postby Shalex » Thu 31 May 2012 11:59

The size of the VARCHAR2 parameter in Oracle can be up to 32KB (not more). You should use the CLOB data type for bigger data. Please use the dbMonitor tool that performs per-component tracing of database events such as commit, rollback, SQL statement execute etc to find out the data type of the parameter which is sent to server:
Download link: http://www.devart.com/dbmonitor/dbmon3.exe
Documentation: http://www.devart.com/dotconnect/oracle/docs/?DbMonitor.html
Shalex
Devart Team
 
Posts: 7465
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle