Heterogeneous Services (HS): access SQL server 2000 from Oracle 11g

Actually, it is even more easier than oracle did in previous version. Oracle called it "DGODBC" (data gateway from ODBC) when we consider ODBC is good choice though we know it is bad in performance and functions compare to native methods.

I set up environment in local pc: oracle 11.1.0.6.0 + ms SQL server 2000 . Then I got introduction on how to set up Oracle HS (heterogeneous service): http://www.datadirect.com/developer/odbc/oracle_heterogeneous/index.ssp. That is good but Oracle 11g make it more easier than expected.
Here what I did:
1) install SQL server
2) Create user and password scott/tiger in SQL server via "SQL server enterprise manager", GUI yes.
3) then copy a table ’employee’ from user ‘dbo’.
4) Create ‘system DSN’ via ‘ODBC ‘, it is in your ‘Control Panel’->’Administrative Tools’->’data resource (ODBC)’
username: scott
password: tiger
my ‘system DSN’ name is "sqls2000" /* write it down, it will be used later */
5) Customize listener.ora , tnsnames.ora and initDGODBC.ora in oracle database.
/* Oracle 11g provide sample files under $ORACLE_HOME/hs/admin so you can easily finish it in 1 min*/
/* only change PORT number here if you are not using default */
# my listener
SID_LIST_LISTENERA11G=
  (SID_LIST=
      (SID_DESC=
          (GLOBAL_DBNAME = a11g)
          (ORACLE_HOME = C:oracleproduct11.1.0db_1)
          (SID_NAME = a11g)
      )
      (SID_DESC=
         (SID_NAME=dg4odbc)
         (ORACLE_HOME=C:oracleproduct11.1.0db_1)
         (PROGRAM=dg4odbc)
      )
  )
# my tnsnames.ora
dg4odbc  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=pang)(PORT=2022))
    (CONNECT_DATA=(SID=dg4odbc))
    (HS=OK)
  )
# initdg4odbc.ora
….
HS_FDS_CONNECT_INFO = sqls2000
HS_FDS_TRACE_LEVEL = ON

6) Created database link to SQL server from Oracle. Do it before actual query
CREATE DATABASE LINK sqls2000
CONNECT TO "scott" IDENTIFIED BY "tiger" USING ‘dg4odbc’;
7) Query my tables in SQL server
SQL> desc employees@sqls2000;
 Name                                      Null?    Type
 —————————————– ——– —————————-
 EmployeeID                                  NOT NULL NUMBER(10)
 LastName                                    NOT NULL NVARCHAR2(20)
 FirstName                                   NOT NULL NVARCHAR2(10)
 Title                                       NVARCHAR2(30)
 TitleOfCourtesy                             NVARCHAR2(25)
 BirthDate                                   DATE
 HireDate                                    DATE
 Address                                     NVARCHAR2(60)
 City                                        NVARCHAR2(15)
 Region                                      NVARCHAR2(15)
 PostalCode                                  NVARCHAR2(10)
 Country                                     NVARCHAR2(15)
 HomePhone                                   NVARCHAR2(24)
 Extension                                   NVARCHAR2(4)
 Photo                                       LONG RAW
 ReportsTo                                   NUMBER(10)
 PhotoPath                                   NVARCHAR2(255)

SQL> select "EmployeeID" from employees@sqls2000 where "EmployeeID" is not null;

EmployeeID
———-
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

Conclusion
It is even straightforward than expected.

Advertisements

About Pang

Oracle DBA working in database, fusion middleware and identity management and cloud.
This entry was posted in Oracle database. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s