I am running a call to an Oracle DB based upon an input ID as a feature attribute value coming in from a CSV. There are upwards of 2000 ID’s to process. Initially, that call was running on a per-feature basis; I am now trying to make this more performant by using the IN statement e.g. WHERE ID IN (‘1’, ‘2’, ‘3’). I am doing this by aggregating incoming ID’s into one attribute value.
Where this falls flat is that there appears to be a limit on items that Oracle can cope with in an ‘IN’ statement, so I figured I would chunk it up into more manageable numbers.
My first thought was to create a looping transformer. My conception of this would be that a Counter would increment a Sampler to take the first 500 records (X), then the X+1 group of 500 (e.g. values 501 to 1001), X=2 and so on, until the _matched_records result coming out of SQL Executor was 0, at which point a tester in line would fail.
However, Sampler doesn’t support that kind of expression building (as far as I can see). Any ideas? I’m wondering about lists.
(Googling suggests reading ID’s from a prepared table in Oracle, but that isn’t an option.)
Solved
Sampler and looping - chunking up inputs to SQL Executor
+9- Supporter
Best answer by david_r
Indeed, Oracle has a max length for SQL statements,
You can e.g. use the Grouper from
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
- Best Answer
Indeed, Oracle has a max length for SQL statements,
You can e.g. use the Grouper from
