Table "DUAL" not found in Oracle mode.

[garretwilson]

I'm trying to do an upsert using MERGE in Oracle mode. My connection string is jdbc:h2:mem:spreadsheets;MODE=Oracle;DB_CLOSE_DELAY=-1.

Let's say that I'm creating a spreadsheet database, with multiple spreadsheets. The contents are stored in cells(id, row_index, col_index, content), where id is the id of the spreadsheet, row and col are the cell coordinates, and content is a string to put in the cell.

Each time we put something in a cell, the there may or may not already be a row for that cell. So I want to upsert using MERGE in Oracle mode. (For the moment I don't care about the race conditions inherent in MERGE.) Here is the SQL prepared statement using JDBC:

MERGE INTO cells USING DUAL ON (id = ? AND row_index = ? AND col_index = ?)
  WHEN NOT MATCHED THEN INSERT (id, row_index, col_index, content) VALUES (?, ?, ?, ?)
  WHEN MATCHED THEN UPDATE SET content = ?

It is my understanding that this is correct Oracle format, although I haven't actually tried it on Oracle yet. (I haven't got that far, because H2 doesn't like it.) H2 tells me:

Table "DUAL" not found

Is this an H2 bug? Shouldn't H2 support the Oracle DUAL table? Or is my syntax incorrect even for Oracle?

[katzyn]

This syntax is not valid in Oracle due to another problem. row cannot be used as an identifier without quotes in Oracle.

But Oracle accepts DUAL in this query. H2 does not accept it, this is a bug. Parsing of DUAL can be easily fixed, but such fix will uncover another problem with H2-specific SYSTEM_RANGE table with schema that should be fixed too to allow such usage of DUAL table that is converted internally to SYSTEM_RANGE. I'll take a look on it.

[garretwilson]

… row cannot be used as an identifier without quotes in Oracle.

Ah, because row is a reserved word. Thanks. I've updated the example to use row_index, which the actual code uses in real life anyway.

I'll take a look on it.

Thank you so much! I'm going to have to use an ugly DELETE + INSERT until this is fixed. Let me know if it takes longer than expected, so I can plan things on my end. Cheers!

[katzyn]

Your test case uncovered a lot of issues. They should be fixed now. You can download or clone with git the current sources and build database with the jar target:
http://www.h2database.com/html/build.html#building