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. Read the rest of this entry »

I recently imported the contents of an Oracle 8i database into an 11g database. Since the original application needed to query a MS SQL Server database, I had to set up generic connectivity, which among other things, allows , the Oracle database to connect to non-Oracle databases for DML operations. The external database is referred to via a database link. In Oracle 11g, the Database Generic Connectivity for ODBC (dg4odbc) is a replacement for hsodbc.

After following the instructions for a typical setup shown here: Configuring Oracle Database gateway for ODBC, I set up a database link to a remote SQL Server database, and called it SQLSERVER. I planned to use the following query to test whether it is working:

SQL> select * from information_schema.tables@SQLSERVER;
-- gives me a list of tables that this connection has access to

Since I had installed 11g on a 64-bit Linux system, I downloaded and installed the 64-bit DataDirect drivers (seems logical). I could connect to the SQL Server database using the demoodbc executable. So everything looked good until I ran the test:

SQL> select * from information_schema.tables@SQLSERVER;
select * from information_schema.tables@SQLSERVER
              *
ERROR at line 1;
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from SQLSERVER
SQL>

I did enable tracing, but there is no odbc trace yet to look at, and no log either under $ORACLE_HOME/hs/log Interestingly, the only clue I get (from the listener trace) is:

Mon Mar 25 12:59:54 2008
24-MAR-2008 12:59:54 * (CONNECT_DATA=(SID=odbc_SQLSERVER)(CID=(PROGRAM=)(HOST=myhost)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.121)(PORT=43917)) * establish * odbc_SQLSERVER * 12518
TNS-12518: TNS:listener could not hand off client connection
TNS-12547: TNS:lost contact
TNS-12560: TNS:protocol adapter error
TNS-00517: Lost contact
Linux Error: 32: Broken pipe
Mon Mar 25 13:02:50 2008

In plain English, the listener is trying to tell me that it is unable to handle the processing expected with dg4odbc. The seed of doubt is sown: does dg4odbc work? Now would be a good time to check it out:

[oracle@myhost ~]$ dg4odbc
dg4odbc: error while loading shared libraries: libagtsh.so: cannot open shared object file: No such file or directory
[oracle@myhost ~]$

Arrggh! dg4odbc is a 32-bit Application — even when installed on 64-bit Linux. For this application to run on 64-bit Linux, I have to ensure that LD_LIBRARY_PATH is set appropriately, that the 32-bit libraries are read before the 64-bit libraries.

[oracle@myhost ~]$ echo $LD_LIBRARY_PATH
/d01/oracle/product/11.1/lib32:/d01/oracle/product/11.1/lib:/lib:/usr/lib
[oracle@myhost ~]$

And:

[oracle@myhost ~]$ dg4odbc
Oracle Corporation --- TUESDAY   MAR 25 2008 15:26:11.565
Heterogeneous Agent Release 11.1.0.6.0 - Production  Built with
Oracle Database Gateway for ODBC
[oracle@myhost ~]$

So that means I can alter the entries in listener.ora to also use the libraries appropriately. Here’s the old listener.ora:

(SID_DESC =
    (SID_NAME = odbc_sqlserver)
    (ORACLE_HOME = /d01/oracle/product/11.1)
    (PROGRAM = dg4odbc)
   (ENVS=LD_LIBRARY_PATH=/d01/oracle/odbc64v53/lib:
                                    /d01/oracle/product/11.1/lib)
)

And the new listener.ora:

(SID_DESC =
     (SID_NAME = odbc_sqlserver)
(ORACLE_HOME = /d01/oracle/product/11.1)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH=/d01/oracle/odbc64v53/lib:/d01/oracle/product/11.1/lib32:/d01/oracle/product/11.1/lib)
)

Still no joy! Read the rest of this entry »