slnowak slnowak - 4 months ago 18
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 foo.id IN (ids)


Now,
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 foo.id IN (chunk1) or foo.id 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 foo.id = tempids.id
    .



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:

INSERT ALL
INTO some_table VALUES ('foo')
INTO some_table VALUES ('foo1')
INTO some_table VALUES ('foo2')
....
INTO some_table VALUES ('foo12345')
SELECT * FROM DUAL


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

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

where (foo.id, 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.

Comments