Master Detail Browser - Schema Agnostic Files

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?

Hi Paul.

That's not a bad idea.

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.

-John

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.

Ok. I've logged your request. I'll get to it fairly soon.

HI Paul,

I've made these changes for next beta:

  • 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.

-John

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.

Hm. Can you give me a spool sql of when you try to open the MD file in the new DB?

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.

1 Like