Very new to Ignition and my current admin role in general. I have successfully connected to a couple of our company databases and am able to view all of the schemas in the Query Browser. I recently connected to one of our older Oracle databases and the schemas do not show up in the Query Browser. I am unfamiliar with all of the data we are collecting in that database and being able to browse through it is a must.
You could try the following. Go to your Gateway, then Config>Databases>Connections, regardless of the version.
Edit the connection, and scroll all the way down and check Show advanced properties, and under SQL Compatibility enable the Include Schema in Table Name.
In my case not all tables were listed, or displayed an error when I tried to see their columns using Query Browser, and enabling that solved my issue on MSSQL.
1 Like
Include Schema in Table Name seems to have no effect. I have tried to use different Translations as well. I cannot confirm which version of Oracle so I have not yet tried to update the JDBC driver.
Could it be a matter of granting access to the desired schemas, then?
I have been told that the login info we are using has been granted the correct permissions. I am unable to verify personally, I do not have access. I was just given the db login info. Thank you for the advice though, I will find a way to verify this.
1 Like
Same issue here. Were you able to found a solution?
Also experiencing this issue in 8.0.17. Anything unique with Oracle DBs? I haven’t noticed displaying tables to be a problem with read-only permissions.
Hi All, Did any of you find the solution or any way how we can see schema in Ignition.
We have the same issue here with an Oracle 12.2 database. I am using the same account credentials from Oracle Sql Developer with the search_path property and the connection and filtering works. I have tried jdbc connectors 8, 10, and 11 for Ignition 8.1.7 with no success. I have tried search_path and currentSchema independently as both extra connection properties and url parameters with no success.
Is there a way to show the available tables and views in the database query browser for an Oracle database when the login used is not the owner?
Best ive found is this article:
Hi all, I've been working with a customer regarding this issue, and made some discovery. I thought I'd share.
Essentially, prior to 7.9.1, Ignition would gather all DB tables from Oracle. However, this would cause significant performance issues on the GW and Designer when opening the DB Query Browser, because Oracle would return thousands of system tables. This was considered a bug, and was patched in 7.9.1. Now, Ignition will only return tables that are owned by the user defined in your DB connection. So, if you want to see tables in the DB Query Browser, the tables need to be owned by your defined user.
During my troubleshooting with the customer, I figured that customers should have the option to show all tables from all users if they wish.
I opened feature request IGN-7306, which will allow y'all to display all Oracle DB tables in the DB Query Browser if you would like. I do not have an estimate on when (or even if) the feature will be implemented.
Hopefully I remember to return to this forum post when the feature is implemented. In the meantime, hang tight!
Has anybody tried to use ORACLE objects that belong to others in their queries?
In the article, there was an example "SELECT * FROM mary.Employee" when I'm user "bob"
In my example, I'm "Ignition_RO", and I want to use tables owned by "QUALITY".
So the query I want to run is "SELECT * FROM quality.employee"
This workw fine in OracleSQLDesigner.
But ... in the NamedQuery and the DatabaseQueryBrowser tool, running that same SQL Statement returns 00942 - "table or view does not exist".
For some queries that work fine in OracleSQLDesigner, when I run those same queries as NamedQueries or in the DatabaseQueryBrowser too, I will get error 01031 - "insufficient privileges"
How is SQLDesigner different from NamedQuery in terms of how it is passing credentials?
SQLDesigner is making a native Oracle connection. Ignition makes a JDBC connection through the Oracle-supplied driver. Most DB brands have vendor-specific behavior on their own native connections. JDBC is standardized. You should examine the Oracle JDBC documentation to look for possible explanations.
Ok ... my first worry was just syntax.
Since it seems like the syntax of using another schema-user is allowed and parsed properly, I'm guessing that it's going to be a permissions problem ... just not sure what the question is that I'll ask my DBA team ... hopefully they'll know the diff between JDBC and Native connections.
Thanks for the encouragement.
JDBC drivers usually allow a large variety of permissions/constraints to be specified in the JDBC URL and/or its "Extra Connection Properties". Typically brand-specific. You should look at your gateway's connection settings for this DB name.