Error: ORA-01861: literal does not match format string

[Former Member]

Hi,

I am doing a RFC-XI-JDBC scenario.

In the CC monitoring , i am getting this error for the reciver CC:

"Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. "TableNAMe"(structure 'STATEMENTNAME'): java.sql.SQLException: ORA-01861: literal does not match format string "

Please guide me what can be the cause and how to solve it.

Thanks,

Puneet

[Shabarish_Nair · Accepted solution]

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm - your receiver structure should match the xml as shown here.

[Former Member · Accepted solution]

Shabarish...can you also look at the payload and provide me some suggestions.

[Former Member]

Thanks..

[Former Member]

Thanks everyone...

I have resolved the issue....it was a data type mismatch in the date field in SAP and Oracle. Had to write a user-defined function to convert the SAP standard into DD-MON-YY.

[Former Member]

Hello Puneet and All,

I encounter same issue with you. The fieldname's data type in Oracle and PI is already date .but there is a error as following.

Could you please give me a advice?

Thank you & best regards,

Hubery

[Former Member]

Hello All,

I resolved the issue. I change the format of date to DD-MON-YY via mapping.

Many thanks & best regards,

Hubery

[Former Member]

I have checked all the settings ..and they looks absolutely fine.

All the fields are in capital letters both in SAP as well as Oracle.

I think the error is due to data type mismatch between Oracle fields and SAP fields.

The fields in Oracle are of type VARCHAR and in SAP the type is Char ( string in XI).

Also, the date format differs between SAP and oracle (DD-MON-YYYY).

Please sugeest me how to go about it ?

[nisarkhan_n]

Are you sending the date from XI in this (DD-MON-YYYY) to Oracle? the data type should not be issue, check the length of the field in database.

[Former Member]

This is how my payload looks like :

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

<ns1:MT_JDBC_REC xmlns:ns1="https:namespace.scene3">

<STATEMENTNAME>

<TABLE_NAME action="INSERT">

<TABLE>ggclgis</TABLE>

<access>

<VALVE_ID>12584</VALVE_ID>

<EQUNR>122</EQUNR>

<ERNAM>12122</ERNAM>

<INVNR>1212</INVNR>

<GROES>1212</GROES>

<ELIEF>123</ELIEF>

<GWLEN>21-jul-2008</GWLEN>

<GWLDT>12-jun-2006</GWLDT>

<SERGE>wqwqw</SERGE>

<TYPBZ>wqwqwq</TYPBZ>

</access>

</TABLE_NAME>

</STATEMENTNAME>

</ns1:MT_JDBC_REC>

Please tell me if it looks fine.

[Former Member]

No, I am sending the date data in standard date format of SAP ( DATUM).

Can you please explain me why do you think the data type mismatch should not be an issue here ?

[nisarkhan_n]

What i meant was the length of the field which is holding the data field in the D/B is it 10 length?

[nisarkhan_n]

Can you check this "TableNAMe" your payload has name "TABLE_NAME".

[Former Member]

In Sender side SAP has Date field (YYYYMMDD) stored as char(8),

whereas in Oracle it is using standard date field (DD-MON-YYYY).

[nisarkhan_n]

in the error description it the log says "TableNAMe"

But the payload your payload has name "TABLE_NAME". can you check this?

[Former Member]

Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. 'ggclgis' (structure 'STATEMENTNAME'): java.sql.SQLException: ORA-00942: table or view does not exist

this is how the error looks like in Audit log where ggclgis is my table name.

TABLE_NAME is as in <dbTableName action=”INSERT”>

[Former Member]

Puneet,

Add the parameter <b>logSQLStatement</b> with value <b>true</b> in the receiver JDBC adapter. You can see the resultant SQL query formed in Msg monitor.

What is the type you have used while declaring the JDBC data type. Try using xsd:string for all elements.

Regards,

Jai Shankar

[Former Member]

Hi,

Check these links

http://help.sap.com/saphelp_nw70/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/frameset.htm

/people/sap.user72/blog/2005/06/01/file-to-jdbc-adapter-using-sap-xi-30

May help you.

Regards,

Kumar

[nisarkhan_n]

make sure the fieldnames in XI structure is matching with the fieldnames in the table which you are updating in oracle.

The filed names are case sensitive and should exactly match