topic Re: Update Oracle table with Rownum in Talend Studio

Update Oracle table with Rownum

Anonymous — Sat, 16 Nov 2024 04:49:20 GMT

How to update oracle table with rownum. I am rownum as key to update a record. But i am getting below error.

Error message: ORA-00904: "rownum": invalid identifier.

Re: Update Oracle table with Rownum

nivedhitha — Thu, 29 Aug 2019 20:10:14 GMT

@senthilswing ,

can you post screenshots of your job?

Re: Update Oracle table with Rownum

Anonymous — Thu, 29 Aug 2019 20:17:32 GMT

This the select query in tOracleinput

select rownum,col1 from table

Updating col1 with some value by using rownum as key in tOracleoutput. I am getting error.

Re: Update Oracle table with Rownum

Anonymous — Thu, 29 Aug 2019 20:20:35 GMT

rownum is a pseudocolumn which indicates the row number in oracle. I am using rownum as a key to update a column since my table doesn't have any unique value.

Is it possible to use rownum in tOracleoutput. But we can update the logic in Sql developer editor.

Re: Update Oracle table with Rownum

Ganshyam — Fri, 30 Aug 2019 12:11:48 GMT

Hello,

We can create sequence using tmap and update the records using this sequence. As the rownum will generate the unique id against the record fetched by the SQL query.

Hope this solves your problem.

Regards

Ganshyam Patel

Re: Update Oracle table with Rownum

rmartin2 — Fri, 30 Aug 2019 16:04:18 GMT

As said before, "rownum" is not a sequence generated. It's a variable created "on the spot" to guide you to locate your data (kind of).

You can use this variable to generate a sequence of your own, but it will reflect the SELECT statement.

So if you sort in different manner it will not match.

As suggested, if you have no way of having a primary key, you'll have to manage a sequence. You can index it, and it's then very easy to manage, update or insert thanks to triggers !

Just put a trigger "BEFORE INSERT" (or AFTER) and add the id from the nextvalue of the sequence !