Error insertion - Invalid Number (ORA-01722)

[Grégory]

Version attunity : 1.1
SQLServeur 2008 R2
Oracle 11g R2 (machine : exadata)

Hello,
I encounter a problem with the Attunity Oracle SSIS Connector when I try to load my data into an Oracle DB by using the Oracle Destination to Attunity composant.
The destination table contains a field of type NUMBER(19,4) (the equivalent of SQLServeur Money datatype). The execution of package fails with this error message :

[Oracle Destination 1 [337]] Error: Fast Load error encountered during PostLoad or Termination phase. Class: Logical
Status: 0 Code: 0
Note:
At: ORAOPRdrpthEngine.c:1128
Text: Conversion error on column '"CHAMP1"'. ORA-01722: Nombre non valide [In english : Invalid Number]

It turns out that the deployment of the package is carried out smoothly but the execution fallsinto error.
We tried with a float in the destination table and the problem remains.
I suspect that the issue could be due to a problem of numeric separator. The NLC_LANG is French and use a “,” separator and not a “.”.

Do some one ever encounter such a problem ? Is there any work around ?

More detail about the test package:
Box Source :

Oledb Source (SQLServeur)
Table source : MyTableSource
Script table source : CREATE TABLE MyTableSource (MyCHAMP MONEY)
Data access mode : SQL Command :
SELECT MyCHAMP FROM MyTableSource

Lié à

Box Data Transformation :
MyCHAMP convertie en numeric [DT_NUMERIC] avec pour précision : 19 et échelle : 4, le champ en sortie se nomme : MyChampCopy

Lié à

Oracle Destination Attunity :
Data access mode : Table Name – Fast Load (Using Direct Path) avec les options : Parrel load et No Logging d’activées
Script table destination :
CREATE TABLE MyTableDestOracle ("CHAMP1" NUMBER(19,4) NOT NULL ENABLE)
Mapping : MyChampCopy -> CHAMP1

[Costi Zaboura]

Hi

pls have a look at the following user forum article that looks similar to what you are experiencing.

http://www.attunity.com/forums/micro...elds-1305.html (KNOWN LIMITATION-Number fields)


thx
Costi

[Grégory]

Hey Costi. Thank you for you answer.

We do encounter the same error message "ERROR ORA-01722" but we are using the 1.1 version and in the post it is said that "The Microsoft Connector Version 1.1 for Oracle by Attunity (...) solves this issue". Could the limitation still be present in the 1.1 version ?

Moreover, we tried to change the DB setting to set the nls_lang setting to American but only the Database and Instance setting were affected, the Session parameters and so the SSIS Attunity component were still in French. The only way we found was an Alter Session (ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '. '; ) but we didn't manage to execute it inside the SSIS Attunity Componotent.

It's really anoing because we discovered this bug during the beginning of the production stage.

Thank you for your help!

[Gadi.Farhat]

Hi Gregory,

You need to change the NLS_LANG on the client and not on the database. This can be done in the registry for the specific client key.

To make sure you use the specific client, where you changed the NLS_LANG, you can use the oracle home property of the connection manager.

Regards,

Gadi

[Grégory]

Hey Gady, thank you very much for you're answer, it did correct the problem. So it seams that the numeric seperator bug which make decimal numbers loading fail is still present in the 1.1 version.