Pythian Group

The Oracle gateway for ODBC provides an almost seamless data integration between Oracle and other RDBMS. I won’t argue about its performance, limits, or relevance. It serves a few purposes; set it up and you’ll be able, for example, to create database links between Oracle and MySQL. After all, wouldn’t it be nice if you could run some of the following SQL statements?

• select o.col1, m.col1 from oracle_tab
o, mysql_tab@mysql m where o.col1=m.col1;
• insert into oracle_tab (select * from mysql_tab@mysql);

This post is intended to share, the same way Karun did it for SQL Server last year, some tips related to the setup of the Oracle Gateway for ODBC with MySQL Connector ODBC on Linux.

Prerequisites

I’ve installed all the configuration on my laptop to test it. It’s running Ubuntu Intrepid Ibex 32bits, but I won’t dig into the challenge of installing the MySQL Connector ODBC 5.1 on it. All I’ll tell you is that, if I understand correctly, the version of iodbc that comes with Intrepid doesn’t support MySQL Connector ODBC 5.1 too well, and the messages that it returns are not quite explicit. To be frank, what I did is put that monkey on Augusto’s back. He sorted out everything in a few minutes. I guess I have to thank Augusto twice, just for this post! I’ve also followed his “Installing Oracle 11gR1 on Ubuntu 8.10 Intrepid Ibex” post to install the Oracle part.

But let’s talk about the prerequisites! You need to have installed and configured the following components:

Read the rest of this entry . . .

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 . . .