DBlink from oracle 10g/XE to sql server

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.



Labels: , , , , , , , ,