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.
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