Query tab not available after connecting to Oracle

[bkief]

After connecting to a oracle db (screen grays, blue button says connected), I am unable to select the QUERY tab, and clicking it socialites no screen changes. It does not appear to register as a link because the mouse remains in its pointing state when hovering. Logs do not reveal anything notable. Postgres and sqllite connects work fine.

{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"CORS_ALLOWED_ORIGINS: [\"https://plot.ly\",\"https://stage.plot.ly\",\"https://local.plot.ly\",\"http://localhost:9494\"]","time":"2018-08-23T15:55:39.908Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Listening at: http://localhost:9494","time":"2018-08-23T15:55:39.909Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Loading persistent queries","time":"2018-08-23T15:55:39.915Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Electron version: 2.0.4","time":"2018-08-23T15:55:39.915Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Chrome version: 61.0.3163.100","time":"2018-08-23T15:55:39.915Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Request: /connections","time":"2018-08-23T15:55:42.434Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Response: /connections","time":"2018-08-23T15:55:42.437Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Request: /settings","time":"2018-08-23T15:55:42.441Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Response: /settings","time":"2018-08-23T15:55:42.442Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Request: /connections","time":"2018-08-23T15:56:43.477Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Response: /connections","time":"2018-08-23T15:56:43.480Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Request: /connections/oracle-92ca465e-956d-444d-b639-fdaa5b06f33e/connect","time":"2018-08-23T15:56:46.518Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Response: /connections/oracle-92ca465e-956d-444d-b639-fdaa5b06f33e/connect","time":"2018-08-23T15:56:46.520Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Request: /connections/oracle-92ca465e-956d-444d-b639-fdaa5b06f33e/sql-tables","time":"2018-08-23T15:56:46.528Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Response: /connections/oracle-92ca465e-956d-444d-b639-fdaa5b06f33e/sql-tables","time":"2018-08-23T15:56:46.530Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Request: /queries","time":"2018-08-23T15:56:46.533Z","v":0}
{"name":"plotly-database-connector-logger","hostname":"USER","pid":31384,"level":30,"msg":"Response: /queries","time":"2018-08-23T15:56:46.535Z","v":0}

[n-riesco]

I don't see any errors on the log. One reason to be unable to open the Query panel (despite a successful connection) can be that the user doesn't have access to any tables in the database.

[n-riesco]

Re #465

[bkief]

I can use the database just fine with Sql Developer. It is a large database, serveral TBs, not sure if that has anything to do with it. Does Falcon iterate through all of the schema to find tables?

[n-riesco]

Falcon runs the following query to get a list of tables:

        SELECT * FROM user_all_tables
        WHERE
           table_name NOT LIKE '%$%' AND
           (table_type IS NULL OR table_type <> 'XMLTYPE') AND
           (num_rows IS NULL OR num_rows > 0) AND
           secondary = 'N'

Do you get a list of tables when you run the above query in SQL Developer?

It also runs a query to get a list of columns and data types (but, if I remember correctly, only after opening the query panel):

        SELECT
            c.table_name,
            c.column_name,
            c.data_type
        FROM
            user_tab_columns c,
            user_all_tables t
        WHERE
           c.table_name = t.table_name AND
           t.table_name NOT LIKE '%$%' AND
           (t.table_type IS NULL OR t.table_type <> 'XMLTYPE') AND
           (t.num_rows IS NULL OR t.num_rows > 0) AND
           t.secondary = 'N'

[bkief]

Okay, this is starting to make sense. My database user has no tables, but I have grants to various schemas within the database that I write my queries against. Could something like the following be used instead?

SELECT * FROM all_all_tables
        WHERE owner not in ('SYS', 'SYSTEM') and
           table_name NOT LIKE '%$%' AND
           (table_type IS NULL OR table_type <> 'XMLTYPE') AND
           (num_rows IS NULL OR num_rows > 0) AND
           secondary = 'N'

[bkief]

Oracle docs for reference

[n-riesco]

I can't test this today, But I'll come back to you as soon as I do.

[bkief]

I forked, tried the above, and did a build. It appeared to have solved the issue. Shall I do a PR?

[n-riesco]

I've tested the change you've proposed and it works for me too. But I'd also like to exclude tables owned by MDSYS. Otherwise the database overview is crowded with system tables.

Something like this works for me:

SELECT table_name FROM all_all_tables
WHERE
    owner not in ('SYS', 'SYSTEM', 'MDSYS') and
    table_name NOT LIKE '%$%' AND
    (table_type IS NULL OR table_type <> 'XMLTYPE') AND
    (num_rows IS NULL OR num_rows > 0) AND
    secondary = 'N'

And for the function schemas:

        SELECT
            c.table_name,
            c.column_name,
            c.data_type
        FROM
            all_tab_columns c,
            all_all_tables t
        WHERE
           t.owner not in ('SYS', 'SYSTEM', 'MDSYS') and
           c.table_name = t.table_name AND
           t.table_name NOT LIKE '%$%' AND
           (t.table_type IS NULL OR t.table_type <> 'XMLTYPE') AND
           (t.num_rows IS NULL OR t.num_rows > 0) AND
           t.secondary = 'N'

I forked, tried the above, and did a build. It appeared to have solved the issue. Shall I do a PR?

That'd be great! Thanks.

[bkief]

PR #518 - I also had to concat the schema to the table name, otherwise not all queries work work. If you wouldn't mind editing the PR to also include MDSYS, I missed that inclusion.

[n-riesco]

Closed via #518