Data Replication 2
Results 1 to 5 of 5

Thread: Error insertion - Invalid Number (ORA-01722)

  1. #1
    Grégory is offline Junior Member
    Join Date
    Jul 2011
    Posts
    3
    Rep Power
    0

    Error insertion - Invalid Number (ORA-01722)

    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

  2. #2
    Costi Zaboura is offline World Wide Support Manager
    Join Date
    Aug 2006
    Posts
    300
    Rep Power
    14
    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


    thx
    Costi
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    Grégory is offline Junior Member
    Join Date
    Jul 2011
    Posts
    3
    Rep Power
    0

    Unhappy

    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!

  4. #4
    Gadi.Farhat is offline Development Manager
    Join Date
    Mar 2009
    Posts
    255
    Rep Power
    11
    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
    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  5. #5
    Grégory is offline Junior Member
    Join Date
    Jul 2011
    Posts
    3
    Rep Power
    0
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •