Using Oracle ROWID as an Update key

[Bill_G]

We are trying to use the Oracle ROWID pseudo column as the update key in an Oracle Enterprise Stage. The job does a lookup on the table which returns the ROWID of the matching record (if one exists).

We added ROWID to the column list of the Oracle stage, which is set as an Upsert with an Auto-Generated Update. ROWID is the only key column.

Code: Select all

Update (abridged):
T_EARNINGS_SNAPSHOT_update: Update prepare failed:
update is: UPDATE T_1 SET F_1 = :F_1, ... , WHERE (ROWID = :ROWID)

ORA-01745: invalid host/bind variable name

It seems as thought ROWID is an invalid bind variable name. We have change the name of the column linked to ROWID in the preceding Transformer, but no luck.

Is there a workaround to enable using ROWID as the update key in Oracle?

Thanks

[kirankota79]

use like this


(u can use any name u want)RROWID = :ROWID. Since ROWID is a key word in oracle you cannot use the sameone in the job. Change it to something in ur job and it will work.

[Bill_G]

use like this


(u can use any name u want)RROWID = :ROWID. Since ROWID is a key word in oracle you cannot use the sameone in the job. Change it to something in ur job and it will work.

Close, but what actually worked was changing the update to Manual SQL and "tricking" the SQL:

Code: Select all

WHERE (ROWID = ORCHESTRATE.IN_ROWID)

The column is named IN_ROWID, however I manually changed the SQL of the WHERE clause, using ROWID on the left side.

[MaxRevan]

I hope to be helpful.
I retrieved ROWID to be used for the update by converting it using the Oracle function RowIDToChar(ROWID) assigning it to the metadata column called ID_ROW.
In the updating OCI, my custom statement is

UPDATE <table_name>
SET <one or more fields>
WHERE ROWID = CharToRowID(ORCHESTRATE.ID_ROW)

[ray.wurlod]

use like this


(u can use any name u want)RROWID = :ROWID. Since ROWID is a key word in oracle you cannot use the sameone in the job. Change it to something in ur job and it will work.

U did not ask the question. U is one of our posters.

The second person personal pronoun in English is spelled "you", not "u" or, in its possessive form, "your", not "ur". Please strive for a professional standard of written English on DSXchange, because even good English is difficult enough for non-native speakers. Further, DSXchange is not a mobile-telephony device - there is no need for SMS-style abbreviations.