JDBC Oracle Action on Field type NUMBER !!!

[Former Member]

Hello all,

I have the following issue to insert or update a field in an oracle database whose type is NUMBER(12)

Value to put for example : 26000

N.B. This field belongs to table key => it appears 2 times in my UPDATE_INSERT STATEMENT !! in access node and in key node....

We tried the following solutions :

1) put type xsd:long in data type and put directly 26000 in XML node

=> Adapter exception ORA-01858: a non-numeric character was found where a numeric was expected

2) put type xsd:string in data type and put directly 26000 in XML node

=> Adapter exception ORA-00904: "26907": invalid identifier

3) put type xsd:string in data type and put value to_number("26000") in XML node

=> Adapter exception ORA-01858: a non-numeric character was found where a numeric was expected

4) put type xsd:string in data type and put value to_number('26000') in XML node

=> Adapter exception ORA-00907: missing right parenthesis

What can we try else ???

Do you have the solution ???

Thanks a lot

[Former Member]

I added "hasQuot" attribute with NO value on my 3 dates :

Now I have the following exception :

Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. 'GEN$ILOTS.NC_LOT_TRACKWISE_HISTO' (structure 'SQL_Update_Statements'): java.sql.SQLException: ORA-00904: "YYYY-MM-DD HH24:MI:SS": invalid identifier

It's very borring !

[bhavesh_kantilal]

Hi,

<i>YYYY-MM-DD HH24:MI:SS</i> -- This is not a valid Oracle Date.

Take a look at this blog to see a workaround to deal with oracle Dates.

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

Regards,

Bhavesh

[Former Member]

Hi,

Change all double-quots in to_date() function to single-quot like

<DATE_DE_FIN_PREVUE>to_date('2006-03-21 00:00:00', 'YYYY-MM-DD HH24:MI:SS')</DATE_DE_FIN_PREVUE>

Regards,

Uma

[Former Member]

Must I set the hasQuot attribute for target DATE type fields ???

[Former Member]

I turn all my DT field into xsd:string and I still have the issue :

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

[bhavesh_kantilal]

Jean,

What you would need to do is use attribute <b>hasQuot</b> with the value = '<b>NO</b>' for the field which is a number.

To the corresponding field , add the attribute <b>hasQuot</b> in the datatype and in the mapping map it to a constant '<b>NO</b>' .

This will solve your issue.

Just check the last part of this link for morei info,

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Regards,

Bhavesh

[Former Member]

Here is my XML :

[/code]

All fields are defined as xsd:string in DT except PR_ID that is a xsd:long !!!!

I have the following exception :

ORA-01858: a non-numeric character was found where a numeric was expected

[Former Member]

I do delete before insert or update because the key of the table is composed of 3 fields :

- PR_ID NUMBER(12)

- LOT_CONCERNE VARCHAR

- DATE_TRAITEMENT DATETIME (Date of entry (ms))

[bhavesh_kantilal]

Hi,

><i>All fields are defined as xsd:string in DT except PR_ID that is a xsd:long !!!!</i>

I have always used all my elements to be of type STRING and then made use of the attribute <b>hasQuot</b> = NO for the numeric elements.

Try yours with a String and hasQuot=NO and let me know.

Regards,

Bhavesh