We are trying to insert records into Oracle table using PutSQL and attribute Obtain Generated Keys is set to true. NiFi DBCPConnectionPool controller service is configured to use Oracle 12c and JDBC is ojdbc8.jar.
The expected value for attribute sql.generated.key should be in number but getting rowid like AAJV6hAAAAAB/qFAAA.
So do we need to do any config settings at Oracle end to return the generated sequence id instead of rowid?
Please note that the same is working as expected for PostgreSQL!
As per this,
the Oracle JDBC driver will not return the value of the id column, but instead it will return the ROW_ID (a pseudo column that identifies a specific row), to allow you to retrieve the value yourself.
Historically the Oracle driver did it this way, because previous Oracle versions didn't have identity columns.
So I have to add a processor to get the generated sequence ID from rowid.