Shankha, PutSQL works by creating batches using a PreparedStatement, then executing those batches using the incoming flow files (up to the value of the Batch Size parameter). In order to support that, auto-commit has to be false or else each statement would have to be executed individually, causing a performance problem. If your Oracle DB or driver does not support setting auto-commit to false, then PutSQL will not work. However if you are not using the sql.args.N.type/value attributes to set the types/values of your columns, meaning you have an explicit SQL statement in your flow file, you can try ExecuteSQL instead. Prior to the (upcoming) release of NiFi 1.5.0, you should be able to use ExecuteSQL as if it were PutSQL, and you should get empty flow files out (for things like INSERTs that don't return a result set). As of NiFi 1.5.0, I'm not sure you'll get flow files out using this technique, due to support for multiple result sets (NIFI-3432 [1]), but it should still execute the SQL statements, you may just not get any output from that processor.
A possible "improvement" might be to have a different code path if only one flow file is retrieved from the session; that would not require disabling auto-commit but would suffer from the performance hit I mentioned before. Having said that, it seems like too much added complexity to get around this particular issue, it's the first I've seen of it, but if it is a common issue with various DBs then we can take a look. Regards, Matt [1] https://issues.apache.org/jira/browse/NIFI-3432 On Tue, Oct 17, 2017 at 5:53 AM, shankhamajumdar <[email protected]> wrote: > Hi, > > I am trying to insert some data in oracle table using PutSQL Processor. But > getting the below error. > > 2017-10-17 14:53:58,409 ERROR [Timer-Driven Process Thread-1] > o.apache.nifi.processors.standard.PutSQL > PutSQL[id=24b8613d-015f-1000-2f70-88d0c843300b] > org.apache.nifi.processors.standard.PutSQL$$Lambda$209/1062828301@1098de1 > failed to process due to > org.apache.nifi.processor.exception.ProcessException: Failed to disable auto > commit due to java.sql.SQLFeatureNotSupportedException: Disabling > auto-commit mode not supported; rolling back session: {} > org.apache.nifi.processor.exception.ProcessException: Failed to disable auto > commit due to java.sql.SQLFeatureNotSupportedException: Disabling > auto-commit mode not supported > > Regards, > Shankha > > > > > -- > Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/
