Step 7:
Configuring listener.ora
Open $ORACLE_HOME/network/admin/listener.ora in a text editor, find
SID_LIST_LISTENER definition and add a new entry for myodbc5:
SID_LIST_LISTENER=
(SID_LIST=
# Another entry in the list
# (SID_DESC=
# (SID_NAME=sampleDSN)
# (ORACLE_HOME=/home/dbs/app/Ora/product/11.2.0/dbhome_1)
# (PROGRAM=dg4odbc)
# (ENVS=LD_LIBRARY_PATH=/home/dbs/app/unixodbc-2.2.14)
# )
(SID_DESC=
(SID_NAME=myodbc5)
(ORACLE_HOME=/home/dbs/app/Ora/product/11.2.0/dbhome_1)
(PROGRAM=dg4odbc)
(ENVS=LD_LIBRARY_PATH=/home/dbs/app/unixodbc-2.2.14/lib:/home/dbs/app/Ora/product/11.2.0/dbhome_1/lib)
)
)
We strongly recommend to add the LD_LIBRARY_PATH to the listener.ora file to avoid any conflicts with already existing ODBC driver managers.
The LD_LIBRARY_PATH must contain the fully qualified path to the $ORACLE_HOME/lib and also the library paths of the ODBC driver manager and the ODBC driver itself.
Step 8:
Configuring gateway init<sid>.ora file.
This file does not exist and you have to create it.
In listener.ora, tnsnames.ora we use the name myodbc5 just for convenience,
but it can be literally anything. The init file (initmyodbc5.ora) is a
different story because HS_FDS_CONNECT_INFO is the DSN name in odbc.ini.
$ vi $ORACLE_HOME/hs/admin/initmyodbc5.ora
HS_FDS_CONNECT_INFO=myodbc5 # Data source name in odbc.ini
# HS_FDS_TRACE_LEVEL=user
HS_FDS_SHAREABLE_NAME=/home/dbs/app/unixodbc-2.2.14/lib/libodbc.so
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P15
#
# ODBC env variables
set ODBCINI=/home/dbs/etc/odbc.ini
+---------------------------------------------------------------------------+
NOTE: HS_FDS_SHAREABLE_NAME must point to the [ODBC Driver Manager] library. It is an error to put there the [ODBC Driver] library. The [ODBC Driver] DSN is referenced in HS_FDS_CONNECT_INFO=myodbc5.
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
| NOTE: UnixODBC [ODBC Driver Manager] might not support any character set |
| used in Oracle, so the connection string will be corrupted. |
| To avoid the connection string corruption it is recommended to set |
| HS_LANGUAGE parameter. I the present case AMERICAN_AMERICA.WE8ISO8859P15 |
| worked ok |
+---------------------------------------------------------------------------+
Step 9
Applying the settings in the configuration files.
The listeners must be restarted in order to pick up the changes we just made into tnsnames.ora, listener.ora and initmyodbc5.ora:
$ lsnrctl stop
$ lsnrctl start
+---------------------------------------------------------------------------+
NOTE: The start status has to contain information about the service:
Services Summary...
Service "myodbc5" has 1 instance(s).
Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
EXP:- lsnrctl start listener_test
Step 10 (Optional)
Checking the service status:
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 06-MAY-2011 19:42:10
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 06-MAY-2011 19:41:39
Uptime 0 days 0 hr. 0 min. 30 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/dbs/app/Ora/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /home/dbs/app/Ora/diag/tnslsnr/dbs-pc/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
Services Summary...
Service "myodbc5" has 1 instance(s).
Instance "myodbc5", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
The command prints the status of "myodbc5" service. UNKNOWN is not a problem
in this case because we have not tried using the service yet.
Pinging:
$ tnsping myodbc5
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on
06-MAY-2011 19:42:22
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=localhost)
(PORT=1521)) (CONNECT_DATA= (SID=myodbc5)) (HS=OK))
OK (10 msec)
If the above commands report errors check thsnames.ora and listener.ora and set them as shown on Step 6-7.
Step 11:
Creating the database link and getting the data.
Start sqlplus and type the following command (user and password are the same as in odbc.ini):
SQL> create public database link myodbc5 connect to "mysql_user" identified by
"********" using 'myodbc5';
Database link created.
SQL> select * from "tab1"@myodbc5;
id
----------
txt1
1
some text
2
some more text
DONE!
Exp:--
create public database link myodbc5 connect to "mysql_user" identified by "********" using 'myodbc5';

+---------------------------------------------------------------------------+
NOTE: When selecting data from MySQL linked table it is recommended to enclose the table name into double quotes as "tab1" unless MySQL Server is set to ANSI_QUOTES |
+---------------------------------------------------------------------------+
Note: because MySQL is case sensitive, make sure to use double quotes on username and password. Otherwise, you may get following error:
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[unixODBC][MySQL][ODBC 5.1 Driver]Access denied for user
'USER1'@'XXXX' (using password: YES) {HY000,NativeErr = 1045}
ORA-02063: preceding 2 lines from MYSQLMYDB