jump to navigation

Koneksi Oracle ke SQL Server menggunakan Oracles Heterogeneous Services (OHS) October 23, 2008

Posted by masrony in Oracle.
Tags: , ,
trackback

Step 1
siapkan sistem DSN SQL Server (dengan nama SQLKU) pada Windows ODBC Data Source

Step 2
Buat Heterogeneous Services Initialization File contoh file ada di alamat berikut :
—————-
$ORACLE_HOME/hs/admin/inithsodbc.ora
# 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 =
HS_FDS_TRACE_LEVEL =
#
# Environment variables required for the non-Oracle system
#
#set =
diganti menjadi
$ORACLE_HOME/hs/admin/initsqlku.ora
# 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 = SQLKUDSN
HS_FDS_TRACE_LEVEL = OFF
—————-

Step 3
– Buat 1 listener baru dengan nama LISTENERSQL
– ubah port menjadi 1522
– ubah sid_name menjadi SQLKUDSN
– ubah letak ORACLE_HOME
– Ubah program menjadi hsodbc
contoh file ada di $ORACLE_HOME/hs/admin/listener.ora.sample file diubah sehingga menjadi :
————————–
LISTENERSQLSERVER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_LISTENERSQLSERVER=
(SID_LIST=
(SID_DESC=
(SID_NAME=SQLKUDSN)
(ORACLE_HOME = d:\oracle\product\10.1.0\db_1)
(PROGRAM=hsodbc)
)
)
————————-

STEP 4
buat alias Name server (TNSName)
————————
SQLSERVERDSN =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(CONNECT_DATA=(SID=SQLKUDSN))
(HS=OK)
)
———————–

STEP 5
Validasi koneksi DSN
C:\>tnsping sqlserverdsn
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 parameter files:
d:\oracle\product\10.1.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(CONNECT_DATA=(SID=SQLSERVERDSN)) (HS=OK))
OK (30 msec)

STEP 6
Buat database link
SQL> create database link sqlsvr
connect to sa identified by using ‘SQLSERVERDSN’;
Database link created.

STEP 7
select data
SQL> desc employees@sqlserverdsn
Name Null? Type
—————————————– ——– —————————-
EmployeeID NOT NULL NUMBER(10)
LastName NOT NULL VARCHAR2(20)
FirstName NOT NULL VARCHAR2(10)
Title VARCHAR2(30)
TitleOfCourtesy VARCHAR2(25)
BirthDate DATE
HireDate DATE
Address VARCHAR2(60)
City VARCHAR2(15)
Region VARCHAR2(15)
PostalCode VARCHAR2(10)
Country VARCHAR2(15)
HomePhone VARCHAR2(24)
Extension VARCHAR2(4)
Photo LONG RAW
Notes LONG
ReportsTo NUMBER(10)
PhotoPath VARCHAR2(32512 CHAR)

SQL> select “EmployeeID”,”LastName”,”FirstName” from employees@sqlserverdsn;
EmployeeID LastName FirstName
———- ——————– ———-
1 Davolio Nancy
2 Fuller Andrew
3 Leverling Janet
4 Peacock Margaret
5 Buchanan Steven
6 Suyama Michael
7 King Robert
8 Callahan Laura
9 Dodsworth Anne
9 rows selected.

————————————
Kredit dan sumber tulisan : Ditya Octavianto

Advertisements

Comments»

1. ZAQI - November 19, 2008

SULIT BANGTTTTT

2. masrony - November 19, 2008

Yang sulit dimana mas, sapa tau bisa bantu?

3. sub3xt1 - March 18, 2009

step install OHS belum ada, mungkin itu yang dianggap sulit

4. Mas Rony - March 18, 2009

Wehh pak bekti mampir kesini hehehe…
Untuk sql server bisa dicoba link ini pak :
http://www.masrony.com/2009/03/03/koneksi-oracle-transparent-gateway-dengan-ms-sql-server/
lagi “pindahan rumah” soalnya pak.
Gimana kabarnya pak bekti?

5. andhy - June 7, 2010

klo OS nya linux bagaimana mas???


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

%d bloggers like this: