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!
SQL> select * from information_schema.tables@SQLSERVER select * from information_schema.tables@SQLSERVER * ERROR at line 1:ORA-28500: connection from ORACLE to a non-Oracle system returned this message: ORA-02063: preceding line from SQLSERVER Now we have a trace to look at. And it says:
.... Entered hgolofn at 2008/03/25-08:30:03 HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/d01/oracle/odbc64v53/lib/libodbc.so" Entered hgolofns at 2008/03/25-08:30:03 hoaerr:28500 Exiting hgolofns at 2008/03/25-08:30:03 Failed to load ODBC library symbol: /d01/oracle/odbc64v53/lib/libodbc.so(SQLAllocHandle) Exiting hgolofn, rc=28500 at 2008/03/25-08:30:03 Exiting hgoinit, rc=28500 at 2008/03/25-08:30:03 with error ptr <a href="///hgoinit.c">FILE:hgoinit.c</a> LINE:282 FUNCTION:hgoinit() ID:Loading ODBC aray of function ptrs Entered hgoexit at 2008/03/25-08:30:03 .... In its language, it's saying:
Sorry this is the wrong library and cannot work with dg4odbc. So the 64-bit drivers were a wrong choice -- time to install and configure the 32-bit drivers. I downloaded the evaluation version from DataDirect. After unzipping in a temporary location . . .
[oracle@myhost data_direct]$ ksh unixmi.ksh DataDirect Technologies Product Setup is preparing....English has been set as the installation language. Log file: /tmp/logfile.392.1 ---------------------------------------------------------------------- DataDirect Technologies Product Setup (DPS) for UNIX operating systems ---------------------------------------------------------------------- The following operating system has been detected: Linux Is this the current operating system on your machine (Y/N) ? [Y] END USER PRODUCT LICENSE AGREEMENT ("AGREEMENT") ---snipped ---- Enter YES to accept the above agreement: YES Please enter the following information for proper registration. In the Key field, enter either EVAL or the Key provided. Name :EVAL Company :Mycompany Serial Number:EVAL Key :EVAL You can install drivers for all supported databases or install a single driver. Drivers for All Supported Databases includes one driver for each supported database. In the case of Oracle, DataDirect Connect for ODBC 5.3 SP1 has two available drivers that support the database--a wire protocol driver that does not require any database client software to connect to the database, and a client-based driver that does require client software. By default, the Oracle Wire Protocol driver is installed. Single Driver allows you to choose one from any of the DataDirect Connect for ODBC 5.3 SP1 drivers, including the client-based Oracle driver. If you want to evaluate more than one single driver, you will have the opportunity to do so after completion of the initial installation. 1. Drivers for All Supported Databases 2. Single Driver Please enter an option [1]:2 1. DB2 Wire Protocol 2. dBASE 3. Informix Wire Protocol 4. Oracle Wire Protocol 5. Microsoft SQL Server Wire Protocol 6. Sybase Wire Protocol 7. Teradata 8. Text 9. MySQL Wire Protocol 10. Oracle (client) Please enter driver option:5 You have chosen to eval the Microsoft SQL Server Wire Protocol driver. To change this information, enter C. Otherwise, press Enter to continue.: DataDirect Technologies Product Setup is preparing the installation. Choose a temporary directory. Enter the full path to the temporary install directory.[/tmp]: /d01/oracle/tmp_datadirect Checking for available space... There is enough space. Extracting files... Choose a destination directory. Enter the full path to the install directory.[/opt/odbc32v53]: /d01/oracle/odbc32v53 Checking for available space... There is enough space. Extracting files... Creating license file...... DataDirect Technologies Product Setup successfully removed all of the temporary files. Thank you for using DataDirect Technologies products. Installation ended successfully. Would you like to install another product (Y/N) ? [Y]N Thank you for using DataDirect Technologies products. Installation ended successfully. [oracle@myhost data_direct]$ . . . I made changes to the
~/.bash_profile (Oracle user uses bash as its default shell). I added:
export ODBC_HOME=/d01/oracle/odbc32v53
export ODBCINI=/d01/oracle/odbc32v53/odbc.ini
export ODBCINST=/d01/oracle/odbc32v53/odbcinst.ini
export LD_LIBRARY_PATH=/d01/oracle/odbc32v53/lib:/d01/oracle/product/11.1/lib32:/d01/oracle/product/11.1/lib:/lib:/usr/lib
Then I fixed
listener.ora. Instead of using odbc64v53 as before, I moved to odbc32v53:
New:(SID_DESC =
(SID_NAME = odbc_sqlserver)
(ORACLE_HOME = /d01/oracle/product/11.1)
(PROGRAM = dg4odbc)
(ENVS=LD_LIBRARY_PATH=/d01/oracle/odbc32v53/lib:/d01/oracle/product/11.1/lib32:/d01/oracle/product/11.1/lib)
)
I was ready to run and test.
select * from information_schema.tables@SQLSERVER; select * from information_schema.tables@SQLSERVER * ERROR at line 1: ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [DataDirect][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'information_schema'.[DataDirect][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. ORA-02063: preceding 2 lines from SQLSERVER And the trace tells a different story:
hgopoer, line 159: got native error 170 and sqlstate 42000; message follows... [DataDirect][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'INFORMATION_SCHEMA.TABLES'.[DataDirect][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. Exiting hgopoer, rc=0 at 2008/03/25-09:20:34 hgopars, line 278: calling SQLPrepare got sqlstate 42000 Exiting hgopars, rc=28500 at 2008/03/25-09:20:34 with error ptr <a href="///hgopars.c">FILE:hgopars.c</a> LINE:308 FUNCTION:hgopars() ID:Prepare stmt It turned out that dg4odbc has "hard coded" the quotes and this will need to be changed in
odbc.ini. I should have looked more closely at the installation instructions -- yes, the example shows
QuotedId=Yes. The old:
QuotedId=No
And the new:
QuotedId=Yes
The next attempt:
select * from information_schema.tables@SQLSERVER; select * from information_schema.tables@SQLSERVER * ERROR at line 1: ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [DataDirect][ODBC SQL Server Driver]Connection is busy with results for another hstmt ORA-02063: preceding 2 lines from SQLSERVER Now we are getting somewhere. The trace tells us that the failure happens when the connection tries to get stats for the table:
Entered hgotcis at 2008/03/25-12:18:23 Calling SQLStatistics for TABLES Entered hgopoer at 2008/03/25-12:18:23 hgopoer, line 159: got native error 0 and sqlstate HY000; message follows... [DataDirect][ODBC SQL Server Driver]Connection is busy with results for another hstmt Exiting hgopoer, rc=0 at 2008/03/25-12:18:23 hgotcis, line 384: calling SQLStatistics got sqlstate HY000 And so I did yet another workaround -- I added the line:
HS_FDS_SUPPORT_STATISTICS=FALSE
to
initodbc_sqlserver.ora.
SQL> select count(*) from information_schema.tables@SQLSERVER
2 /
COUNT(*)
----------
627
SQL>
Sweet! And so, when next using dg4odbc:
- The 32-bit drivers are needed to configure Generic Connectivity.
- In
odbc.ini, change QuotedId=No to QuotedId=Yes
- In
initodbc_SQLSERVER.ora, add the line: HS_FDS_SUPPORT_STATISTICS=FALSE