BUG - When exporting MVIEWs with remove and add to refresh group commit goes in wrong spot

Toad for Oracle
1

I am doing a migration that requires rebuild of MVIEWS and is purpose of my company purchasing TOAD. When MVIEWs are export DDL and the removal and addition to refresh group is scripted, it requires a commit in the BEGIN/END block of the add. There is a bug now that it puts the commit outside the begin end block and before the / making it fail and not be added to any refresh group... very frustrating with 100s of mviews to rebuild. Anyone know of a work around where manual intervention all the scripts?

2

Hi Billingsd2,

I am one of the developers of Toad. Sorry for the inconvenience.

A couple of points/questions:

  1. if it helps you, you can leave out the call to dbms_refresh entirely by unchecking here:

    image
    image790×591 43.7 KB

  2. I just tested Toad 16.3 and see what you mean about the COMMIT being outside of the begin/end (see script below), but for me, it is not a problem. When I run this script, the MV is added to the refresh group. There is no failure. I can easily fix this before Toad 17.0 is to be released in October, but can you help me understand why it is a problem? COMMIT does not need to be inside the BEGIN/END block in order to commit. Also, if the next statement is DDL, then Oracle runs an implicit commit anyway.

BEGIN
  DBMS_REFRESH.SUBTRACT(NAME => 'REST_RG', LIST => 'TEST_MV');
END;
/
DROP MATERIALIZED VIEW TEST_MV ;

CREATE MATERIALIZED VIEW TEST_MV 
    (EMPNO,ENAME,JOB,MGR,HIREDATE,
     SAL,COMM,DEPTNO)
BUILD IMMEDIATE
NEVER REFRESH
AS 
SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR" "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"."DEPTNO" "DEPTNO" FROM "EMP" "EMP";


COMMENT ON MATERIALIZED VIEW TEST_MV IS 'snapshot table for snapshot JDORLON.TEST_MV';

BEGIN
  DBMS_REFRESH.ADD(NAME => 'REST_RG', LIST => 'TEST_MV');
END;
-- DBMS_REFRESH requires a commit.
Commit;
/
3

Oh, and I forgot to say...in 1).....you could get all of your MV scripts w/o the Refresh Group option, then go to Schema Browser -> Refresh Groups and get the script for your refresh groups, then run that afterwards. There, the commit is inside the begin...end. But again, I fail to see why this is important.

image
image1046×679 32.6 KB

4

When I copy the script out to the Server and run via sqlplus using the script generated:

SQL> @ACTUAL_HOURS_SNP.sql

PL/SQL procedure successfully completed.

Materialized view dropped.

Materialized view created.

Comment created.

Commit;
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00103: Encountered the symbol "COMMIT"

Index created.

Index created.

Even if I commit after running the script it is not added to the refresh group.

5

Oh, I see.

We're missing a slash after the begin..end, so that whole block is invalid. Weird that it runs in Toad, but that's the problem.

I will have this fixed for the next beta, which will be released on Monday. Are you using 16.3? If so, you can get the beta here. If not, 17.0 will be officially released around the end of October.

If you can't get the beta, you should be able to solve by search-replace on that comment:

-- DBMS_REFRESH requires a commit. ...and just replace it with a slash.

Or, can you turn that option off, then get separate scripts for the refresh groups?

-John

6

Thanks for reporting this, and sticking with me until I saw the problem. :slight_smile:

7

16.3.231.2085 version.

Thanks for the quick response, never seen such fast replies. Im glad to help! TOAD is an amazing tool for us DBAs. My Key will work with new beta? also you said you might have the fix in there by next Monday?

8

It's my pleasure. Fast replies are what you (usually) get around here. :slight_smile:

Yes, your key will work with the beta. Read the notes on that page about install. It's pretty simple.

The beta with this fix will be released Monday, Sept 4th. Hm, actually, that's a holiday, so the 5th. If that's a problem let me know and I'll kick a new beta later today.

-John

9

No worries just on my part on the timing and thanks for the heads up

10

You can actually install the beta now if you want. Then on Tuesday when a new one is released the beta will show an icon in the lower left corner saying that an update is available. Assuming Toad can reach the internet from your PC.

image