Improve Oracle support

[miniwark]

I can see than Oracle support is already almost here.

I have been able to add an Oracle database as a source by installing the Oracle Instant Client, the node oracledb module and by uncomment the Oracle line in /public/partials/data-source/edit.html.

Unfortunately the layer functionally work only partially as only the first tables from the main schema is available. Only the first 100 available tables displayed (i assume than there is a LIMIT somewhere in the code but id do not know where).

Is it possible and/or planed to finish the Oracle support ?

[miniwark]

Hi,

i have investigated a bit more about this issue.

1. The limit to 100 first results is because of node-oracledb default behaviour with the maxRows parameter. See here. For now i will just bypass this by manually setting oracledb.maxRows in/server/core/db/oracle.js.

2. The recommended way to overturn this limit is apparently to set maxRows or to use resultSet in the connection.execute() method. Setting maxRows from the database Data Packet Size limit may be the easy way here.

[miniwark]

Also... /server/core/db/oracle.js use the query SELECT tablespace_name as table_schema, table_name as name FROM user_tables to get the list of table than the user own. Could it be instead SELECT tablespace_name as table_schema, table_name as name FROM all_tables to have all the table than the user as the right to read (and then add the table shame name in the result list.

This because, the database admin can tailor a limited user, with just read right on a list of tables from other users (or shemas).

[miniwark]

After testing, all_tables may be a bit inconvenient because it also list many tables form the system schemas. It's possible to tailor the query to exclude them, but using two different data sources from the same database with different users may be a way to do this (but #24).

Also dba_tables is better than user_tables & all_tables because views are also listed (but with the same problem than all_tables regarding system tables).

[beevou]

We are adding oracle support via JDBC in the next release