I use the Master Detail Browser for many situations, but it's not very useful in multi schema environments, as it saves the owner. Each of our many customers are contractually given their own schema, so a saved Master Detail Browser set will only work on one of those, which makes them cumbersome to manage. Could an option be added so as NOT to save the schema name as part of the queries, so it can be used across multiple schemas in a schema agnostic way?
Also, could there be an option to save "*" rather than column names, so that schema evolution (adding / removing columns) doesn't break the Master Detail Browser sets?
You probably know this, but in the meantime...those .md files can be edited with notepad. Might be easier to swap a schema by a global search and replace in there.
The only problem with * that I can think if is with XMLTYPE (if you have prior to 12cR2 client) or JSON. We use special handling for those kinds of columns. But maybe we could handle new/dropped columns automatically when the MD file is loaded.
Hi John.
Yes, I do edit / search replace at the moment, but when you have over 80 schemas and you need to check stuff with a nice Master / Detail / Sub Detail...., it's easy to forget. Or you get stung by a missing or new column due to schema versions.
If user attempts to load a MD file that references a schema or table that cannot be found, MD browser will look for other suitable schemas. If one is found, a dialog will be shown to ask user if he wants to migrate the MD file to that schema. If more than one is found, user can choose which schema to migrate to.
If user attempts to load a MD file that references columns that cannot be found, MD browser will automatically refresh the column list. After file is loaded, user will be notified.
If new columns exist that are not referenced in MD file, Toad just loads the MD file without those columns. We have no way of knowing if they were intentionally left out or not.
A new button has been added to master and detail toolbars to add missing columns. User can just click this button to pull in new columns.
Hi John
I'm not seeing the functionality mentioned here. I have two schemas open, in the latest Beta - 16.0.36.1439 (32 Bit). I load a MD in Schema2 which was saved from Schema1, both schemas have the tables used in the MD. I just get ORA-00942: table or view does not exist, there's no prompting for migration.
Here you go.
The first table in the MD set is UK_TEST.TH_CON_DTL_GRPS (there's detail and sub detail). I'm opening it in UK_PRE schema.
----------------------------------
-- Session: UK_PRE@DEVDB1
-- Timestamp: 17:11:01.416
Select object_name
from sys.DBA_OBJECTS
where object_type = :typ
and owner = :own
order by 1;
:typ(VARCHAR[5],IN)='TABLE'
:own(VARCHAR[18],IN)='UK_TEST'
----------------------------------
-- Session: UK_PRE@DEVDB1
-- Timestamp: 17:11:01.693
Select m.rowid,
F_CGP_IDNO,F_CGP_DESC,F_CGP_ORDER,F_CGP_PARENT,F_CGP_SETS_SQL
from UK_TEST.TH_CON_DTL_GRPS m
Where
F_CGP_PARENT = -520
ORDER BY 4 ASC NULLS LAST;
-- ORA-00942: table or view does not exist
In my tests, the users don't have access to DBA_OBJECTS, only ALL_OBJECTS. I think that's the key. We're seeing the table name in the dropdown because it's in DBA_OBJECTS, but you don't have the SELECT priv on it. So then the error hits. MD Browser is assuming if the table is the dropdown, you can select from it.
I'll have to do some more work on it to handle that situation too.
Sorry to revive this thread, but a while ago you added the facility to prompt the user to change schema if the MD file referred to another schema when opened.
I have to work with about 140 schemas now and use the Master / Detail functionality a lot - I have lots of MD files for various situations when checking / analysing data, but it is really laborious having to click through so many dialogs to change schema for an MD file each time. I also don't want to save 150 versions of the file, just to record the schema.
Could there be an option to auto-migrate to the current logged in schema, if the schema doesn't match? Perhaps include a check box on the initial confirmation dialog "Auto-migrate to current schema in future?" or save the MD info without the schema name or a token to represent the current schema in the XML file e.g. {CURRENT_SCHEMA}, rather than SCHEMA_NAME and search replace? Just throwing ideas out there:
Ok, next beta, you'll be able to do this, which is probably closer to what you were asking for in the first place
I set up this while logged in as JDORLON using Current Schema, then saved it, logged in as SCOTT and opened the same file.
The .MD file saves Current Schema as the username, and the SQL for the tables does not specify schema. Seems to work pretty well. Give it a try on the June 24th beta and let me know what you think.
I tried the latest Beta 24.2.79.4007, opened one of my MD files (quite a complex one, with multiple M-D-Sub detail levels) and tried to change the Master schema to Current Schema and it mangled things. Ideally there would be an easy way to change an MD file to use Current Schema. I suppose users could manually edit the files in something like Notepad++, but not a particularly great user experience, especially if like me, they have loads of them.
Perhaps you could you add "Current Schema" as an option to the schema picker after the ".... Do you want to migrate..." dialog? And perhaps a Migrate Schema speed button which could bring up the schema picker at any time, to achieve this?
I added Current Schema as an option to the Migration dialog (if the target table exists in the current schema) and it works nicely. I'd prefer to not add a toolbar button for this as it won't have long-term value.
If you want to manually migrate your files, you need to do two global search and replaces in the MD files.
I forgot to mention, if I have lots of sessions open when I open an MD file that isn't for the current schema and it asks if I want to migrate to another schema, I sometimes get scores of "Current Schema" in the list of schemas, rather than just one occurrence. Seems a bit weird as a user experience.
