I'm hoping someone might have a suggestion here. A PutDatabaseRecord processor which was working properly under 1.15 started generating java.sql.BatchUpdateException: ORA-00936: missing expression on a 1.18 cluster.
I'm using the Oracle12+ database type and the incoming flowfile format is Avro - so there should be no Schema inference happening.
It's strange, since that error usually indicates a structural problem with the SQL itself. So, I'm concerned that there is a defect in the 1.18 version of PutDatabaseRecord. But, maybe there is something I can change about the source data or in the properties of the processor which will workaround this issue?
If anyone has seen something like this or has some suggestions I could try, I would appreciate it. Thanks.
Can you post full error, logs, the sql, table structure. What version of Oracle?
Can you post your full NiFi settings. is it with all data, perhaps some nulls causing an issue.
what is the settings for the Avro reader?
It is with all data attempted. It's a dynamic process, where the flowfile contents are mapped by an earlier QueryRecord processor to match the column names of the underlying table. I have no access to the SQL, which is part of the problem, and this same process IS working against Postgres with no difference in the Avro file contents or destination table structure. The postgres processor is configured the same with the exception of the Database type and DBCP controller service.
And the error implies that the SQL is being generated incorrectly - feels like something is being left out somewhere. Unfortunately, there does not seem to be a way to see the SQL NiFi is generating before sending to Oracle.
I think the Oracle version on the other end is 19 - I know the JDBC driver is on the NiFi side. But that also has not changed, and this same processor is still working on our 1.15 instance.
(I went to check the AVRO reader config - not much to it. It is just using the one embedded in the AVRO content.)
And - for as much additional information as it provides (which isn't much), this is the complete bulletin. I do not have access to see if there was additional logging.
PutDatabaseRecord[id=8e509290-6652-385d-83e1-8e4788ae4086] Failed to put Records to database for FlowFile[filename=file_crmanalyticsam_useram_userpvo-batch792591341-20230907_230017.csv]. Routing to failure.: java.sql.BatchUpdateException: ORA-00936: missing expression
Can you upgrade to NiFi 1.23.2? If not, I would suggest opening a ticket with Cloudera. This maybe a bug. I would like to see full nifi logs though and which JDK you are.
what language set?
Did the source system change? https://support.oracle.com/knowledge/Oracle%20Cloud/2925617_1.html
nothing in provenance, bulletins or nifi logs/?
turn on debugging
perhaps a dump of the data, maybe some strange characters in there.
@kellerj, Has the reply helped resolve your issue? If so, can you please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future? If you are still experiencing the issue, can you provide the information @TimothySpann has requested?