Getting error ORA-01401: inserted value too large for column

[Former Member]

Hello ,

I have Configured the scenario IDOC to JDBC .In the SXMB_MONI am getting the succes message .But in the Adapter Monitor am getting the error message as

ORA-01401: inserted value too large for column and the entries also not inserted in to the table.I hope this is because of the date format only.In Oracle table date field has defined in the format of '01-JAN-2005'.I am also passing the date field in the same format only for INVOICE_DATE and INVOICE_DUE_DATE.Please see the target structure .

<?xml version="1.0" encoding="UTF-8" ?>

- <ns:INVOICE_INFO_MT xmlns:ns="http://sap.com/xi/InvoiceIDoc_Test">

- <Statement>

- <INVOICE_INFO action="INSERT">

- <access>

<INVOICE_ID>0090000303</INVOICE_ID>

<INVOICE_DATE>01-Dec-2005</INVOICE_DATE>

<INVOICE_DUE_DATE>01-Jan-2005</INVOICE_DUE_DATE>

<ORDER_ID>0000000000011852</ORDER_ID>

<ORDER_LINE_NUM>000010</ORDER_LINE_NUM>

<INVOICE_TYPE>LR</INVOICE_TYPE>

<INVOICE_ORGINAL_AMT>10000</INVOICE_ORGINAL_AMT>

<INVOICE_OUTSTANDING_AMT>1000</INVOICE_OUTSTANDING_AMT>

<INTERNAL_USE_FLG>X</INTERNAL_USE_FLG>

<BILLTO>0004000012</BILLTO>

<SHIPTO>40000006</SHIPTO>

<STATUS_ID>O</STATUS_ID>

</access>

</INVOICE_INFO>

</Statement>

</ns:INVOICE_INFO_MT>

Please let me know what are all the possible solution to fix the error and to insert the entries in the table.

Thanks in Advance!

[bhavesh_kantilal]

Hi muthu,

// inserted value too large for column

When your oracle insertion throws this error, it implies that some value that you are trying to insert into the table is larger than the allocated size.

Just check the format of your table and the respective size of each field on your oracle cleint by using the command,

DESCRIBE <tablename> .

and then verify it with the input. I dont think the problem is with the DATE format because if it is not a valid date format, you would have got on error like

String Literal does not match type

Hope this helps,

Regards,

Bhavesh

[Former Member]

Hi,

did you try to insert such a value with the same format via SQL Console? Did it work there? Maybe it is not exactly the format Oracle expects.

Anyway, you could always use oracles TO_DATE function eg TO_DATE('01012006', 'ddmmyyyy'). In this case you"ll need to add attribute hasQuot with value NO in the JDBC Adapter SQL.

Best regards

Christine

[Former Member]

Christine,

Thanks for your reply.I am not getting " you"ll need to add attribute hasQuot with value NO in the JDBC Adapter SQL " . Can you explain this ?

Regards.

[Former Member]

Hi Muthu,

if you put a value into any of the colum tags in your statement, the JDBC Adapter will automatically add ' around it, like '01-JAN-2006' so that the database will interpret that as value. Adding the attribute hasQuot with value no to any element in the XML, will prevent this so the database can differentiate between values and functions.

Check the help for more details: http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Best regards

Christine

[Former Member]

Hello Bhavash,

thanks for your help .

Yes it is not because of Date . The error in Invoice type .I have fixed this error by passing single character (constant value : just for the test purpose) and inserted the entries in the table.

Regards,

[bhavesh_kantilal]

Hi Muthu,

Nice to know that the problem has been resolved.

No points..??

regards,

Bhavesh