Sequence number or nextval in an ELT Oracle output component

One Star

Sequence number or nextval in an ELT Oracle output component

Hi,
I was trying to rewrite a simple UPDATE-INSERT (MERGE) workflow using the ELT components that was otherwise working fine in the ETL approach. The problem I am facing is with the tELTOracleOutput where I need to provide a seq number for the key field. We have a trigger that will auto insert it in case it is not provided in the sql input. It worked fine with the key field removed from the output of tMap component in ETL model. But the same is not working in tELTOracleMap as it was complaining that the tELTOracleOutput does not match with its schema. So I am forced to include the key field in the output and need to provide a value for it which is a seq number. When I supplied the seq number I am getting an error saying
Exception in component tELTOracleOutput_2
java.sql.SQLSyntaxErrorException: ORA-02287: sequence number not allowed here
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at
SQL generated by tELTOracleMap is
SELECT BUSER_SEQ.NEXTVAL, SUSER.O_AUDIT_KEY , DIM_AUDIT.DIM_AUDIT_KEY , SUSER.WEB_USER_ID , SUSER.FIRST_NAME , SUSER.LAST_NAME , SUSER.EMAIL_ADDRE, SUSER.INACTIVATED , SUSER.DELETED , SUSER.ADMINISTRATOR FROM SUSER , DIM_AUDIT WHERE DIM_AUDIT.STATUS = 'INPROCESS'

The above sql is working fine when executed from command line or SQL PLUS.
One Star

Re: Sequence number or nextval in an ELT Oracle output component

Hi
The sql above is not the entire sql in this Talend Job.
It is as below.
String insertQuery = "INSERT INTO "
+ tableName_tELTOracleOutput_1 + "(your columns) ("
+ select_query + ")";

select_query is the sql generated by tELTOracleMap.
When you get nextval from another table, this Oracle Error occurs.
In fact, it's a bug. Pleae report it on BugTracker.
Or try to use the normal Oracle components.
Regards,
Pedro