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!