Schema dropdown in SQL Commander window as empty by default for Oracle?

Is there any way that I can set the Schema drop-down to default to the empty setting when connecting to an Oracle Database? I see that I can set a connection hook to alter the session to set the current schema to a specific schema, but what would I need to do to have the drop-down default to empty? Thanks, Jason Edited by: Jason Brome on Oct 26, 2010 11:26 AM

Hi Jason, The schema drop-down is currently set to the default database initially. Please clarify why you want it to be set to "empty"? Best Regards, Hans

We have a number of tables in Oracle spread across multiple schemas with corresponding public synonyms. In order to get auto-completion within the SQL Commander editor for these public synonyms, I find myself always switching the schema drop-down from a specific schema to the blank/empty option. Thanks, Jason

Hi Jason, Thanks for the clarification. I have opened a ticket for this. In the meantime, please be aware that if you know that the table belongs to a specific schema other than the schema selected in the list, you can type the schema name and then invoke auto completion to see tables from that schema. I realize this requires more typing than what you suggest, but it may be a workable solution until we implement your suggestion. Best Regards, Hans

Hi DB Visualizer team, I was wondering if there is any update on the ticket that was opened for this issue? Thanks, Jason

I would like a similar option. I also use Oracle, and I often switch back and forth between multiple connections and the schema is always reset to the default. I then have to either keep switch back to whichever schema I was using or keep typing the schema name over and over in the queries. Perhaps you could provide three options: 1. Use default schema 2. Use blank schema 3. Use previously-used schema Thanks! -- Jason Boyer

Hi guys, There has been no progress on this ticket, partly because there has not been a lot of requests for it. I have now added your two votes. But I wonder if this is still important now when SQL Commander tabs can be saved between sessions? You can configure an SQL Commander tab (or as many tabs as you need) with the Schema setting you want, and when you restart DbVisualizer, they come up with the same settings again. You can even rename them so that the schema name is visible in the tab header to make it easy to find the right one. Please let us know what you think. Best Regards, Hans

Hans, Thanks for looking into this. I appreciate the tab feature and I use it a lot! (I currently have 6 SQL Commander tabs open over 2 different connections) One use case that I run across a lot is that I will switch to a schema one on connection and run some non-query statements, and verify that those changes worked. Then I'll select the other connection and re-run the same statements on that database. However, this requires the extra step of re-selecting the schema I was in before switching connections. Even with queries, I might run a PL/SQL function on one connection, pin the results tab, then switch my connection and run the function again on the other database to compare the output. I sometimes have a very brief moment of confusion when Oracle complains that the function doesn't exist, only because I forgot to switch the schema back. Thanks! -- Jason Boyer

Hi Jason, Sorry for not replying earlier. We will take a fresh look at this and see if we can get a solution into a maintenance release. Best Regards, Hans

Hi Jason, I'm looking into this now, and noticed that it seems like you are asking for the selected schema to be kept even when you switch between connections, not just when opening new SQL Commander tabs for the same connection. I appreciate if you can confirm this. Best Regards, Hans

Hi Hans, Yes, I want the schema to remain when I use the drop-down to pick a different connection, then re-select the first connection, all in the same tab. Thanks! -- Jason Edited by: Jason Boyer on Oct 3, 2013 8:42 PM Edited by: Jason Boyer on Oct 3, 2013 8:42 PM

Hi Jason, Thanks, but I'm still not 100% clear. Let's try an example. Say that Connection A has Schema A as the default. You open an SQL Commander tab for this connection and change the Schema value to Schema B. Now you change the Database to Connection B, which has Schema C as the default. Are you saying you want the Schema to remain as Schema B in this case (the value used for the other connection), or should it be set to the default for this connection, i.e. Schema C? Finally you switch back to Connection A. Are you saying you want Schema to now be set to the previously used schema for this connection, i.e. Schema B? This is kind of tricky, so I appreciate your help clarifying the requirements. Before I understand completely, I cannot tell you if it's feasible to implement. Regardless of the answers above, you can do this today: 1) Open one SQL Commander for Connection A and one for Connection B and set the Schema to what you want in each. 2) Run some SQL with Connection A. 3) To run the same SQL for Connection B in the other SQL Commander tab, enable Sticky Database for this tab and then click Previous SQL in the toolbar. This loads the SQL you just executed for Connection A but, because of the Sticky setting, all the drop down lists keep their values, including Schema. Is that a workable solution for you? Adding options for the initial value for the Schema for the same connection (e.g., None, Default or Previous as you suggested) looks to be possible at first glance. It is just when two different connection are involved that things gets tricky, so that's why I'm trying to figure out what you need and if it can only be done some other way. Best Regards, Hans

Hi Hans Thanks for the suggestion about Sticky Database. I was not aware of that. Use the example you have described, I understand that it would be infeasible to attempt to use Schema B as the initial schema for Connection B (who knows if that connection even has such a schema?). So, Schema B using its own default is fine. The main thing is that if I switch back to Connection A, then the schema should switch back to Schema B. If I switch to Connection B again, it should go back to the last-used-schema for that connection, which is this case is Schema C. I hope this helps clarify things. Thank you for listening and for all your hard work on this great product! -- Jason

Hi Jason, Thanks for clarifying. Then I believe I understand what you want. As I said earlier, this is a tricky area in general and we also need to consider how to deal with databases that do not have a schema concept (e.g. MySQL) or that has both a database and a schema concept (e.g. SQL Server), and how this would be handled for those. I would appreciate if you can try out the approach with two SQL Commanders and with Sticky enabled for one and see how that works for you before we do anything along the lines of keeping track of "previous". Yet another alternative would be to use a Connection Hook to set the default schema for each connections using ALTER SESSION. You could then use just one SQL Commander tab and switch between the connections and always get the schema that you want for each. Please have a look at these options in more detail and let us know how it goes. Best Regards, Hans