Toad World® Forums

bug in creating refresh group script in Toad 10.1


#1

When I look at the script for a REFRESH GROUP in Toad 10.1 I see the following
generated:

DECLARE

SnapArray SYS.DBMS_UTILITY.UNCL_ARRAY;

BEGIN

SnapArray(1) := ‘BANINST1.TRNH’;

SYS.DBMS_REFRESH.MAKE (

name => ‘BANINST1.DWH_GROUP1’

,tab => SnapArray

,next_date =>

,interval => ‘’

,implicit_destroy => FALSE

,lax => TRUE

,job => 0

,rollback_seg => NULL

,push_deferred_rpc => TRUE

,refresh_after_errors => FALSE

,purge_option => 1

,parallelism => 0

,heap_size => 0

);

Note the red entry NEXT_DAY does not have a value. It needs to be changed to
NULL to make it work.

Has this been fixed in 10.5?


#2

Doesn’t look that way. How can you get a null value there?

When I create a refresh group with NULL there, and then look in dba_refresh ,
next_date has a value of 1/1/4000


#3

That is fine. We run our refresh groups via a job on demand, not an automatic
schedule.

I our warehousing adventure we run a chain of some 250 jobs that refresh the
data warehouse.


#4

No, what I mean is –

How did you create a refresh group that ends up with a NULL in
DBA_REFRESH.NEXT_DATE?

I am trying to reproduce this scenario but I’m not able to.


#5

In DBA_refresh when you say NEXT_DATE=>Null you get 01/01/4000. That is OK.

I don’t mind of Toad generates a NEXT_DATE of 01/01/4000 or NULL. But
currently it leaves it empty which causes an ORA error.


#6

When I create a refresh group like yours below, using the null keyword, then do
this query:

Select next_date

from dba_refresh

where rname = ‘DWH_GROUP1’

It comes back as 1/1/4000. So I am not able to reproduce the problem.

I did make a change that should take care of it, but I’d like to be able
to reproduce it. So if you can, please send me a script that will produce a
refresh group that will give a null in the select statement above.


#7

When I create a refresh group like yours below, using the null keyword, then do
this query:

Select next_date

from dba_refresh

where rname = ‘DWH_GROUP1’

It comes back as 1/1/4000. So I am not able to reproduce the problem.

I did make a change that should take care of it, but I’d like to be able
to reproduce it. So if you can, please send me a script that will produce a
refresh group that will give a null in the select statement above.


#8

I can’t recreate the problem. It is possible that this is a legacy issue.
We created the refresh groups in Oracle 9 or possibly even 8. Who remembers that
far back. From then on we never had to drop and recreate them. We always added
to them. And dropping an MV took it out of the MV. It is now when we are
“upgrading” from Western European to UTF8 in Oracle 11g the import
of one of our refresh groups failed. So we need to recreate it. This is where I
got that NEXT_DATE problem. I was able to recreate it using NULL. And now it
generates a correct script. I have a feeling this will not happen again.

I just checked DBA_REFRESH and it does have a NULL in NEXT_DATE for one of the
refresh groups. So maybe can just let the NULL be a null. Though I don’t
know how many others would be caught by this.


#9

Ok, no problem.

I made the change anyway so it’ll be there when you get to 10.6 (or next
beta)


#10

By then we won’t need it because this adventure will cause all our refresh
groups to be “normal”