Introduction:
This article is the last of the series "Informix to Oracle Connectivity by using the Oracle Gateway 11g for ODBC". This last part is very easy and we will fetch the data from informix into Oracle and will see that all the configurations which was done is working properly.
Cookbook:
1- Install the Oracle 10g R2 (10.2.0.1).
2- Path Oracle 10g R2 (10.2.0.1) to 10.2.0.4. For Sun Sparc 64 bit platform, we need to down load the patch p6810189_10204_Solaris-64.zip.
3- After applying the patch, now we need to change the tnsnames.ora file of Oracle 10g R2 (10.2.0.4) database and need to add the following entry.
DG4ODBC =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=oragateway_host)(PORT=1522))
(CONNECT_DATA=(SID=DG4ODBC))
(HS=OK)
)
4- we can test this tns entry by tnsping, following is the successfull test.
bash-2.05$ tnsping DG4ODBC
TNS Ping Utility for Solaris: Version 10.2.0.4.0 - Production on 30-DEC-2009 16:26:41
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=oragateway_host)(PORT=1522)) (CONNECT_DATA=(SID=DG4ODBC)) (HS=OK))
OK (0 msec)
bash-2.05$
5- Now we can open the SQL session and fetch the data from the informix database.
SQL> select * from "emp"@dg4odbc;
first_na last_name emp_id hire_date salary dept exempt interests-------- ---------- ------ --------- ---------- ---- ---------- ----------Tyler Bennett E10297 32000 D101 1Tyler Bennett E10297 32000 D101 1John Rappl E21437 47000 D050 1George Woltman E00127 53500 D101 1Adam Smith E63535 18000 D202 0David McClellan E04242 41500 D101 1Rich Holcomb E01234 49500 D202 1Nathan Adams E41298 21900 D050 0Richard Potter E43128 15900 D101 0David Motsinger E27002 19250 D202 0Tim Sampair E03033 27000 D101 1
first_na last_name emp_id hire_date salary dept exempt interests-------- ---------- ------ --------- ---------- ---- ---------- ----------Kim Arlich E10001 57000 D190 1Timothy Grove E16398 29900 D190 1Tyler Bennett E10297 32000 D101 1Tyler Bennett E10297 32000 D101 1John Rappl E21437 47000 D050 1George Woltman E00127 53500 D101 1Adam Smith E63535 18000 D202 0David McClellan E04242 41500 D101 1Rich Holcomb E01234 49500 D202 1Nathan Adams E41298 21900 D050 0Richard Potter E43128 15900 D101 0
first_na last_name emp_id hire_date salary dept exempt interests-------- ---------- ------ --------- ---------- ---- ---------- ----------David Motsinger E27002 19250 D202 0Tim Sampair E03033 27000 D101 1Kim Arlich E10001 57000 D190 1Timothy Grove E16398 29900 D190 1
26 rows selected.
The important point is the table name is case sensitive and should be enclosed.
Conculsion:
We have seen in these 3 articles how to fetch data from informix database into Oracle database, this was the testing, any one who wants to implement this into production needs to see the compatibility of the products.
