I am creating a process which retrieves Organisation records from a database.
One of the WHERE clauses includes a list of Organisation IDs:
fr.business_org_fk IN (123,345,567,890)
Is there a way to make all of the values within an IN clause a single parameter?
fr.business_org_fk IN (?)
I have tried passing the 123,345,567,890 as string to this parameter (also a string type) but I am getting the following error:
Errors occurred while building or executing SQL statement: ORA-01722: invalid number ; Caused by: ORA-01722: invalid number
If I pass a single ID into the IN clause - that works just fine, and only fails when there are multiple IDs delimited by a comma.
If there were just a handful of IDs I would set them up as a parameter each, but there about 100 of them and they are constantly changing, so I need this as a process property that can be maintained in a sustainable manner.