 |
| How to create database Link Oracle 10g / XE to MYSQL Server |
:) Hi there, a few days ago, i had a problem that makes me to use
oracle 10g dblink to sql server.
This is my first time to do this and i do not sleep around 4 days for finding how to do this properly.
When i tried the procedures that i searched on google. . .there are so many errors i faced. at last fortunately i managed connecting oracle to MYSQL server. . . :)
I,m Using
OS: WIN7
ODBC DSN : hsodbc
DB1 : oracle XE
DB2 :MYSQL Server
DB2 : user "a" pass "a"
Database link from oracle 10g to sql server :
1.Setting up a system DSN(Data Source Name)
2.Setting hsodbc(oracle XE/10g)/dg4odbc(oracle11g-12c)
3.Setting tnsname.ora / listener.ora
4.Create db link to MYSQL
done!
1.
Configure ODBC by setting up a system
DSN(Data Source Name)
Typically you can find this at
Start->Settings->Control Panel->Administrative Tools->Data Sources(ODBC)
2.
Look for inithsodbc.ora in
$ORACLE_HOME/hs/admin/
Make a copy of it and rename it to init<your odbc Data Source Name>.ora
Remember we had chosen "hsodbc" as our DSN.
Open the file and enter the DSN that you just created recently i.e "hsodbc".
This is how
$ORACLE_HOME/hs/admin/inithsodbc.ora should look like:
# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = hsodbc
HS_FDS_TRACE_LEVEL = 0
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
3.
Configure listener.ora
$ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = hsodbc)
(ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
(PROGRAM = hsodbc)
)
)
Note:
SID_NAME should be your
DSN name. PROGRAM=
hsodbc or
dg4odbc tells Oracle to use heterogenous services.
4.
Configure tnsnames.ora
$ORACLE_HOME/network/admin/tnsnames.ora
HSODBC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Lab)(PORT = 1521))
(CONNECT_DATA = (SID = hsodbc))
(HS = OK)
)
HOST = Oracle Host
PORT = Oracle PORT
SID = your DSN name
Restart your listener to make sure the settings are in effect.
lsnrctl stop
lsnrctl start
You can now validate the connection to your SQL Server database by the normal Oracle tnsping utility.
tnsping hsodbc
TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 01-DEC-2004 13:19:54
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used TNSNAMES adapter to resolve the alias
Attempting to contact
OK (30 msec)
5.
Make sure that global_names parameter is set to false
SQL> sho parameter global_names
NAME TYPE VALUE
------------------ ---------------- -------
global_names boolean FALSE
Create a database link:
CREATE DATABASE LINK <<dblinkname>> CONNECT TO <<user MYSQLServer>> IDENTIFIED BY <<password MYSQLServer>> USING '<<connection string>>';
Example:
SQL>
CREATE DATABASE LINK hsodbc CONNECT TO a IDENTIFIED BY a USING 'hsodbc';
Database link created.
We have completed our configuration.
Example:
SQL>
desc emp@hsodbc
Name Null? Type
------------- -------- --------------
EMPID NOT NULL NUMBER(1)
EMPNAME VARCHAR2(60)
CREATED DATE
Note :
Check Whether HSODBC is Installed
The first common error is people do not check the OracleDB has installed hsodbc/dg4odbc on the DB or not.
The full text of the error reported to us was:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
You may get this error if:
Oracle fails to connect to DG4ODBC/HSODBC. DG4ODBC/HSODBC cannot retrieve the underlying error from the ODBC driver.
open command prompt / terminal go to $ORALCE_HOME/bin
cd $ORALCE_HOME/bin
hsodbc
you will see something similar to the following if HSODBC is installed:
hsoodbc: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV)
If you get
HSODBC: empty, your version of Oracle does not include HSODBC.
if you find this error
ORA-00942 table of view does not exist
MYSQLServer is not case sensitive as long as you omit the quotes. The following names are all identical:
TEST2, test2, Test2, TeSt2
The following tables are different:
"TEST2", "test2", "Test2"
So, use double quote (
") between your table name, because oracle will send upper case command by default.
:) i hope it will help your problem.
