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.)
Sampler and looping - chunking up inputs to SQL Executor
+8- Contributor
Best answer by david_r
Indeed, Oracle has a max length for SQL statements,
You can e.g. use the Grouper from
- Best Answer
Indeed, Oracle has a max length for SQL statements,
You can e.g. use the Grouper from
+8- Contributor
Indeed, Oracle has a max length for SQL statements,
You can e.g. use the Grouper from
Ah! A simple and elegant solution. Many thanks. 🙏
Reply
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.