H2 Console connecting to Oracle DB will not show the list of tables

[yonatang]

When using H2 console to connect to an Oracle DB (using an Oracle connection string and Oracle JDBC), it will try to fetch the list of tables/views using MySQL syntax and fail.

The tables.do action will return the following exception:

ORA-00942: table or view does not exist
42000/942
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1230)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:511)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:162)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:1009)
    at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1270)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1148)
    at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1660)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1469)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3760)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3935)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1101)
    at org.h2.server.web.WebApp.addTableOrView(WebApp.java:740)
    at org.h2.server.web.WebApp.addTablesAndViews(WebApp.java:700)
    at org.h2.server.web.WebApp.tables(WebApp.java:789)
    at org.h2.server.web.WebApp.process(WebApp.java:247)
    at org.h2.server.web.WebApp.processRequest(WebApp.java:176)
    at org.h2.server.web.WebThread.process(WebThread.java:152)
    at org.h2.server.web.WebThread.run(WebThread.java:101)
    at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: Error : 942, Position : 47, Sql = SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = :1  AND TABLE_NAME = :2 , OriginalSql = SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?, Error Msg = ORA-00942: table or view does not exist

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:636)
    ... 22 more

The table INFORMATION_SCHEMA.VIEWS is an MySQL table, where it should use Oracle's ALL_VIEWS .

Happened with latest stable version (2.1.214)

[katzyn]

This is not a MySQL table, this is a table defined in the SQL Standard, part 11, Information and Definition Schemas (SQL/Schemata). Unfortunately, Oracle doesn't implement it, unlike many other database systems.

It also looks like JDBC driver of Oracle doesn't throw an exception when incorrect SQL is passed during construction of a PreparedStatement, but H2 Console relies on that exception. H2 Console needs to be improved to ignore exceptions during actual query execution too.

where it should use Oracle's ALL_VIEWS

H2 Console is not designed to support any other database system perfectly and it isn't tested with them at all. To support this functionality, H2 needs to detect version of Oracle and use different queries for different versions. This is too complicated, you need to use some specialized tool tested with them all. But H2 Console can skip this feature and load views without showing their original SQL.