topic Pb with TOracleOutput : insert or update, update or insert, delete ... in Talend Studio

Pb with TOracleOutput : insert or update, update or insert, delete ...

Anonymous — Sat, 16 Nov 2024 14:32:38 GMT

Hello,
I've some difficulties with this component :
I'm working on TALEND 2.0.3 r3791, oracle V8.1.7.0 in a java project.
When I use :
update or insert : The job inserts ignoring records previously present in the table : results => dubling records
insert or update : Error : ORA-00921: unexpected end of SQL command
Delete : Error : ORA-00921: unexpected end of SQL command
Have you already meet this disfunctionment ?
Thanks for your help
Best regards
Gilles

Re: Pb with TOracleOutput : insert or update, update or insert, delete ...

Anonymous — Wed, 10 Oct 2007 06:59:00 GMT

Hi Gilles
It is a 1961. The new release TOS2.2GA have been released. Please download TOS2.2GA.
Best regards
shong

Re: Pb with TOracleOutput : insert or update, update or insert, delete ...

Anonymous — Wed, 10 Oct 2007 07:07:10 GMT

Hello Shong,
Thanks for your help
I 'll download version TOS2.2
Best regards
Gilles

Re: Pb with TOracleOutput : insert or update, update or insert, delete ...

Anonymous — Thu, 11 Oct 2007 14:14:05 GMT

Hello,
I've downloaded and installed TOS2.2
But, I've always problems with insert or update and update or insert
When It 's supposed to make an update , there's an Oracle ERROR...
Exception in component tOracleOutput_1
java.sql.SQLException: ORA-00001: unique constraint (EGOWRK.SYS_C0034914) violated
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:543)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2888)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:2960)
at testoracle7.maj_lib_comm.MAJ_LIB_COMM.tDBInput_1Process(MAJ_LIB_COMM.java:647)
at testoracle7.maj_lib_comm.MAJ_LIB_COMM.runJob(MAJ_LIB_COMM.java:1201)
at testoracle7.maj_lib_comm.MAJ_LIB_COMM.main(MAJ_LIB_COMM.java:1148)
So, when I choose clear data on table : It's OK but ALL MY DATA are deleted !!!
Thanks for your help
Best regards
Gilles

Re: Pb with TOracleOutput : insert or update, update or insert, delete ...

Anonymous — Fri, 12 Oct 2007 06:46:08 GMT

Hi Gilles
we need more info about what you are actually doing. Can you upload some screenshots about your job?
But the difference between update or insert and insert or update is the following :
update or insert : Will first try to update, if it can't (because record doesn't exist), it will try to insert.
insert or update : Will first try to insert, if records already exists (primary key already exists), it will try to update the record.
Best regards
shong

Re: Pb with TOracleOutput : insert or update, update or insert, delete ...

Anonymous — Fri, 12 Oct 2007 10:10:33 GMT

Hello Shong,
It's a very simple JOB
Select data in a data base and insert or imput in an other....
I've made 3 tests and the results are :
1.UPDATE or INSERT:
pstmtUpdate_tOracleOutput_1 = conn_tOracleOutput_1
.prepareStatement("UPDATE \"" + "STAT_LIB"+ "\" SET \"LIB\"=?,\"ORDRE\"=?,\"VAL1\"=?,\"VAL2\"=? WHERE \"COD\"=? AND \"TYP\"=?");
pstmtInsert_tOracleOutput_1 = conn_tOracleOutput_1
.prepareStatement("INSERT INTO \"" + "STAT_LIB"+ "\" (\"COD\",\"LIB\",\"TYP\",\"ORDRE\",\"VAL1\",\"VAL2\") VALUES (?,?,?,?,?,?)");

but in all the code, we find only
pstmtInsert_tOracleOutput_1 and pstmtUpdate_tOracleOutput_1 is'nt use !!!
2.INSERT or UPDATE :
pstmt_tOracleOutput_1 = conn_tOracleOutput_1
.prepareStatement("SELECT COUNT(1) FROM \"" + "STAT_LIB"
+ "\" WHERE \"COD\"=? AND \"TYP\"=?");
pstmtInsert_tOracleOutput_1 = conn_tOracleOutput_1
.prepareStatement("INSERT INTO \"" + "STAT_LIB"+ "\" (\"COD\",\"LIB\",\"TYP\",\"ORDRE\",\"VAL1\",\"VAL2\") VALUES (?,?,?,?,?,?)");
pstmtUpdate_tOracleOutput_1 = conn_tOracleOutput_1
.prepareStatement("UPDATE \"" + "STAT_LIB"+ "\" SET \"LIB\"=?,\"ORDRE\"=?,\"VAL1\"=?,\"VAL2\"=? WHERE \"COD\"=? AND \"TYP\"=?");

and same effect :
but in all the code, we find only
pstmtInsert_tOracleOutput_1 and pstmtUpdate_tOracleOutput_1 is'nt use !!!
3. UPDATE (only)
pstmt_tOracleOutput_1 = conn_tOracleOutput_1
.prepareStatement("UPDATE \""
+ "STAT_LIB"
+ "\" SET \"LIB\"=?,\"ORDRE\"=?,\"VAL1\"=?,\"VAL2\"=? WHERE \"COD\"=? AND \"TYP\"=?");

and this method is used

If you want, I could give you all the code
Best regards
Gilles

Re: Pb with TOracleOutput : insert or update, update or insert, delete ...

Anonymous — Mon, 15 Oct 2007 08:09:59 GMT

Hi Gilles

There is a bug in tOraclexxx component. Can you report a bug in our bugtracker?
Thanks for your support!
Best regards
shong