Hitachi Vantara Pentaho Community Forums
Results 1 to 22 of 22

Thread: Insert/update not looking up correctly

  1. #1

    Unhappy Insert/update not looking up correctly

    Hello,

    I'm using PDI 3.10 and it's really nice ! A lot of eye candy...

    I've got a problem since 2.5 and it's still there in 3.10. It's strange for me because I use Kettle since 1.x and I've never had a problem like this.

    I have a transformation that inserts/updates rows in a Oracle database. The lookup is on two string fields, AT_COD_PROV and AT_TRANS_ID

    CREATE TABLE SEACNG.ATTIVAZION
    (
    AT_COD_PROV VARCHAR2(13)
    , AT_DATA_RIC DATE
    , AT_DATA_TRAN DATE
    , AT_DITTA INTEGER
    , AT_DEP INTEGER
    , AT_BARRE VARCHAR2(13)
    , AT_TRANS_ID VARCHAR2(20)
    , AT_TIPO VARCHAR2(3)
    , AT_IMP NUMBER(19, 2)
    );

    But at the second run of the same data the control simply doesn't look up the rows already inserted and tries to make a frsh insert, which results in a primary key violation.

    I've attached the transformation.

    Could anyone please help me understand where I'm doing wrong ? After two years of using Kettle I'd thought myself more skilled, but evidently I'm not yet...

    Thanks in advance.

    Cheers,
    Paolo
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2007
    Posts
    1,013

    Default

    Did you try changing the "Update" property in the "Update fields" table to "N" for the key fields (AT_TRANS_ID and AT_COD_PROV). If those are the keys you wouldn't want them to be updated.

  3. #3

    Default

    Yes, but still no luck. I've been using Kettle since 2005 or 2006 and this is the first time I'm stuck with something so simple as a Insert/Update step.

    No, the problem looks like it doesn't understand that the row is already there when it makes the lookup, so it starts an insert instead of an update,

    Any ideas ?

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Check if your string key is properly trimmed.
    Verify that there are no other transactions open somewhere on the Oracle target table.

  5. #5

    Default

    Hi, Matt.

    Yes, they should be, since AT_TRANS_ID is trimmed in the read from file step (trimmed both left and right) and the other is a constant I've set myself in a specific step.

    There isn't any other transaction open on that table...

    The strange thing is that is I do manually (using the same data I see in the rowlevel debug log) the select I get the right row.

    Is there any way to debug at low level ? Rowlevel doesn't shed any more light...

    2008/11/12 09:13:26 - inserisci in tabella.0 - Controllo riga: [EPAC45000042436], [8032325056389], [2008/11/12 09:13:26.544], [1], [57], [EURONET], [ 1000], [DEB], [27/10/2008]
    2008/11/12 09:13:26 - estrai ditta deposito e aggiungi data corrente.0 - Segnalato "output terminato" a 1 set di righe di output.
    2008/11/12 09:13:26 - inserisci in tabella.0 - Il campo [AT_TRANS_ID] ha n° 0
    2008/11/12 09:13:26 - inserisci in tabella.0 - Il campo [AT_COD_PROV] ha n° 5
    2008/11/12 09:13:26 - estrai ditta deposito e aggiungi data corrente.0 - Elaborazione terminata (I=0, O=0, R=26, W=26, U=26, E=0)
    2008/11/12 09:13:26 - inserisci in tabella.0 - Il campo [AT_TRANS_ID] ha n° 0
    2008/11/12 09:13:26 - inserisci in tabella.0 - Il campo [AT_BARRE] ha n° 1
    2008/11/12 09:13:26 - inserisci in tabella.0 - Il campo [AT_DATA_RIC] ha n° 2
    2008/11/12 09:13:26 - inserisci in tabella.0 - Il campo [AT_DITTA] ha n° 3
    2008/11/12 09:13:26 - inserisci in tabella.0 - Il campo [AT_DEP] ha n° 4
    2008/11/12 09:13:26 - inserisci in tabella.0 - Il campo [AT_COD_PROV] ha n° 5
    2008/11/12 09:13:26 - inserisci in tabella.0 - Il campo [AT_IMP] ha n° 6
    2008/11/12 09:13:26 - inserisci in tabella.0 - Il campo [AT_TIPO] ha n° 7
    2008/11/12 09:13:26 - inserisci in tabella.0 - Il campo [AT_DATA_TRAN] ha n° 8
    2008/11/12 09:13:26 - inserisci in tabella.0 - Setting preparedStatement to [SELECT AT_TRANS_ID, AT_BARRE, AT_DATA_RIC, AT_DITTA, AT_DEP, AT_COD_PROV, AT_IMP, AT_TIPO, AT_DATA_TRAN FROM SEACNG.ATTIVAZION WHERE AT_TRANS_ID = ? AND AT_COD_PROV = ? ]
    2008/11/12 09:13:26 - rimappa.0 - Segnalato "output terminato" a 1 set di righe di output.
    2008/11/12 09:13:26 - rimappa.0 - Elaborazione terminata (I=0, O=0, R=26, W=26, U=26, E=0)
    2008/11/12 09:13:27 - Oraeuro_write_native - Preparing statement:
    2008/11/12 09:13:27 - Oraeuro_write_native - INSERT INTO SEACNG.ATTIVAZION (AT_TRANS_ID, AT_BARRE, AT_DATA_RIC, AT_DITTA, AT_DEP, AT_COD_PROV, AT_IMP, AT_TIPO, AT_DATA_TRAN) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?)
    2008/11/12 09:13:27 - inserisci in tabella.0 - Setting update preparedStatement to [UPDATE SEACNG.ATTIVAZION
    2008/11/12 09:13:27 - inserisci in tabella.0 - SET AT_BARRE = ?
    2008/11/12 09:13:27 - inserisci in tabella.0 - , AT_DATA_RIC = ?
    2008/11/12 09:13:27 - inserisci in tabella.0 - , AT_DITTA = ?
    2008/11/12 09:13:27 - inserisci in tabella.0 - , AT_DEP = ?
    2008/11/12 09:13:27 - inserisci in tabella.0 - , AT_IMP = ?
    2008/11/12 09:13:27 - inserisci in tabella.0 - , AT_TIPO = ?
    2008/11/12 09:13:27 - inserisci in tabella.0 - , AT_DATA_TRAN = ?
    2008/11/12 09:13:27 - inserisci in tabella.0 - WHERE AT_TRANS_ID = ? AND AT_COD_PROV = ? ]
    2008/11/12 09:13:27 - inserisci in tabella.0 - Valori impostati per il lookup: [EPAC45000042436], [EURONET]
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Errore inatteso :
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : org.pentaho.di.core.exception.KettleStepException:
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Errore nel passo, richiesto a tutti di interrompere a causa di:
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) :
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Error inserting/updating row
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : ORA-00001: unique constraint (SEACNG.ATTIVAZION_276_00) violated

    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) :
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) :
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:291)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2664)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.run(InsertUpdate.java:485)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Error inserting/updating row
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : ORA-00001: unique constraint (SEACNG.ATTIVAZION_276_00) violated

    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) :
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1314)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1227)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1210)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:103)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:274)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : ... 2 more
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Caused by: java.sql.SQLException: ORA-00001: unique constraint (SEACNG.ATTIVAZION_276_00) violated

    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3400)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1262)
    2008/11/12 09:13:28 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : ... 6 more
    2008/11/12 09:13:28 - Oraeuro_write_native - Rollback on database connection [Oraeuro_write_native]
    2008/11/12 09:13:28 - Oraeuro_write_native - Commit on database connection [Oraeuro_write_native]
    2008/11/12 09:13:28 - Oraeuro_write_native - Connection to database closed!
    2008/11/12 09:13:28 - inserisci in tabella.0 - Elaborazione terminata (I=1, O=0, R=1, W=0, U=0, E=1)
    2008/11/12 09:13:28 - Spoon - La trasformazione è terminata!
    2008/11/12 09:13:28 - Acquisizione report Euronet - Rilevata trasformazione
    2008/11/12 09:13:28 - Acquisizione report Euronet - La trasformazione sta uccidendo gli altri passi!
    2008/11/12 09:13:28 - Acquisizione report Euronet - Osservazione passo: leggi_da_file
    2008/11/12 09:13:28 - Acquisizione report Euronet - Osservazione passo: EURONET
    2008/11/12 09:13:28 - Acquisizione report Euronet - Osservazione passo: Prendi nome file unico
    2008/11/12 09:13:28 - Acquisizione report Euronet - Osservazione passo: Copia nome file in uscita
    2008/11/12 09:13:28 - Acquisizione report Euronet - Osservazione passo: estrai ditta deposito e aggiungi data corrente
    2008/11/12 09:13:28 - Acquisizione report Euronet - Osservazione passo: rimappa
    2008/11/12 09:13:28 - Acquisizione report Euronet - Osservazione passo: inserisci in tabella
    2008/11/12 11:29:50 - org.pentaho.di.ui.trans.steps.textfileinput.TextFileInputDialog - getting fields info...



    Thank you for your help.

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    It usually means that the key to update in insert/update does not match the primary key of your table in oracle.

    Regards,
    Sven

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Implicit data conversion between Numeric and String data types, things like that.

  8. #8

    Default

    As for the primary key, I thin it should suffice that the fields are a candidate primary key on the table... anyway, it does a SELECT with those fields in the WHERE so it should see there's a result and go for the update.

    In this case the fields for the lookup are just string and they are mapped on VARCHAR2 on Oracle. I've attached also the data layout for the table.

    So no conversion of data shoul occur. How is it possible ? Is there something obvious I don't see ?

  9. #9

    Default

    What frustrates me is that I don't know how to debug this. Even at rowlevel the log information isn't giving me any clue...

  10. #10
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    What are the table data types?
    What are the data types going into the Insert/Update step?

  11. #11

    Default

    Hi, Matt.

    The types in the table are

    CREATE TABLE SEACNG.ATTIVAZION
    (
    AT_COD_PROV VARCHAR2(13)
    , AT_DATA_RIC DATE
    , AT_DATA_TRAN DATE
    , AT_DITTA INTEGER
    , AT_DEP INTEGER
    , AT_BARRE VARCHAR2(13)
    , AT_TRANS_ID VARCHAR2(20)
    , AT_TIPO VARCHAR2(3)
    , AT_IMP NUMBER(19, 2)
    );

    The types incoming in are

    Fieldname Type Length Precision Step origin
    AT_TRANS_ID String - - rimappa
    AT_BARRE String - - rimappa
    AT_DATA_RIC Date - - rimappa
    AT_DITTA Integer - 0 rimappa
    AT_DEP Integer - 0 rimappa
    AT_COD_PROV String - - rimappa
    AT_IMP Integer - 0 rimappa
    AT_TIPO String - - rimappa
    AT_DATA_TRAN Date - - rimappa


    If you help me find the bug I'll offer you a beer

    Cheers,
    Paolo

  12. #12
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    There are in fact 2 lookup keys:

    AT_TRANS_ID(String) and AT_COD_PROV(String)

    Both are Varchar2 and Strings so there can't actually be any data conversion issue unless it's some weird Oracle codepage issue.

    So, next step: check what constraint SEACNG.ATTIVAZION_276_00 actually is in the database.

    Something like "SELECT * FROM USER_CONSTRAINTS WHERE table_name = 'ATTIVAZION'

    That or use one of the Oracle tools.

    Take care,
    Matt

  13. #13

    Default

    The constraint is defined as follows:
    UNIQUE INDEX ON SEACNG.ATTIVAZION
    (AT_COD_PROV, AT_DATA_TRAN, AT_DITTA, AT_DEP, AT_TRANS_ID, AT_TIPO)

    So the violation is just a duplicate record. Which reflects what I see in the log since it says that it does an INSERT instead of the UPDATE I'd expect since if I do the lookup manually using the SELECT provided by the log I get a row which is the copy that is already in the database.

    What really baffles me is that this isn't the first insert/update step I do on this Kettle system, and the String values aren't really very exotic...

  14. #14
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    I don't think so. The unique index contains these columns : AT_COD_PROV, AT_DATA_TRAN, AT_DITTA, AT_DEP, AT_TRANS_ID, AT_TIPO
    You only do the lookup on 2 : AT_COD_PROV, <missing>, AT_TRANS_ID, <missing>

    As such, you either have to extend the lookup or change the constraint. The database is always right.

  15. #15

    Default

    I've tried that too. Here is the log (same outcome). Logically, if I make a SELECT query (which I see in the logs because it's been prepared with the ? ) and I get a row Kettle should see that row, too...

    Here is the log with all the fields:
    2008/11/13 15:58:33 - inserisci in tabella.0 - Controllo riga: [EPAC45000042436], [8032325056389], [2008/11/13 15:58:33.845], [1], [57], [EURONET], [ 1000], [DEB], [27/10/2008]
    2008/11/13 15:58:33 - inserisci in tabella.0 - Il campo [AT_TRANS_ID] ha n° 0
    2008/11/13 15:58:33 - inserisci in tabella.0 - Il campo [AT_COD_PROV] ha n° 5
    2008/11/13 15:58:33 - inserisci in tabella.0 - Il campo [AT_DITTA] ha n° 3
    2008/11/13 15:58:33 - inserisci in tabella.0 - Il campo [AT_DEP] ha n° 4
    2008/11/13 15:58:33 - inserisci in tabella.0 - Il campo [AT_TIPO] ha n° 7
    2008/11/13 15:58:33 - inserisci in tabella.0 - Il campo [AT_DATA_TRAN] ha n° 8
    2008/11/13 15:58:33 - inserisci in tabella.0 - Il campo [AT_TRANS_ID] ha n° 0
    2008/11/13 15:58:33 - inserisci in tabella.0 - Il campo [AT_BARRE] ha n° 1
    2008/11/13 15:58:33 - inserisci in tabella.0 - Il campo [AT_DATA_RIC] ha n° 2
    2008/11/13 15:58:33 - inserisci in tabella.0 - Il campo [AT_DITTA] ha n° 3
    2008/11/13 15:58:33 - inserisci in tabella.0 - Il campo [AT_DEP] ha n° 4
    2008/11/13 15:58:33 - inserisci in tabella.0 - Il campo [AT_COD_PROV] ha n° 5
    2008/11/13 15:58:33 - inserisci in tabella.0 - Il campo [AT_IMP] ha n° 6
    2008/11/13 15:58:33 - inserisci in tabella.0 - Il campo [AT_TIPO] ha n° 7
    2008/11/13 15:58:33 - inserisci in tabella.0 - Il campo [AT_DATA_TRAN] ha n° 8
    2008/11/13 15:58:33 - inserisci in tabella.0 - Setting preparedStatement to [SELECT AT_TRANS_ID, AT_BARRE, AT_DATA_RIC, AT_DITTA, AT_DEP, AT_COD_PROV, AT_IMP, AT_TIPO, AT_DATA_TRAN FROM SEACNG.ATTIVAZION WHERE AT_TRANS_ID = ? AND AT_COD_PROV = ? AND AT_DITTA = ? AND AT_DEP = ? AND AT_TIPO = ? AND AT_DATA_TRAN = ? ]
    2008/11/13 15:58:33 - estrai ditta deposito e aggiungi data corrente.0 - No end Script found!
    2008/11/13 15:58:33 - estrai ditta deposito e aggiungi data corrente.0 - Segnalato "output terminato" a 1 set di righe di output.
    2008/11/13 15:58:33 - estrai ditta deposito e aggiungi data corrente.0 - Elaborazione terminata (I=0, O=0, R=26, W=26, U=26, E=0)
    2008/11/13 15:58:33 - rimappa.0 - Segnalato "output terminato" a 1 set di righe di output.
    2008/11/13 15:58:33 - rimappa.0 - Elaborazione terminata (I=0, O=0, R=26, W=26, U=26, E=0)
    2008/11/13 15:58:36 - Oraeuro_write_native - Preparing statement:
    2008/11/13 15:58:36 - Oraeuro_write_native - INSERT INTO SEACNG.ATTIVAZION (AT_TRANS_ID, AT_BARRE, AT_DATA_RIC, AT_DITTA, AT_DEP, AT_COD_PROV, AT_IMP, AT_TIPO, AT_DATA_TRAN) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?)
    2008/11/13 15:58:36 - inserisci in tabella.0 - Setting update preparedStatement to [UPDATE SEACNG.ATTIVAZION
    2008/11/13 15:58:36 - inserisci in tabella.0 - SET AT_BARRE = ?
    2008/11/13 15:58:36 - inserisci in tabella.0 - , AT_DATA_RIC = ?
    2008/11/13 15:58:36 - inserisci in tabella.0 - , AT_DITTA = ?
    2008/11/13 15:58:36 - inserisci in tabella.0 - , AT_DEP = ?
    2008/11/13 15:58:36 - inserisci in tabella.0 - , AT_IMP = ?
    2008/11/13 15:58:36 - inserisci in tabella.0 - , AT_TIPO = ?
    2008/11/13 15:58:36 - inserisci in tabella.0 - , AT_DATA_TRAN = ?
    2008/11/13 15:58:36 - inserisci in tabella.0 - WHERE AT_TRANS_ID = ? AND AT_COD_PROV = ? AND AT_DITTA = ? AND AT_DEP = ? AND AT_TIPO = ? AND AT_DATA_TRAN = ? ]
    2008/11/13 15:58:36 - inserisci in tabella.0 - Valori impostati per il lookup: [EPAC45000042436], [EURONET], [1], [57], [DEB], [27/10/2008]
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Errore inatteso :
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : org.pentaho.di.core.exception.KettleStepException:
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Errore nel passo, richiesto a tutti di interrompere a causa di:
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) :
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Error inserting/updating row
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : ORA-00001: unique constraint (SEACNG.ATTIVAZION_276_00) violated

    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) :
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) :
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:291)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2664)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.run(InsertUpdate.java:485)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Error inserting/updating row
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : ORA-00001: unique constraint (SEACNG.ATTIVAZION_276_00) violated

    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) :
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1314)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1227)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1210)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:103)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:274)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : ... 2 more
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : Caused by: java.sql.SQLException: ORA-00001: unique constraint (SEACNG.ATTIVAZION_276_00) violated

    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3400)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : at org.pentaho.di.core.database.Database.insertRow(Database.java:1262)
    2008/11/13 15:58:36 - inserisci in tabella.0 - ERROR (version 3.1.0, build 826 from 2008/09/30 12:32:36) : ... 6 more
    2008/11/13 15:58:36 - Oraeuro_write_native - Rollback on database connection [Oraeuro_write_native]
    2008/11/13 15:58:36 - Oraeuro_write_native - Commit on database connection [Oraeuro_write_native]
    2008/11/13 15:58:36 - Oraeuro_write_native - Connection to database closed!

  16. #16
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Try the same lookup with a database lookup, see if the lookup finds the row.
    Also try to swap out the Oracle JDBC driver with the one you were using previously. Oracle is notorious for introducing bugs in those.

    Matt

  17. #17

    Default

    Hi, Matt.

    Ok, I've found out the source of problem, but still it's puzzling me.

    The field AT_TRANS_ID is trimmed when I read it from the text file, but somehow the Insert/Update step the first time it inserts it into the target Oracle table the field is padded with blanks to the length of the row. If I do the db join with WHERE TRIM(AT_TRANS_ID) = ? woks, otherwise not.

    But the data in the source file is correctly trimmed, so I think it's a problem in the insert/update step. Any idea on how to solve this problem ? Has anyone found somethhing similar before with Oracle ?

    Thank you for all the support.

    Cheers,
    Paolo

  18. #18

    Default

    LOL

    I've found out an old post maybe two years ago when I was asking exactly the same about fields that got trimmed on Oracle. But now this is blocking me. Has anyone found something similar to this on Oracle ?

    Matt, thank you for all the help. When you're around Torino in Italy tell me and I'll offer you that beer

    Hope I'll find some way to work around this...

  19. #19

    Default

    GOTCHA !!!!!!!!!!
    EUREKA !!!!!!!!!!!

    Kettle was showing me the text fields in Oracle as VARCHAR2, but then I had a peek with Tora and found out that the fields are really CHAR !!! So they get right padded with blanks automatically (as they should be since they are fixed length).

    I'll change the type of the fields in the table (I didn't create the table myself, so I never had doubts about what Kettle was showing me).

    So I think we may have a bug here the field is CHAR but Kettle says it's VARCHAR2. Do I need to file a bug ?

    Matt, you always save my day. Thank you again and see you somewhere in the future for that beer !

    Cheers,
    Paolo

  20. #20
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Kettle is not saying anything at all, it's just describing the field as String.
    The recommended data type for that is VARCHAR2 if you want to create the table based on that.

    The moral of the story is NOT TO USE CHAR data types!

    ;-)

    Matt

  21. #21

    Default

    Yes.

    CHAR is the ultimate evil.... now I know and I'll avoid it...

    Thanks again for all !



    Cheers,
    Paolo

  22. #22

    Default Try this one....

    Hi i am new to the kettle and even i got the same problem what you got but thing is use 2 insert/update trasf so that u will get the correct answer.one for the only new row insertion and the second one is only for the update.Try with this hope you will get correct answer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.