slnowak slnowak - 1 year ago 69
SQL Question

In-memory database supporting proprietary INSERT ALL oracle syntax

I need to write a simple query using oracle, java and mybatis:

select * from FOO foo where IN (ids)

is a large collection of strings, about 7000. Unfortunately, oracle has 1000 elements limit for IN clause.

To overcome this, I can either:

  1. concatenate the query dynamically so it becomes:
    select * from FOO foo where IN (chunk1) or IN (chunk2) ...

    I'm not sure if it even works and I really doubt it performs well

  2. use temporary table and rewrite the query to:
    select * from FOO foo join SOME_TEMPORARY_ID tempids on =

I've decided to go for 2 option. Before performing the query I need to somehow do an efficient batch insert to Oracle. Unfortunately, Oracle has proprietary syntax to do batch inserts:

INTO some_table VALUES ('foo')
INTO some_table VALUES ('foo1')
INTO some_table VALUES ('foo2')
INTO some_table VALUES ('foo12345')

Now, I didn't mention but I want to write an integration test for this, ideally using H2 or any other inmemory database.
Of course H2 doesn't support that syntax. Neither does HSQLDB.

Do you know any in-memory database that fully supports proprietary oracle syntax? Or at least this specific one INSERT ALL clause?

Answer Source

Thanks that you describe your primary problem. Check, may be this can help you

where (, 0) in (('1', 0), ('2', 0),...)

This simple workaround hasn't limitation. If this answer wouldn't be appropriate then let me know. I delete this answer and think again at your child problem.