Toad World® Forums

F4 Decribe on Materialized Views shows incorrect script

Our site is now using editioning so when creating an MV, I will create it using the current edition. If I then press F4 to describe it and look at the Script tab, the line "Evaluate Using Current Edition" has been replaced by "Evaluate Using Edition V_20210817_1459" i.e. it tells me the actual edition that was used rather than what was run as part of the script.

If I was to take that script to run in a different instance, I would have to replace that line which is surely not what you want to be doing.

I note the answer to a different MV script related question was "There is no one query that you can execute to get a script for a MView. Toad runs several queries and then it has to put the pieces together to create the script." but when I run the following:

Select dbms_metadata.get_ddl ('MATERIALIZED_VIEW', 'XX_TEST_MV', 'APPS')
From Dual;

It returns the script and shows the line "Evaluate Using Current Edition" so maybe the pieces you are putting together just need to be tweaked a little. It's possibly the intended functionality although I'm not sure why but if that is the case, maybe it could be something added to the options?

Thanks,

Mark.

I just created these 3 MVs. For me, dbms_metadata.get_ddl is showing "evaluate using current edition" for all of them. So while dbms_metadata.get_ddl is correct in your example, it's wrong in 2 of mine. I don't see anything obvious in the data dictionary that shows if the MV was created with a hard-coded edition name vs "evaluate using current edition". :frowning:

CREATE MATERIALIZED VIEW TABLES_MV_ob
EVALUATE USING EDITION ORA$BASE AS
SELECT * from dba_tables;

CREATE MATERIALIZED VIEW TABLES_MV_bb
EVALUATE USING EDITION BOBBY_BROWN AS
SELECT * from dba_tables;

CREATE MATERIALIZED VIEW TABLES_MV_CE
EVALUATE USING CURRENT EDITION AS
SELECT * from dba_tables;

Hi John,

Thanks for the quick response as always. I really should have tried that test for myself! As we always use Current Edition, it never occurred to me that Oracle would have also made an assumption about how the MV was created - just a different assumption to yours.

Would it be a quick thing to add it to the Materialized Views tab on the options for a Describe Window to toggle between named and current edition?

Thanks,
Mark.

HI Mark,

Sure, I can add that for 15.1. It's getting too late for enhancements on 15.0. We'll be releasing that soon. I've logged it as an enhancement in our internal bug tracking system.

-John

Great. Thanks John.