After setting up dg4odbc to connect to a SQL Server database (read ‘Tips on Using dg4odbc on 64-bit Linux’), I spent last week trying to get refreshes of materialized views to work in 11g, using the same DDL as in 8i. I was very frustrated to see the refreshes break in 11g.

The DDL gives no obvious clues. The remote table:

desc MYTABLE@SQLSERVER
Name                           Null?    Type
------------------------------ -------- ----------------------------
MY_ID                          NOT NULL NUMBER(5)
MY_DESC                        NOT NULL VARCHAR2(40)

SQL> create materialized view MV_MYTABLE
2 as select *
3 FROM   MYTABLE@SQLSERVER
4 WHERE  MY_ID IS NOT NULL
5 AND    MY_DESC IS NOT NULL;

Materialized view created.

SQL> begin
2  DBMS_SNAPSHOT.REFRESH( 'MV_MYTABLE','C');
3  end;
4  /
begin
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01400: cannot insert NULL into (%s)
ORA-02063: preceding line from SQLSERVER
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712
ORA-06512: at line 2

The reason, it turns out, is the ANSI-compliance of dg4odbc. The remote table has a row where the column MY_DESC is storing a '' (a zero-length string).

SQL Server treats the zero-length string as different from NULL. Oracle 11g treats zero-length strings as the same as NULL.

SQL> l
1  select count(*) from dual
2* where '' is null
SQL> /

COUNT(*)
----------
1

SQL>

The new dg4odbc exactly complies with the behaviour of the remote database, and treats zero-length strings as different from NULL. The complete refresh of the materialized view runs the stored DDL. So while the rows do not contain nulls, and hence get transferred across the database link, they are treated as having NULLs in the Oracle world, and the refresh fails.

SQL> select count(*) from MYTABLE@SQLSERVER
2  WHERE  MY_ID IS NOT NULL
3  AND    MY_DESC IS NOT NULL ;

COUNT(*)
----------
8960

SQL>

SQL> select count(*) from MYTABLE@SQLSERVER
2  WHERE  MY_ID IS NOT NULL
3  AND    MY_DESC IS NOT NULL
4  AND    MY_DESC  '';

COUNT(*)
----------
8959   ---- Excludes the Problematic Row!

SQL>

The fix: change the DDL to check for zero-length strings when you are using remote ANSI compliant databases.

SQL> drop materialized view MV_MYTABLE ;

Materialized view dropped.

SQL> create materialized view MV_MYTABLE
2 as select *
3 FROM   MYTABLE@SQLSERVER
4 WHERE  MY_ID IS NOT NULL
5 AND    MY_DESC IS NOT NULL
6 AND    MY_DESC  '' ;

And we can refresh as in 8i.

SQL> begin
2  DBMS_SNAPSHOT.REFRESH( 'MV_MYTABLE','C');
3  end;
4  /

PL/SQL procedure successfully completed.

SQL>

So, to those three tips, I now add #4: check the statements that use a WHERE ... IS NOT NULL across the remote database link to ANSI-compliant databases.