Query tab not available after connecting to Oracle #516

Activity

1. n-riesco commented on Aug 23, 2018

   

   n-riesco

   on Aug 23, 2018

   Contributor

   More actions

   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.
2. n-riesco commented on Aug 23, 2018

   

   n-riesco

   on Aug 23, 2018

   Contributor

   More actions

   Re #465
3. bkief commented on Aug 23, 2018

   

   bkief

   on Aug 23, 2018

   ContributorAuthor

   More actions

   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?
4. n-riesco commented on Aug 23, 2018

   

   n-riesco

   on Aug 23, 2018

   Contributor

   More actions

   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'
5. bkief commented on Aug 23, 2018

   

   bkief

   on Aug 23, 2018

   Last edited by bkief

   ContributorAuthor

   More actions

   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'
6. bkief commented on Aug 23, 2018

   

   bkief

   on Aug 23, 2018

   ContributorAuthor

   More actions

   Oracle docs for reference
7. n-riesco commented on Aug 23, 2018

   

   n-riesco

   on Aug 23, 2018

   Contributor

   More actions

   I can't test this today, But I'll come back to you as soon as I do.
8. bkief commented on Aug 23, 2018

   

   bkief

   on Aug 23, 2018

   ContributorAuthor

   More actions

   I forked, tried the above, and did a build. It appeared to have solved the issue. Shall I do a PR?
9. n-riesco commented on Aug 24, 2018

   

   n-riesco

   on Aug 24, 2018

   Contributor

   More actions

   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.
10. bkief commented on Aug 27, 2018

    

    bkief

    on Aug 27, 2018

    Last edited by bkief

    ContributorAuthor

    More actions

    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.
11. n-riesco

    added a commit that references this issue on Aug 29, 2018

    oracle: show tables from other schemas (#518)

    ...

    This issue will close once commit 4a2582c is merged into the 'master' branch.

    4a2582c
12. n-riesco commented on Aug 29, 2018

    

    n-riesco

    on Aug 29, 2018

    Contributor

    More actions

    Closed via #518
13. n-riesco

    closed this as completedon Aug 29, 2018