Help on JDBC receiver Sql

[Former Member]

I am using JDBC receiver adapter to insert data into oracle db;

when I see the SQL statement in RWB, and I copy paste it into TOAD, it runs ok and successfully inserted into Oracle db, but RWB still show me the error as following:

Unable to execute statement for table or stored procedure. 'R_ERP_SO_HEADER' (Structure 'insert1') due to java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected

INSERT INTO  R_ERP_SO_HEADER (MESSAGE_NO, DATI_MESSAGE, MESSAGE_CODE, SENDER, RECEIVER, MD_SEQ_NO, SONO, USERID, SOSAPTYPE, PONO, PODATE, SALESORG, SALESORGDESC, DISTCHAN, DISTCHANDESC, DIVISION, DIVISIONDESC, SALESGRP, SALESGRPDESC, SALESOFFICE, SALESOFFICEDESC, TRANSPORTCONDITIONCODE, TRANSPORTCONDITION, SOLDTO, SHIPTO, INVTO, DATEMOD, DATEENT, TIMESTAMP, CUSTCERTFICATEID, CUSTPAYERID) VALUES (NULL, to_date('20070627223143','yyyymmddhh24miss'), 2030, NULL, NULL, '0000121079|0D478246C141D0D7E10000000A01FA32', 0000121079, 'CAOBO', 1, NULL, to_date('2007-05-24','yyyy-mm-dd'), 5000, '迁钢钢铁产品销售', 20, '经销商', 16, '钢材 --- 热轧', 042, '销售公司:分销科(热轧', 0015, '销售公司：热轧销售处', 01, NULL, NULL, NULL, NULL, NULL, to_date('2007-05-24','yyyy-mm-dd'), to_date('20070627223143','yyyymmddhh24miss'), NULL, NULL)

Is there anything I missed? thank you very much for your time

[Former Member · Accepted solution]

hi Zhang,

yourpassing string to an integer field. check this 销售公司:分销科(热轧' for SALESGRPDESC

[Former Member · Accepted solution]

I have defined char as all fields data type except date fields.

so I don't think this is the problem, right?

[Former Member · Accepted solution]

did you define same type in datatype of respostiroy. all fields as char except date

[Former Member · Accepted solution]

I defined all data type as String except date

[Former Member · Accepted solution]

can you run now let me know the error

[Former Member · Accepted solution]

the error is still the same as following:

Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. 'R_ERP_SO_HEADER' (structure 'insert1'): java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected

[Former Member · Accepted solution]

can u remove 24 in target date format and try '20070627223143','yyyymmddhh24miss'), this format is used twice so remove 24 in two place

[Former Member · Accepted solution]

OK, I have removed 24, but the error is still the same

[Former Member · Accepted solution]

is ur frist filed MESGNO accepts null value can you send some value into it

Sreeram Reddy

[Former Member · Accepted solution]

Problem show up when I disable all fields.

It works to disable all fields, and no error occurs, when I add to_date() into date fields, the error comes up, is there anyway to work arround?

[Former Member · Accepted solution]

this blog should help slove ur date problem.

regards

sreeram.g.reddy

[Former Member · Accepted solution]

which blog?

Could you give me the link?thank you very much for your help and time

[former_member189387 · Accepted solution]

Hi Qi ,

If you want to to insert .The XML structure to insert data to table as follows

Structure :

<StatementName2>

<dbTableName action=”INSERT”>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2</col2>

</access>

<access>

<col1>val11</col1>

</access>

</dbTableName>

</StatementName2>

The equvalent SQL query for above structure is [ two insert staement simultaneously ]

<b> INSERT INTO dbTableName (col1, col2) VALUES(‘val1’, ‘val2’)

INSERT INTO dbTableName (col1) VALUES(‘val11’)</b>

One access block eqivalent to onw INSERT stament

Helpful Blogs

For synchronous receiver JDBC have a look into the blog:

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

and for stored procedure ref:/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

Best Regards.,

V.Rangarajan

• Please Assign points if helpful *****************

[Former Member · Accepted solution]

check this guy it should slove ur problem

/people/alessandro.berta/blog/2005/10/04/datetime-datatypes-and-oracle-database

Regards

Sreeram.G.Reddy

[Former Member]

Hai Zhang,

The Error is due to a non-numeric character found where a digit was expected

Cause: You tried to enter a date value using a specified date format, but you entered a non-numeric character where a numeric character was expected.

Action: The options to resolve this Oracle error are:

Check the date formats recognized by the <i><b>to_date function</b></i>. Correct the date value and retry.

Thanks & regards

harsha

********If Reward Points if found useful*********

[Former Member]

Yes, I have used function to_date() to convert date format, and I have displayed SQL statement in RWB&#65292; and copy pasted this SQL statement to TOAD, TOAD runs ok and successfully inserted into oracle, but in XI the error described above show up, I feel very strange.