is there some HSQL DB property which would say how much items can be in the list used in IN clause? Oracle limits it to 1000 items, when I have more elements, I split the list by 1000 and execute more queries, but I'd need the HSQL database to simulate this scenario (I am writing an automated test and I'd like it to fail when someone removes this list splitting mechanism in the future)
No such limit can be set in HSQLDB. You should be able to check for the limit with a stored procedure in Oracle and in HSQLDB, so it is not affected by others modifying the application code.